Appending 8 tables into 1 table in Access 97 1

[roddy17]

hi
I'm not an experienced programmer, but i've learned basically how to use an append query. What i would like to do is append 8 tables into one master table in Access 97, without creating 8 separate append queries. Each of these 8 tables is receiving a download from a different dataset. Each table is getting information with the same field structure though - e.g. cust. number, cust. name, item number, and number of items.
i think that i could create an append query to pull the data from all 8 tables simultaneously and merge them into the one master table, but they need have relationships joining them.
the other question is, is there a way to have the append query automatically engage once all 8 tables have received their downloads?

 

[tlbroadbent]

Rather than a JOIN query, create a UNION query.

Select custnumber, custname, itemnumber, numberofitems
From table1
Union
Select custnumber, custname, itemnumber, numberofitems
From table2
Union
Select custnumber, custname, itemnumber, numberofitems
From table3
Union
.
.
.

Save that query - qryUnionEightTables - and use the query in an APPEND query.

Insert Into FinalTbl (custnumber, custname, itemnumber, numberofitems)
Select custnumber, custname, itemnumber, numberofitems
From qryUnionEightTables;

Save that query and execute it at the end of the import process. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin