469,326 Members | 1,359 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

How do I create an Autonumber field in a query?

I need to create an Autonumber field in a query I am currently running. The query right now calls fields such as names, addresses, phone numbers and is distinct on the client id. Each time I run the query I am calling for different sets of records off of the client id (ie: id's 50000-55000 or 32000-37000). Each time I run the query I need it to number the rows 1, 2, 3, 4, 5, ...5,000.
I hope this is understandable and someone can help.
Thanks
Feb 24 '11 #1
6 55353
mshmyob
904 Expert 512MB
If you wish to number your query row results just throw in a COUNT(*) or COUNT([ColumnName]) in your select query.

cheers,
Feb 24 '11 #2
Okay, for example... in a query like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone
  2. FROM Clients
  3. GROUP BY Clients.[First Name], Clients.[Last Name], 
  4. Clients.Phone , Clients.[Client ID];
  5.  
Where would that "Select Count([Client ID])" go?
Feb 25 '11 #3
mshmyob
904 Expert 512MB
It would just be another column in your column selection.

Expand|Select|Wrap|Line Numbers
  1. SELECT COUNT([Client ID]), Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone 
  2. FROM Clients 
  3. GROUP BY Clients.[First Name], Clients.[Last Name],  
  4. Clients.Phone , Clients.[Client ID]; 
  5.  
cheers,
Feb 25 '11 #4
Okay, so when I add the count, it just gives me a whole column of 1's, like it stops counting at the first record. Is there anyway to incorporate a loop? Or is that not right?
Feb 25 '11 #5
Rabbit
12,516 Expert Mod 8TB
Is this going into a report? You can just use a running sum in the report. If not, you'll need to use a subquery ranking.
Feb 25 '11 #6
mshmyob
904 Expert 512MB
Sorry try something like this if it is not in a report.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblB.[Client ID], tblB.[First Name], tblB.[Last Name], tblB.Phone, (SELECT COUNT(*) FROM Clients AS tblA WHERE tblA.[Client ID] <= tblB.[Client ID])
  2. FROM Clients
  3. GROUP BY tblB.[First Name], tblB.[Last Name],  
  4. tblB.Phone , tblB.[Client ID]; 
  5.  
cheers,
Feb 26 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by ben.werdmuller | last post: by
1 post views Thread by S. van Beek | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.