How to assign table name to be value of a field in a UNION query?

H

Herbert Chan

Hello,

I have multiple tables and I want to union them together using a UNION
query. There are the following fields for those tables:

WO_ID, Item_ID

I want to UNION these tables together into a single table:

WO_ID, Item_ID, Layer

For the Layer field, if those records are from table "INV_A", I want all
those records to have "INV_A" as the field value for Layer in the UNION
query, i.e., Layer stores the name of the table from which the records come
from.

Is it possible to do this using SQL? Or must I use VBA to build up the
table?

Herbert
 
A

Allen Browne

SELECT WO_ID, Item_ID, Layer, "Table1" AS TheSource
FROM Table1
UNION ALL
SELECT WO_ID, Item_ID, Null AS Layer, "Table2" AS TheSource
FROM Table2;
 
M

Manoj Rai

Thanks

Allen Browne said:
SELECT WO_ID, Item_ID, Layer, "Table1" AS TheSource
FROM Table1
UNION ALL
SELECT WO_ID, Item_ID, Null AS Layer, "Table2" AS TheSource
FROM Table2;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top