Row Number in Query of Query

[chrisl]

I've seen messages in the group, most recently last month, about adding
row numbers to a query. I'm looking to do the same but with a twist -
I've already sorted the data and have it in a query with no single
unique key field.

I'll give you my reasoning for numbering before someone questions my
motives. I use a label printing program that seems to change the order
of the data because it "sorts" based on one key field. My query in an
Access database does its sorting based on three fields. By having a
unique, numbered field would keep my data in the correct order for
printing.

I've seen other methods that rely on adding row numbers if the query
you're using queries a table directly. Well, my query culminates
several tables and other queries. My query does not have one unique
field - my data requires several fields to make a proper key.

I do have programmatic access to the database and can therefore program
something (Access 2003), however, I would like the label program to
trigger any function and then return the results through ODBC or OLE.
Is there any other methods out there that can help?

Thanks in advance if I'm beating a dead horse.

-Chris

 

[Allen Browne]

Chris, the simplest solution here might be to use a temporary table with an
AutoNumber. Your query can populate the table with the records you want. The
AutoNumber will also populate. You can then use the temporary table for your
label printing program, and tell it to sort on the autonumber.

1. Create a table with the same fields as your query, plus an AutoNumber.
Save as (say) tzLabel.

2. Open your query in design view.
Change it to an Append query (Append on Query menu.)
Answer the dialog you want to append to tzLabel.
Access adds the append row to the design grid.
Map the fields to the table.
Save the query.

3. Create a form with a command button for "Prepare Labels".
Set the buttons On Click property to [Event Procedure].
Click the Build button beside this. Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, set up the code like this:

Dim db As DAO.Database
Set db = dbEngine(0)(0)
'Clear out the label table.
db.Execute "DELETE FROM tzLabel;", dbFailOnError
db.Execute "Query1", dbFailOnError

Use your query name instead of Query1 in the last line.

4. Use tzLabel as the source table for your label software.