Record the Record Number in A Query

[Guest]

Is there a way to have a field in a query which displays the current record /
line number of each record in the queries' recordset.

I want to avoid using temporary tables if possible.

ie.

Line No:
1
2
3
etc

Thank You

Ross

 

[Rick B]

This same question is asked very often, so you could go search and find the
answer. BUT... The exact same question was just asked about an hour ago.
Se the post a few below you with the Subject of "Assigning Value". There is
no answer there yet, but when one is added it should also resolve your
issue.

 

[Rick B]

Lots of possible solutions in past posts...

http://groups.google.com/groups?q=microsoft.public.access.queries+line+number&start=20&hl=en&

 

[Guest]

Thank you Rick.

I did do quite of bit of research here and on google but was using the
keyword "Auto Numbering" or "Autonumbering" I found a good posting (jere) on
05/10/2005, Michael Walsh, but was unable to relate the solution to MY query.

 

[John Vinson]

Is there a way to have a field in a query which displays the current record /
line number of each record in the queries' recordset.

I want to avoid using temporary tables if possible.

ie.

Line No:
1
2
3
etc

Thank You

Ross

This is really easy on a Report: just put a textbox on the report with
a Control Source of 1 (just the number one) and set its Running Sum
property to Over All.

It's considerably less efficient in a Query; you need some field (I'll
call it Seq) in your Query which is different in every record and is
sorted in ascending (or descending) order. You can put a calculated
field in the query

=DCount("*", "[your-query-name]", "[Seq] <= " & [Seq])

This may run ...sssslllloooowwww... because it essentially must rerun
the query again on every row of the query to do the dcount.

John W. Vinson[MVP]

 

[Rick Brandt]

Is there a way to have a field in a query which displays the current record /
line number of each record in the queries' recordset.

I want to avoid using temporary tables if possible.

ie.

Line No:
1
2
3
etc

Just out of curiosity why? I ask because in over a decade of using Access I
have never needed a query with sequential numbers like this yet the question is
asked ALL the time.

In a form you have a number in the navigation pane that tells you where you are
at in the set and in a report you can easily do this since reports are
inherently sequential in their processing (queries are not).

Since a query is seldom used as a stand-alone object but rather to feed data to
a form or report then I have to ask again...what purpose does this sequential
number serve that requires that it exist in the query itself?

 

[Guest]

I have had several occasions where a field reporting the record number was
required. One example would be a database recording runners as they cross the
finish line. Since the awards are based on sequential finish times, the
record number is handy to have in the query itself.

Oli
03.30.06 2.14 hst

 

[Guest]

Try this:

1) In the table (let's call it "MyDatabase"), include a field defined as an
AutoNumber. This will assign a sequential number for each record. Let's call
the field "AutoNo".

2) Since deleting records can leave gaps in this field, create a query for
the table, and add a field called "RecNo" that uses [AutoNo] to create a
record counter. Here's what the formula would look like:

RecNo: dcount("[AutoNo]", "MyDatabase", "AutoNo < " & [AutoNo]) + 1

Of course, RecNo is a dynamic number, so as previous records are deleted,
any particular existing higher RecNo will decrease accordingly.

I hope this helps.

Oli
03.30.06 12.36 hst