I have a table with 3 fields :- ID, Class and FName,
My continuous form (which has Fname and Class fields) is bound to a query
“qryTblNameClass”
The SQL for the query is
SELECT tblNameClass.Class, tblNameClass.FName
FROM tblNameClass
GROUP BY tblNameClass.Class, tblNameClass.SName
ORDER BY tblNameClass.Class, tblNameClass.FName;
What I would like to do is to group the same class and show its running
serial number on the left of the continuous form.
I trying putting a textbox on extreme left of the the continuous form detail
section to show me the running serial number
Eg     1     Bioclass 2   John
          2     BioClass 2  Peter
          3     BioClass 2  Janet
          1     PhyClass 1  Bill
          2     PhyClass 1  Ben
          1     ChemClass 1 Julie

I tried putting in the detail section a unbound textbound =[CurrentRecord]
or =Dcount(          )  I got error message #Name? .

Any Idea?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1

hi,

On 17.03.2010 15:21, forcefield via AccessMonster.com wrote:
> I have a table with 3 fields :- ID, Class and FName,
> My continuous form (which has Fname and Class fields) is bound to a query
> “qryTblNameClass”
> The SQL for the query is
> SELECT tblNameClass.Class, tblNameClass.FName
> FROM tblNameClass
> GROUP BY tblNameClass.Class, tblNameClass.SName
> ORDER BY tblNameClass.Class, tblNameClass.FName;
SName?

> What I would like to do is to group the same class and show its running
> serial number on the left of the continuous form.
Try this:

SELECT
   (
     SELECT Count(*)
     FROM tblNameClass I
     WHERE I.Class = O.Class
     AND I.FName <= O.FName
   ) AS GroupSeqence
   O.Class,
   O.FName
FROM tblNameClass O
ORDER BY O.Class, O.FName;


mfG
--> stefan <--

Sorry typo error . It should be FName.

I am still trying to learn VBA. Please kindly explain what is I. Class or  O.
Class  . Do I copy exactly the SQL into the control source of a textbox and
do I have to replace the I and O with tblNameClass. 

Thank you, Stefan, for your patience.


Stefan Hoffmann wrote:
>hi,
>
>> I have a table with 3 fields :- ID, Class and FName,
>> My continuous form (which has Fname and Class fields) is bound to a query
>[quoted text clipped - 4 lines]
>> GROUP BY tblNameClass.Class, tblNameClass.SName
>> ORDER BY tblNameClass.Class, tblNameClass.FName;
>SName?
>
>> What I would like to do is to group the same class and show its running
>> serial number on the left of the continuous form.
>Try this:
>
>SELECT
>   (
>     SELECT Count(*)
>     FROM tblNameClass I
>     WHERE I.Class = O.Class
>     AND I.FName <= O.FName
>   ) AS GroupSeqence
>   O.Class,
>   O.FName
>FROM tblNameClass O
>ORDER BY O.Class, O.FName;
>
>mfG
>--> stefan <--

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1

hi,

On 18.03.2010 01:43, forcefield via AccessMonster.com wrote:
> I am still trying to learn VBA. Please kindly explain what is I. Class or  O.
> Class  . Do I copy exactly the SQL into the control source of a textbox and
> do I have to replace the I and O with tblNameClass.
Create a new query and paste this SQL statement into it...

I and O are table alias names. You can also modify them in the design 
view of a query. Select a table and open the property editor. Table 
aliases are useful to write shorter easier to read SQL statements.

So I.Class and O.Class are the fields from your tblNameClass.

I is the inner sub-query for calculating the record count, O is the 
outer query retrieving the records.


mfG
--> stefan <--

Thank you Stefan for your help

Stefan Hoffmann wrote:
>hi,
>
>> I am still trying to learn VBA. Please kindly explain what is I. Class or  O.
>> Class  . Do I copy exactly the SQL into the control source of a textbox and
>> do I have to replace the I and O with tblNameClass.
>Create a new query and paste this SQL statement into it...
>
>I and O are table alias names. You can also modify them in the design 
>view of a query. Select a table and open the property editor. Table 
>aliases are useful to write shorter easier to read SQL statements.
>
>So I.Class and O.Class are the fields from your tblNameClass.
>
>I is the inner sub-query for calculating the record count, O is the 
>outer query retrieving the records.
>
>mfG
>--> stefan <--

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1