Returning records not there!

[Les Isaacs]

Hi All (and MSAccessRookie in particular )
This is a re-post of a thread I started yesterday, but which is presumably one of many that have been lost due to a power outage at the site's data centre.
The problem was that I have 2 tables [live] and [proposed] that have identical fields, but with no key field, and I need a report that will return any records that either

1. are in [live] but not in [proposed], or
2. are in [proposed] but not in [live], or
3. are in both tables, but with different values in field 'pay' or 'tax' or'NIC'

For this purpose, corresponding records are where 'employee' and 'period' are equal (effectively, these 2 fields are key fields).

I'd had a reply (from MSAccessRookie) suggesting a union query is the way to go, so I now have:

SELECT proposed.employee, proposed.period, live.pay, live.tax, live.NIC
FROM live LEFT JOIN proposed ON (live.period = proposed.period) AND (live.employee = proposed.employee)
WHERE (((proposed.employee) Is Null) AND ((proposed.period) Is Null))
UNION ALL
SELECT live.employee, live.period, proposed.pay, proposed.tax, proposed.NIC
FROM live RIGHT JOIN proposed ON (live.employee = proposed.employee) AND (live.period = proposed.period)
WHERE (((live.employee) Is Null) AND ((live.period) Is Null))
UNION ALL
SELECT proposed.employee, proposed.period, [proposed].[pay]-[live].[pay] AS PayDif, [proposed].[tax]-[live].[tax] AS TaxDif, [proposed].[NIC]-[live].[NIC] AS NICDif
FROM live INNER JOIN proposed ON (live.employee = proposed.employee) AND (live.period = proposed.period)
WHERE (((live.pay)<>[proposed].[pay])) OR (((live.tax)<>[proposed].[tax])) OR (((live.NIC)<>[proposed].[NIC]));

This runs, and almost works, but where records exist in either of the tables but not the other the above query returns nulls in the 'employee' and 'period' fields (with the 'pay', 'tax' and 'NIC' values from whichever table has the record).
I must be close - but can't seem to get this right
Thanks for any further help.
Les

 

[Les Isaacs]

Hi All
It's all fixed
I can't believe I didn't get it sooner: obviously null 'employee' and 'period' values were going to be returned as I was selecting them from the part of the query where null was set as criteria :banghead:
All sorted now.
Thanks
Les