union query

S

Scott

How do I include a column in a union query that displays the table name where
the data came from in the row. For example, I query CustomerName and Amount
from from Table1, Table2 and Table3 and I want the record or row to display
which table it is from, either Table1, Table2 or Table3. So my query results
would look like this.

Customer Name, Amount, Table
Bob, $25, Table1
John, $30, Table3
Carl, $18, Table2

Thanks,
 
M

Marshall Barton

Scott said:
How do I include a column in a union query that displays the table name where
the data came from in the row. For example, I query CustomerName and Amount
from from Table1, Table2 and Table3 and I want the record or row to display
which table it is from, either Table1, Table2 or Table3. So my query results
would look like this.

Customer Name, Amount, Table
Bob, $25, Table1
John, $30, Table3
Carl, $18, Table2
Click to expand...


SELECT [Customer Name], Amount, "table1" As TableName
FROM table1
UNION
SELECT [Customer Name], Amount, "table2"
FROM table2
UNION
. . .
 
S

Scott

Thank you, it worked great, this is just what I was looking for.

Scott

Marshall Barton said:
Scott said:
How do I include a column in a union query that displays the table name where
the data came from in the row. For example, I query CustomerName and Amount
from from Table1, Table2 and Table3 and I want the record or row to display
which table it is from, either Table1, Table2 or Table3. So my query results
would look like this.

Customer Name, Amount, Table
Bob, $25, Table1
John, $30, Table3
Carl, $18, Table2
Click to expand...


SELECT [Customer Name], Amount, "table1" As TableName
FROM table1
UNION
SELECT [Customer Name], Amount, "table2"
FROM table2
UNION
. . .
Click to expand...
 
Top