I don't think this is as easy as it sounds. You have a few options though (none of them real pretty).
Each time you run the query you could create a table with all the fields you need plus an AutoNumber field. Then import the records into your table.
Or you could do something like this:
Public Sub test2()
Dim rst As Recordset
Set rst = CurrentDb.QueryDefs("query2").OpenRecordset
Do While Not rst.EOF
'code to insert records into your table goes here
'AbsolutePosition is 0 based so add 1.
Debug.Print rst.AbsolutePosition + 1
Create an AutoNumber in your BIG table. The records will automatically be numbered when appended. To know what append query they came from, each append query should have a calculated field:
Needless to say, the BIG table should have the same field...
Numbering the records in a query is difficult, time consuming, needs special conditions to be met and in my opinion, useless...
Thank you, all, for your input. I can't believe it's so hard! I couldn't use the autonumber because I needed the count to reset for each append query... but I think we're going with a different approach entirely.
I would be very interested in knowing what you ended up doing with this. I have an Access 97 database and we are tracking families waiting for services. As they meet all the criteria, they go onto the Waiting List. As a slot opens, the next available family is taken off the Waiting List. I want an easy way to look up a family and display their current position on the Waiting List. So I need a counter that can be re-set to #1 each time I update the table.
I use macros, action queries, and a little Visual Basic that I copy from "real" programmers.
If you found a good way to get a "row number" I would be very interested in learning what it was.