How Join two Queries without losing any data?

[jezjez]

Hi

I need some sort of Union Join for 2 query results; What i need (and cant get) is this:

I have 2 queries:

query 1
user record_set_a
fred 5
colin 2
tom 0
paul 3

query 2
user record_set_b
fred 3
paul 0
harold 2
tom 4

and the result i need is:

user reca recb
fred 5 3
colin 2 0
tom 0 4
paul 3 0
harold 0 2

This will then finally be sent into a report with various % calculations.
All types of Union Joins etc i try seem to remove duplicates, nulls, etc or just ignore one figure etc etc

help much appreciated..

thanks
Duncan

 

[Len Boorman]

Not sure I understand what you are trying to achieve but


if you use UNION ALL then duplicates will be retained.


Len B

 

[Jon K]

You can union two outer joins:

SELECT [Query 1].user, val(nz([Query 1].record_set_a)) as reca,
val(nz([Query 2].record_set_b)) as recb
FROM [Query 1] Left JOIN [Query 2] ON [Query 1].user = [Query 2].user
UNION
SELECT [Query 2].user, val(nz( [Query 1].record_set_a)),
val(nz([Query 2].record_set_b))
FROM [Query 1] Right JOIN [Query 2] ON [Query 1].user = [Query 2].user;
.

 

[jezjez]

thanks guys - i'll take a look...

 

[jezjez]

Jon K - you are a genius!

this is the second time i've posted this particular problem and you're the first person to come up with a fix; and it worked first time..

many many thanks...