Append two tables to a third blank table 1

[chaos18]

I've attempted unsuccessfully twice to append, well really once to append and once using the 2 tables to make a new table. Anyway, the append query makes more logic to me though i am not real familar with sql, but i have had significantly more success with creating a new table from the existing two. For reference i created a smaller version of the database which contains only 15 records. if you would like a copy let me know and I'll be glad to email it to you. any suggestions on how i may be able to make either idea work?
Thankyou

-Thomas Gunter-

 

[Steve101]

Thomas,

It depends on what you're trying to do here, as to what the most appropriate approach might be. Here are a couple of pointers:

(a) The SQL for a simple table append is:
[tt]
INSERT INTO tblDestination
SELECT *
FROM tblSource;
[/tt]
This will copy all records from the tblSource table to the tblDestination table.

(b) Use this technique to append as many tables as you like to the tblDestination table. For example, you could have three queries, defined as follows:

qryDelete:
[tt]
DELETE *
FROM tblDestination
[/tt]

qryAppend1:
[tt]
INSERT INTO tblDestination
SELECT *
FROM tblSource1;
[/tt]
qryAppend2:
[tt]
INSERT INTO tblDestination
SELECT *
FROM tblSource2;
[/tt]

These three queries could be respectively defined in the query window, then run in order, to achieve theend result of appending tblSource1 and tblSource2.

(c) In VBA code, the above process could be fully automated, with the option of coding the SQL into the procedure, without having to define the queries as separate query objects. Let us know if you need more details on doing this.

(d) Finally, if you just wish to append two transaction tables to each other for regular reporting purposes, without the requirement to retain the merged tables, you might consider the use of the SQL UNION operator. This effectively allows two (or more) tables to be "unioned" in a single SQL statement. This generally takes the form:
[tt]
SELECT *
FROM tblSource1
UNION
SELECT *
FROM tblSource2
[/tt]
Select Field lists, Order by and criteria can also be applied as required.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[chaos18]

thankyou for the tips...the union query had the end result of what i desired and worked excellently. Thankyou again!

-Thomas Gunter-

 

[chaos18]

If i may be of some bother again, how would i take the union query and make it into a new table?

-Thomas Gunter-

 

[Steve101]

You need to do this in two steps:

(a) Save the Union query as a query; I'll call it qryUnion, and

(b) Create a new "Make Table" query, I'll call it "qryMake", with the following SQL:
[tt]
SELECT *
INTO tblYourNewTable
FROM qryUnion;
[/tt]

This uses the first query definition to make the new table.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)