counting records

[Pierre]

Hi all,

I have a table with one numeric fields.
i want to add a record number field in a query
Look simple but i cant do it! - help
I have: order no
1
2
6
7
8

I want: order no;recno -
1|1
2|2
6|3
7|4
8|5

Regards
pierre

 

[Sandro]

Hi all,

I have a table with one numeric fields.
i want to add a record number field in a query
Look simple but i cant do it! - help
I have: order no
1
2
6
7
8

I want: order no;recno -
1|1
2|2
6|3
7|4
8|5

Regards
pierre

Hi Pierre,

you need a subQuery :
(select count(*) from YourTable as aaa where aaa.[order
no]<YourTable.[order no]+1) as recNo ....

Ciao, Sandro

 

[Per Larsen]

Hi all,

I have a table with one numeric fields.
i want to add a record number field in a query
Look simple but i cant do it! - help
I have: order no
1
2
6
7
8

I want: order no;recno -
1|1
2|2
6|3
7|4
8|5

Regards
pierre

Hi Pierre,

you need a subQuery :
(select count(*) from YourTable as aaa where aaa.[order
no]<YourTable.[order no]+1) as recNo ....

Ciao, Sandro

While you can do it the way Sandro suggests, you should really consider the need for displaying a record number. Access is a relational database and in those the record number (often) isn't relevant, and it will also depend of your ordering in the recordset. The given method is very CPU intensive (at least when the number of records is large) because of the subquery.

If you need to show the record number in reports, you can put a textbox in the report with the controlsource =1 and then set the Running Sum property for the textbox to 'Over All' (or 'Over Group' if you want the counter reset for each group)

Regards
PerL

 

[Sandro]

[cut]

While you can do it the way Sandro suggests, you should really consider

the need for displaying a record number. Access is a relational database and
in those the record number (often) isn't relevant, and it will also depend
of your ordering in the recordset. The given method is very CPU intensive
(at least when the number of records is large) because of the subquery.

If you need to show the record number in reports, you can put a textbox in

the report with the controlsource =1 and then set the Running Sum property
for the textbox to 'Over All' (or 'Over Group' if you want the counter reset
for each group)

Regards
PerL

Hi PerL,

Yes, of course you're right on all the line

But the question now is : if you want to obtein this progressive counter in
a continous form, how do you do ?

Ciao, Sandro.

 

[Per Larsen]

[cut]

While you can do it the way Sandro suggests, you should really consider

the need for displaying a record number. Access is a relational database and
in those the record number (often) isn't relevant, and it will also depend
of your ordering in the recordset. The given method is very CPU intensive
(at least when the number of records is large) because of the subquery.

If you need to show the record number in reports, you can put a textbox in

the report with the controlsource =1 and then set the Running Sum property
for the textbox to 'Over All' (or 'Over Group' if you want the counter reset
for each group)

Regards
PerL

Hi PerL,

Yes, of course you're right on all the line

But the question now is : if you want to obtein this progressive counter in
a continous form, how do you do ?

Ciao, Sandro.

I guess the answer is: I would not. In my Access forms I always show the 'Navigate Buttons' in the bottom of the form thereby also showing the number of the current record.

Regards
PerL