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

[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

 

[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;

 

[Manoj Rai]

Thanks

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;