Union Queries - Column Headers

[Bev]

I have the following union query

select distinct [trx date], [total] from [qry_a]

UNION all select distinct [trx date] ,[total] from [qry_b];

It does what I want, except it doesn't tell me what table it relates. What I
would like is:
Qry_A.trx date Qry_A.total Qry_B.trx date Qry_B.total

is there a way to do this?

 

[Petr Danes]

You can do it with literals:

select "Qry_A", distinct [trx date], [total] from [qry_a]
UNION all
select "Qry_B", distinct [trx date] ,[total] from [qry_b];

It's not exactly the way you described, but it will identify each row to the
query from which it came.

Petr

 

[gabi]

I have the following union query

select distinct [trx date], [total] from [qry_a]

UNION all select distinct [trx date] ,[total] from [qry_b];

It does what I want, except it doesn't tell me what table it relates. What
I
would like is:
Qry_A.trx date Qry_A.total Qry_B.trx date Qry_B.total

is there a way to do this?

 

[John W. Vinson]

I have the following union query

select distinct [trx date], [total] from [qry_a]

UNION all select distinct [trx date] ,[total] from [qry_b];

It does what I want, except it doesn't tell me what table it relates. What I
would like is:
Qry_A.trx date Qry_A.total Qry_B.trx date Qry_B.total

is there a way to do this?

Not with a UNION query, no; a UNION query joins tables "end to end", and
you're trying to join the tables "side by side".

If there is one record for each [trx date] in each table you can use a simple
JOIN:

SELECT [qry_a].[trx date] AS qryADate, [qry_a].[total] AS qryATotal,
[qry_b].[trx date] AS qryBDate, [qry_b].[total] AS qryBTotal
FROM qry_a INNER JOIN qry_b
ON qry_a.[trx date] = qry_b.[trx date];

If there are missing records in one table or the other you can use LEFT and
RIGHT OUTER JOINS instead of the Inner Join; if there are multiple records for
a date, you're in trouble! Access must have some way to know which record goes
with which, based on the contents of the record, not its sequential position.