How do I create an Autonumber field in a query?

[Amy Badgett]

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

[mshmyob]

If you wish to number your query row results just throw in a COUNT(*) or COUNT([ColumnName]) in your select query.

cheers,

[Amy Badgett]

Okay, for example... in a query like:

SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone
FROM Clients
GROUP BY Clients.[First Name], Clients.[Last Name], 
Clients.Phone , Clients.[Client ID];
 

Where would that "Select Count([Client ID])" go?

[mshmyob]

It would just be another column in your column selection.

SELECT COUNT([Client ID]), Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone 
FROM Clients 
GROUP BY Clients.[First Name], Clients.[Last Name],  
Clients.Phone , Clients.[Client ID]; 
 

cheers,

[Amy Badgett]

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?

[Rabbit]

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.

[mshmyob]

Sorry try something like this if it is not in a report.

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])
FROM Clients
GROUP BY tblB.[First Name], tblB.[Last Name],  
tblB.Phone , tblB.[Client ID]; 
 

cheers,