join type

[Guest]

I have 2 tables I want to run a query on, "Balances" & "Budget". Both table
have a common field, Center #. I want to show data from the Balances table if
no corresponding value exists in the Budget table & show data from the Budget
table is no corresponding value exists in Balances. I thouhgt someone told me
I had to do a Union query or something but can't remember.

Dan

 

[Guest]

hi,
accually i think you may need three. 2 unmatched queries
at least. there is a wizard for that. just click new query
and select unmatched. if you want to see the results in 1
query, then use the union query.

 

[Ken Snell [MVP]]

You need a Union query like this:

SELECT Balances.Field1, Balances.Field2, "Balances" AS SourceTable
FROM Balances LEFT JOIN
Budget ON Balances.[Center #] =
Budget.[Center #]
WHERE Budget.[Center #] Is Null
UNION ALL
SELECT B.Field1, B.Field2, "Budget" AS SourceTable
FROM Budget AS B LEFT JOIN
Balances AS BB ON B.[Center #] =
BB.[Center #]
WHERE BB.[Center #] Is Null;