Union Query Help

[Def7]

Is there a way to create a field within the results of a Union Query that shows the name of the table that record came from?

 

[CT Witter]

You could manually include an additional field in your query:
"Table1" as TableSource

I don't know of a way to make it self-documenting where the query pulls in the name to the table without user intervention.

You could automatically do it if you were generating the query in vba.

 

[Def7]

I have a field on each table already called Project, so the query already comes up with that field, but it is blank since it is blank in the table. I want the query to populate in what table the record is coming from. For example "Project 1 Table" or "Project 2 Table". Is there a way to do that?

 

[james_lankford]

select 
  "table 1 - " & table1.project as table_project
from 
  table 1

union all

select 
  "table 2 - " & table2.project as table_project
from 
  table 2

 

[Def7]

ADVERTISEMENT

I can't get it to work. I keep getting an error message. This is how I have the code for the entire query:

TABLE [Table 1]
UNION SELECT * FROM [Table 2]
UNION SELECT * FROM [Table 3]
UNION SELECT * FROM [Table 4]

SELECT "Table1 - " & Table1.project as table_project
FROM Table 1
SELECT "Table2 - " & Table2.project as table_project
FROM Table 2
SELECT "Table3 - " & Table3.project as table_project
FROM Table 3
SELECT "Table4 - " & Table4.project as table_project
FROM Table 4;

 

[james_lankford]

SELECT 
  "Table 1 - " & [Table 1].project as table_project,
  [Table 1].*
FROM 
  [Table 1]

union all

SELECT 
  "Table 2 - " & [Table 2].project as table_project,
  [Table 2].*
FROM 
  [Table 2]

union all

SELECT 
  "Table 3 - " & [Table 3].project as table_project,
  [Table 3].*
FROM 
  [Table 3]

union all

SELECT 
  "Table 4 - " & [Table 4].project as table_project,
  [Table 4].*
FROM 
  [Table 4]

assuming your table names are
[Table 1]
[Table 2]
[Table 3]
[Table 4]

 

[Def7]

Thanks for the help