Creating an Auto ID in a Query

[fireytech]

I have a query that needs to pull data from 3 tables (1 table holds
the vendor (technician's) name and skill set, 1 table holds all the
vendor contact data, and 1 table that holds a list of zip codes that
the vendor covers. All tables are linked by the vendorID. Since
there may be multiple records in the contacts and coverage tables, how
do create a unique ID for the query? Is it possible to have some auto-
generate id create within the query itself? Somehow I need a unique
number to be assigned to each line of the query.

 

[Allen Browne]

Turn the query into an Append query, and write the results to a temporary
table that has an AutoNumber field.

This article discusses your options:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html
but I think the temp. table will be the most productive approach.

 

[fireytech]

Thanks, Allen. Your solution worked as I need. A followup question I
have is: Our vendor database changes constantly so obviously if I just
run the append query it will add more records to the temporary table.
Is there a way to setup an automatic dump of the temporary table
before I append it? It would be nice to setup a button off the
switchboard (or something like that) for my non-technical employees to
click one button that would 1. Delete all the old records in the temp
table, and 2. Run the append query. Is that possible?

 

[Allen Browne]

Put code something like this into the Click event procedure of the command
button:

Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM [MyTempTable];"
db.Execute strSql, dbFailOnError
strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) inserted."
Set db= Nothing