Display table name as field in query

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

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
 
Craig said:
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.
Click to expand...


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 ...
. . .
 
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.
 
This is perfect. Thank you both for your help.

Jerry Whittle said:
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.


Craig said:
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
Click to expand...
Click to expand...
 
Back
Top