Numbering rows sequentialy in a query

I want the row number to shown in a column of a query. Starting at 1 and continuing until the end of the records
DatabaseDekAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
In a Report, you can create a 'Running Sum" in a Text Box using a Control Source of
=1
Then (with this text box visible) ... 'records'  in the Detail Section will start at 1 and increment forward.

Simple
0
 
rspahitzCommented:
Unfortunately, this features is not built into Access.

However, if you want to create a custom function, you can get that in a query.

The function would be added to a new VBA module with this:

Function RowNumber(TableName As String, ID As Long) As Long
    Dim cnt As Long
    cnt = DCount("*", TableName, "ID <= " & ID)
    RowNumber = cnt
End Function

Open in new window


then your query would be include something like this:

SeqNum: RowNumber("tblTest",[ID])

Where tblTest is the name of the table and [ID] is the name of the primary key in that table.
0
 
Dale FyeCommented:
Funny that you are asking this question now.  I just this morning answered a similar question, although in that case the user wanted to update a column with a sequential number.

Take a look at my response.  Keep in mind, you will have to reset the value each time you run the query.  Also, I only use this in a MakeTable, or Update query because as you scroll through the query, forwards and backwards, the sequential numbers will get recomputed.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
aikimarkCommented:
Is there an autonumber field?

Is there some relationship between the rows' IDs returned by the query?
0
 
DatabaseDekAuthor Commented:
Thank you.

Would it be simpler to use a make table query and base the report on that?
Can a make table query generate an increment field.
0
 
rspahitzCommented:
yes, but you don't need a MakeTable...you can simply add a new field into your current table and set it as AutoNumber (then probably set it as primary key).
when you save, the field will be populated with values from 1 to the number of records, then increment as new records are added.

The only caveat is that the numbers are not guaranteed to be in exact sequence since it will skip numbers if someone started to create a record then cancels (escapes) leaving that ID unused.
0
 
DatabaseDekAuthor Commented:
As I say the query does not use all of the numbers from the table.

What I am trying to do is print labels. The labels have a product code. But if I print 50 pages of labels with 10 per page how would you find an individual label. If I could number the labels then I can also print a numbered reference to the labels that would be printed in order and would subsequently be easy to find by the incremental label number.
0
 
DatabaseDekAuthor Commented:
That was so simple had to give the points to MX.

But my thanks to you all. I will try be more exact in future
0
 
Dale FyeCommented:
If you had told us in your original question that this was for a report, it would have saved a lot of time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.