Report structure

[Former Member]

I have two tables that list names of clients. One is for USA and the is Europe. I'd like to display the names in my report from each table in the same column. Can this be done? If so how?

[Former Member · Accepted solution]

The only way I see you doing this is to use a SQL command query that combines queries from each table together using a "UNION statement. You will have to make sure both queries have the same number of columns with matching data types in the same order.

Here's an example

SELECT 
    USA_CLIENT_ID AS CLIENT_ID,
    USA_CLIENT_NAME AS CLIENT_NAME,
    USA_CLIENT_DATA AS CLIENT_DATA
FROM USA_TABLE
UNION
SELECT
    EUR_CLIENT_ID AS CLIENT_ID,
    EUR_CLIENT_NAME AS CLIENT_NAME,
    EUR_CLIENT_DATA AS CLIENT DATA
FROM EUR_TABLE

[ido_millet · Accepted solution]

Assuming that

a) you want to see an indication of row source

b) you don't need to remove duplicate rows

you can tweak the excellent suggestion above to:

SELECT 
    USA_CLIENT_ID AS CLIENT_ID,
    USA_CLIENT_NAME AS CLIENT_NAME,
    USA_CLIENT_DATA AS CLIENT_DATA,
    'USA' as Source
FROM USA_TABLE
UNION ALL
SELECT
    EUR_CLIENT_ID AS CLIENT_ID,
    EUR_CLIENT_NAME AS CLIENT_NAME,
    EUR_CLIENT_DATA AS CLIENT DATA,
    'EUR' as Source
FROM EUR_TABLE

[Former Member · Accepted solution]

Thanks it worked.