Returning a chronological record number for each record?

[Guest]

Hello,

I'm using Access 2003. I have a query that returns a number of fields from
various tables. The query results for example could be:

Jim Green Cleveland
Sue Orange Chicago
Bob Blue Montreal

I would like to add another field to the query that assigns a "record
number" to each record, always starting at 1. Such the query results would
be:

Jim Green Cleveland 1
Sue Orange Chicago 2
Bob Blue Montreal 3
..
..
..

I would like the "record number" to always start with 1. This query gets
run multiple times, with any number of resulting records, and no matter how
many times it's run (results will vary depending on other criteria) ,I'd
still like it to start from 1:

Jim Green Cleveland 1
Sue Orange Chicago 2
Bob Blue Chicago 3
Ed Yellow Shanghai 4

Or if I run the query again:

Tom Black Shanghai 1
Jim Green Cleveland 2
Sue Red Seattle 3
Bob Blue Chicago 4
Angela Red Seattle 5
Sue Green Cleveland 6
Tim Red Seattle 7
Missy Blue Daytona 8
Sue Red Seattle 9

etc...

Seems like something that should be pretty easy, but I don't know how to do
it!

Any ideas?

Thanks,

Scott

 

[Guest]

The only thing I can think of off the top of my head that may work is to use
a make-table query. One of the fields of the table can be an autonumber then
you will get 1,2,3,4 .....

Then when you have exported the information you can then delete the records.
The problem with that is that there isn't an easy way to set the autonumber
back to 1.

 

[Guest]

The query results will be transfered to an Excel file. One of the query
fields will contain an Excel formula. For the 1st record, the simplified
formula is something like

=$X$1

2nd record:

=$X$2

3rd record:

=$X$3

etc

So I would like my query results to ultimately be something like:

Jim Green Cleveland =$X$1
Sue Orange Chicago =$X$2
Bob Blue Chicago =$X$3
Ed Yellow Shanghai =$X$4

So when I transfer the query to an Excel file, it already has the formulas.
Depending on other criteria, the query could have 1,000s of records. So it
would be nice to just be able to say for record 3,445 the formula is =$X$3445

I realize another option is pre-populating an Excel Template file with
formulas, but I am trying to find a way to generate the formulas with an
Access query since it fits into my overall design better.

-Scott

 

[Guest]

Is there a reason why you want to do this?