Creating an Auto ID in a Query

  • Thread starter Thread starter fireytech
  • Start date Start date
F

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.
 
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.
 
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?
 
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
 
Back
Top