Trying to generate a count field within a query

[MacroScope]

I'm using a Make Table query to create a new table that has only email addresses for a mass mailing. Those addresses will be transferred to an Excel spreadsheet and then imported into Outlook as a group. We have nearly 3,000 email addresses, and our mass mail program only allows 1,000 mails to be sent at one time.

I would like to create a query field that generates a numeric count of the records up to that point. In other words, the first record in the query would start at number 1, and the last record would be 2972. I can then easily sort the records to groups of 1,000. I'm sure there's an easy way to do it but I'm just not seeing it.

All assistance appreciated.

 

[PHV]

Create the table with an AutoNumber field.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

I tried that, but because no similar field exists in the Make Table Query it just makes the table using the available field and deletes the field I created. I created an auto-number field I called Count and then created a field in the table I'm drawing data from, also called Count. There's no data in it, but I brought it into the Make Table query anyway. It changed the data type in the table from Auto-number to Number, and of course it was a null field.

I'd be happy to use Auto-number if I could just get it to cooperate!

 

[PHV]

Create the new table with an AutoNumber field and then use an Append query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thanks. I'll give that a try tomorrow. I think it'll probably work!

 

[MacroScope]

I'm pretty sure that the Auto-number and the Append Query will solve the problem for me, but I'm curious. Is there a way to dynamically generate sequential numbers within the query? Now I'm just curious!

 

[dhookom]

You can create a ranking query with sequential numbers as long as there is some uniqueness of the values in the record that can determine the order.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Thanks. Can you explain how to do it? The only field I'm outputting is an email address field, plus the sequential count field.

 

[dhookom]

Here is an example using the Products table in the Northwind sample application:

SELECT Products.ProductName, (SELECT Count(*) FROM Products P WHERE P.ProductName <= Products.ProductName) AS Rank
FROM Products
ORDER BY Products.ProductName;

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Thanks, PHV. I used your solution and it worked fine.

Thanks for the additional info Duane. I'll play with that. Even if I don't use it now I've learned something new.