Multiple Simultaneous Appends?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have five tables - A, B, C, D, E (referring to 5 points in time) which
contain the same variables. Each table contains information for each ID over
the five time points A, B, C, D, E. I want to join all tables (i.e. append
B,C,D, and E to A) to produce one grand table called “AtoEâ€, so that the
information for each ID over time is contained in the same variables. At the
moment I have to go through a long process of:

1) Creating a new Table A with a Different Name i.e. Table A+B, which
initially contains just As information.
2) Creating an Append query to Add A to B and putting the result in Table
A+B e.g. as follows:

INSERT INTO [TableA+B] ( ID, Variable1, Variable2, Variable3 )
SELECT TableB.ID, TableB.Variable1, TableB.Variable2, TableB.Variable3
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID;

3) Repeating the equivalent step 4 times.
(i.e. after appending B unto A+B, then append C unto A+B+C, then D unto
A+B+C+D etc.)

Since, I and others will need to go through this process several times, Is
there a way of doing this all in one go i.e. via a single SQL statement or a
visual basic module?

I’d appreciate any advice on this,

Thanks in advance,

Eric Van Lente
(e-mail address removed)
 
If the tables have similar fields, you can use a union query to create a
combined recordset of your 5 tables.
SELECT ID, Variable1, Variable2, Variable3
FROM tblA
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblB
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblC
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblD
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblE

Use this union query as the source to create a table of tblAtoE.

I trust your tables are properly normalized and you understand your
application much better than I do.
 
Thank you very much Duane! That was very helpful.

Eric

Duane Hookom said:
If the tables have similar fields, you can use a union query to create a
combined recordset of your 5 tables.
SELECT ID, Variable1, Variable2, Variable3
FROM tblA
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblB
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblC
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblD
UNION ALL
SELECT ID, Variable1, Variable2, Variable3
FROM tblE

Use this union query as the source to create a table of tblAtoE.

I trust your tables are properly normalized and you understand your
application much better than I do.

--
Duane Hookom
MS Access MVP


EVL said:
Hi,

I have five tables - A, B, C, D, E (referring to 5 points in time) which
contain the same variables. Each table contains information for each ID
over
the five time points A, B, C, D, E. I want to join all tables (i.e. append
B,C,D, and E to A) to produce one grand table called "AtoE", so that the
information for each ID over time is contained in the same variables. At
the
moment I have to go through a long process of:

1) Creating a new Table A with a Different Name i.e. Table A+B, which
initially contains just As information.
2) Creating an Append query to Add A to B and putting the result in Table
A+B e.g. as follows:

INSERT INTO [TableA+B] ( ID, Variable1, Variable2, Variable3 )
SELECT TableB.ID, TableB.Variable1, TableB.Variable2, TableB.Variable3
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID;

3) Repeating the equivalent step 4 times.
(i.e. after appending B unto A+B, then append C unto A+B+C, then D unto
A+B+C+D etc.)

Since, I and others will need to go through this process several times, Is
there a way of doing this all in one go i.e. via a single SQL statement or
a
visual basic module?

I'd appreciate any advice on this,

Thanks in advance,

Eric Van Lente
(e-mail address removed)
Click to expand...
Click to expand...
 
Back
Top