Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-03-2019, 06:08 AM   #1
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 77
Thanks: 19
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Sequentially numbering records in a query

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?

miacino is offline   Reply With Quote
Old 10-03-2019, 06:18 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,597
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sequentially numbering records in a query

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-03-2019, 06:21 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Sequentially numbering records in a query

Check the responses in the Similar Threads at the bottom of the page.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
miacino (10-03-2019)
Old 10-03-2019, 06:21 AM   #4
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 77
Thanks: 19
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Sequentially numbering records in a query

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])
miacino is offline   Reply With Quote
Old 10-03-2019, 06:22 AM   #5
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,381
Thanks: 11
Thanked 2,278 Times in 2,230 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Sequentially numbering records in a query

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
miacino (10-03-2019)
Old 10-03-2019, 06:30 AM   #6
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 77
Thanks: 19
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Sequentially numbering records in a query

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 is offline   Reply With Quote
Old 10-03-2019, 06:34 AM   #7
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 77
Thanks: 19
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Sequentially numbering records in a query

Got it! I created a sub query with the criteria and then used the formula! Thank you all!

miacino is offline   Reply With Quote
Old 10-03-2019, 06:34 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,597
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sequentially numbering records in a query

Quote:
Originally Posted by miacino View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-03-2019, 06:54 AM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Sequentially numbering records in a query

miacino,

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 10-03-2019, 07:00 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,597
Thanks: 56
Thanked 1,232 Times in 1,213 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sequentially numbering records in a query

Quote:
Originally Posted by miacino View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sequentially number records sheederj Queries 16 01-10-2007 03:20 PM
Sequentially numbering the records in a query andrefrancis Queries 2 01-27-2006 05:02 PM
[SOLVED] Numbering Records In Query jf03cg Queries 3 08-12-2005 11:20 AM
Returning records sequentially Lamb2087 Queries 2 10-28-2003 02:02 PM
[SOLVED] Sequentially Numbering Entries bmg Queries 0 12-19-2002 11:57 AM




All times are GMT -8. The time now is 01:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World