merging 21 tables into 1 in an mdb file

[Greg Gardner]

I have an mdb file in which I have created 21 unique
tables, all with the same format of 14 columns, the same
type of data in each column within the 21 tables. I would
like to merge all of these 21 tables into 1 individual
table for easier sorting and parsing of data. I am not
understanding if this is done by Join or Link or how
exactly I can do this in the easiest way, and would
welcome any assistance anyone could shed on this.
Thanks very much,
Greg

 

[Rebecca Riordan]

Greg,

Use an append query.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[John Vinson]

I have an mdb file in which I have created 21 unique
tables, all with the same format of 14 columns, the same
type of data in each column within the 21 tables. I would
like to merge all of these 21 tables into 1 individual
table for easier sorting and parsing of data. I am not
understanding if this is done by Join or Link or how
exactly I can do this in the easiest way, and would
welcome any assistance anyone could shed on this.
Thanks very much,
Greg

There are two ways to do this (laudable!) project:

- 21 Append queries

- A single APPEND query based on a UNION query

To create the UNION query, create a new query based on one of your
tables; select all the fields. Select SQL view using the leftmost
toolbar icon or the View menu option. Copy and paste the query to read
something like

SELECT this, that, theother, ... FROM MyTable1
UNION ALL
SELECT this, that, theother, ... FROM MyTable2
UNION ALL
SELECT this, that, theother, ... FROM MyTable3

<etc>

for all 21 tables. (You can do this text editing in another text
editor and paste back into the SQL window if you prefer). Check that
this query returns all the records you expect, and then base an Append
query on it to populate your new master table.

 

[Greg Gardner]

John,
thanks VERY much! I got all of the information compiled
into one query, with all of the records successfully and
perfectly transferred into one table. However, I seem to
be having some difficulty with the APPEND command. I
wrote the SQL syntax as follows:
SELECT ID, A, B, ..., FROM 1
UNION ALL
....
SELECT ID, A, B, ..., FROM 21
APPEND TO 22;

In this example, I wanted 22 to be my new "Master Table,"
as opposed to having a query, but I keep getting the
error message "Syntax error in FROM clause"
If I only end with "APPEND" then it just brings the
information into the query, and not a new table. Any idea
how to avoid this SQL error and successfully run the
APPEND command?
I tried running a UNION ALL at the end of my last SELECT
ID... line and I got a different error message of:
Invalid SQL statement;
expected 'DELETE','INSERT','PROCEDURE','SELECT',
or'UPDATE'.

Any thoughts? Thanks again very much!
Greg

 

[John Vinson]

John,
thanks VERY much! I got all of the information compiled
into one query, with all of the records successfully and
perfectly transferred into one table. However, I seem to
be having some difficulty with the APPEND command. I
wrote the SQL syntax as follows:
SELECT ID, A, B, ..., FROM 1
UNION ALL
...
SELECT ID, A, B, ..., FROM 21
APPEND TO 22;

In this example, I wanted 22 to be my new "Master Table,"
as opposed to having a query, but I keep getting the
error message "Syntax error in FROM clause"

You need TWO QUERIES. Save the Union query as uniAllTables; then
create a *second* query

INSERT INTO [22]
SELECT * FROM uniAllTables;

 

[Greg Gardner]

John,
What else can I say,
you've saved my day!
The queries are run,
the table is done!
"The Cat in the Hat,"
as a flic may be flat,
but this guy is elated
with the instructions you've stated!
Happy Accessing!
Greg

-----Original Message-----

John,
thanks VERY much! I got all of the information compiled
into one query, with all of the records successfully and
perfectly transferred into one table. However, I seem to
be having some difficulty with the APPEND command. I
wrote the SQL syntax as follows:
SELECT ID, A, B, ..., FROM 1
UNION ALL
...
SELECT ID, A, B, ..., FROM 21
APPEND TO 22;

In this example, I wanted 22 to be my new "Master Table,"
as opposed to having a query, but I keep getting the
error message "Syntax error in FROM clause"

You need TWO QUERIES. Save the Union query as uniAllTables; then
create a *second* query

INSERT INTO [22]
SELECT * FROM uniAllTables;



.

 

[John Vinson]

John,
What else can I say,
you've saved my day!
The queries are run,
the table is done!
"The Cat in the Hat,"
as a flic may be flat,
but this guy is elated
with the instructions you've stated!
Happy Accessing!
Greg

You've just made this MVP's day:
your poetry blows me away!
I'm glad I could aid
the "advancement of trade"
so now I can log off and play!