CODEWITHSUNDEEP
sqlms-access
Catalina Radu
Catalina Radu

Reputation: 17

Adding a column to pull in name of the table in union query

I have 2 queries that I want to combine before exporting to Excel. I used a UNION query to do so, which worked great. Now I want to create a column that says the name of the table it came from. What would be the best way to do this? In the 2 separate queries or the joined query?

For reference:

SELECT qry_xxx.Sold_Date, qry_xxx.Sold_Year, qry_xxx.Sold_Month
FROM qry_xxx

UNION SELECT qry_yyy.Sold_Date, qry_yyy.Sold_Year, qry_yyy.Sold_Month
FROM qry_yyy;

Upvotes: 0

Views: 54

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7990

Please try the query below:

SELECT qry_xxx.Sold_Date, qry_xxx.Sold_Year, qry_xxx.Sold_Month, 'qry_xxx' as NameOfTable
FROM qry_xxx

UNION 

SELECT qry_yyy.Sold_Date, qry_yyy.Sold_Year, qry_yyy.Sold_Month, 'qry_yyy' as NameOfTable
FROM qry_yyy;

EDIT:

You can use "group by" after using your first query as a subquery like below:

Select max(Sold_Date) as MaxSoldDate,
       max(Sold_Year) as MaxSoldYear,
       max(Sold_month) as MaxSoldMonth,
       NameOfTable
FROM (
    SELECT qry_xxx.Sold_Date, qry_xxx.Sold_Year, qry_xxx.Sold_Month, 'qry_xxx' as NameOfTable
    FROM qry_xxx

    UNION 

    SELECT qry_yyy.Sold_Date, qry_yyy.Sold_Year, qry_yyy.Sold_Month, 'qry_yyy' as NameOfTable
    FROM qry_yyy;
     ) x
group by NameOfTable

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I am guessing that you do not need to remove duplicates. If so, you should use union all and not union.

Then, just add the table name as a string column:

SELECT 'qry_xxx' as which, qry_xxx.Sold_Date, qry_xxx.Sold_Year, qry_xxx.Sold_Month
FROM qry_xxx
UNION ALL
SELECT 'qry_yyy' as which qry_yyy.Sold_Date, qry_yyy.Sold_Year, qry_yyy.Sold_Month
FROM qry_yyy;

Upvotes: 1

Related Questions