Problem with form serial number

[forcefield via AccessMonster.com]

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?

 

[Stefan Hoffmann]

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
“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 <--

 

[forcefield via AccessMonster.com]

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.

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 <--

 

[Stefan Hoffmann]

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 <--

 

[forcefield via AccessMonster.com]

Thank you Stefan for your help

hi,

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 <--