I am having some issues creating the SQL coding to create a union query.
I have 10 tables named 1998- 2007. Each table has the same fields(21 fields
to be exact) but I would like to create a union with all tables into one
master table. Can someone help me with this?
Thanks!
I would actually create an eleventh table, tblMaster, and run ten Append
queries. Put a field in tblMaster for the year (don't use the reserved word
Year for its name though) and just create an append query
INSERT INTO tblMaster(TheYear, thisfield, thatfield, theotherfield)
SELECT 1998 As TheYear, thisfield, thatfield, theotherfield
FROM [1998];
Run the query, edit it to use 1999 in both places and run it again, until
you're done. For a one-time operation this will be quicker than building a
union query.
If you do want to build a UNION query, you need the SQL window. First create a
query based on [1998], including all the fields, and then select View... SQL.
Edit the SQL text to something like
SELECT this, that, theother FROM [1998]
UNION ALL
SELECT this, that, theother FROM [1999]
UNION ALL
SELECT this, that, theother FROM [2000]
UNION ALL
<and so on>
Since you chose not to post any description of your tables I can't be more
specific, other than to warn you that if you have Autonumber ID's in the
tables you will need to come up with some way of ensuring that you don't
create duplicates ID's in tblMaster. One way to do so is to include an
autonumber Primary Key in tblMaster and simply don't include it in the append
query. This can cause problems if your tables have links to child tables -
post back with a fuller description of your tables if that's the case.
