Solved

Numbering rows sequentialy in a query

Posted on 2015-01-31
9
116 Views
Last Modified: 2015-01-31
I want the row number to shown in a column of a query. Starting at 1 and continuing until the end of the records
0
Comment
Question by:DatabaseDek
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40581732
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40581758
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40581850
Is there an autonumber field?

Is there some relationship between the rows' IDs returned by the query?
0
 

Author Comment

by:DatabaseDek
ID: 40581864
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 22

Expert Comment

by:rspahitz
ID: 40581867
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
 

Author Comment

by:DatabaseDek
ID: 40581893
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 40581947
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
 

Author Closing Comment

by:DatabaseDek
ID: 40581985
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40582004
If you had told us in your original question that this was for a report, it would have saved a lot of time.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now