Display table name as field in query

[Guest]

Does anyone know if there is a way to display the table name as a field in a
query?

I have a UNION query that merges all of the records from 10 different
tables, but I need a field that indicates which table each record comes from.
Any help would be greatly appreciated.

Thanks,
Craig

 

[Marshall Barton]

Does anyone know if there is a way to display the table name as a field in a
query?

I have a UNION query that merges all of the records from 10 different
tables, but I need a field that indicates which table each record comes from.

Just use the table name in a calculated field in each Select
clause:

SELECT "table1" As TableName, f1, f2, ... FROM table1 ...
UNION
SELECT "table2" As TableName, f1, f2, ... FROM table2 ...
. . .

 

[Guest]

If you don't mind hard-coding in the table names, something like this will
work:

SELECT [1stShot] as ShotMonthYear, "ClientShots" as TableName
FROM ClientShots
UNION ALL
SELECT [2ndShot], "ClientShots2"
FROM ClientShots2
UNION ALL
SELECT [3rdShot], "ClientShots3"
FROM ClientShots3 ;

Just make sure to put a name in the first select clause and put the actual
table name in the right place in the following.

 

[Guest]

This is perfect. Thank you both for your help.

If you don't mind hard-coding in the table names, something like this will
work:

SELECT [1stShot] as ShotMonthYear, "ClientShots" as TableName
FROM ClientShots
UNION ALL
SELECT [2ndShot], "ClientShots2"
FROM ClientShots2
UNION ALL
SELECT [3rdShot], "ClientShots3"
FROM ClientShots3 ;

Just make sure to put a name in the first select clause and put the actual
table name in the right place in the following.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Does anyone know if there is a way to display the table name as a field in a
query?

I have a UNION query that merges all of the records from 10 different
tables, but I need a field that indicates which table each record comes from.
Any help would be greatly appreciated.

Thanks,
Craig