Re: counting records

Tech-Archive recommends: Fix windows errors by optimizing your registry

Sandro wrote: 
"Pierre" <pf@xxxxxxxxxx> ha scritto nel messaggio
news:C04Pe.73678$Qh7.1045731@xxxxxxxxxxxxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: counting records
    ... Sandro wrote: ... for the textbox to 'Over All' (or 'Over Group' if you want the counter reset for each group) ... a continous form, how do you do? ... Regards ...
    (microsoft.public.access.queries)
  • Re: How to convert numeric fields in an input line?
    ... Best Regards, ... Robin Theilade ... > How do you convert numeric fields in an input line like ... > to the int values ...
    (microsoft.public.dotnet.languages.csharp)