Sequentially numbering records in a query (1 Viewer)

miacino

Registered User.
Local time
Today, 01:03
Joined
Jun 5, 2007
Messages
106
I can't seem to find an answer to this, if there is one.

I am trying to assign a sequential number to each output query row.

i.e.,
ID Name
5 Bob
8 Tom
3 Joe

I want:

ID Name
1 5 Bob
2 8 Tom
3 3 Joe


Has anyone found a way to do this, directly in the query?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
Yes! You can either use a subquery or a VBA function to assign the sequence number, but either method may require you to have a unique way to identify and order your records, like an ID field.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:03
Joined
Jan 23, 2006
Messages
15,361
Check the responses in the Similar Threads at the bottom of the page.
 

miacino

Registered User.
Local time
Today, 01:03
Joined
Jun 5, 2007
Messages
106
Thanks DB Guy. There is a unique ID for each record. Not sure exactly how I would do what you suggested.

I tried this in the query but does not accurately work:

RowNum: DCount("[ID]","[tableName]","[ID]<=" & [ID])
 

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,611
You can add sequential numbers to a query. However, it will never be in the manner you described.

This is all predicated on order. You must order your data in a manner and then numbers are assigned based on that order. The example you described is not in order, therefore Bob will never assigned 1 while Tom is assigned 2 while Joe is assigned 3.

The best way to do this is to assign them by ID--because it will be unique which guarantees a unique order. The query for that is this:

Code:
SELECT ID, [Name], DCount("[ID]", "YourTableNameHere", "[ID]<=" & [ID])
FROM YourTableNameHEre
ORDER BY ID

It's output based on your sample data will be this:

1, 3, Joe
2, 5, Bob
3, 8, Tom

Lastely, this will be a computation intensive query; the larger YourTableNameHere is the more processing time it will take to order and number your data.
 

miacino

Registered User.
Local time
Today, 01:03
Joined
Jun 5, 2007
Messages
106
RowNum: DCount("[ID]","[tableName]","[ID]<=" & [ID])

This works, except if I have a criteria in the query, the RowNum is not starting at "1".
 

miacino

Registered User.
Local time
Today, 01:03
Joined
Jun 5, 2007
Messages
106
Got it! I created a sub query with the criteria and then used the formula! Thank you all!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
RowNum: DCount("[ID]","[tableName]","[ID]<=" & [ID])

This works, except if I have a criteria in the query, the RowNum is not starting at "1".
Which means you'll probably need VBA solution. You could try out this one.
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:03
Joined
Jan 23, 2006
Messages
15,361
miacino,

Perhaps you could post your solution so others may find it in future.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
Got it! I created a sub query with the criteria and then used the formula! Thank you all!
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom