Compare two tables and select records based on unmatch rules

[rustynails]

It's a pretty straightforward requirement. We have two tables, A and B. Both of them have one field called Hours. I have to select Hours from either A or B depending on few criteria.





The criteria is as follows:

If A.Hours = B.Hours, reject records
If A.Hours <> B.Hours, select A.Hours
If A.Hours <> B.Hours, select NEXT B.Hours where B.Hours > A.Hours
If A.Hours <> B.Hours AND B.Hours = A.Hours+1, reject records

Based on the attachment I'm providing, I want the result set to look like this (except the grey fields which I added for clarity):



I'm also attaching Access 2000 mdb file. I dont want to do this all in one query...if it helps to break the query into 3 or 4 different pieces, then I'm all for it. Any help is super appreciated

[June7]

Query1:
SELECT A.Hours, DMin("Hours","B","Hours>" & [Hours]) AS BgreaterA FROM A
WHERE (((DLookUp("Hours","B","Hours=" & [Hours])) Is Null) AND ((DLookUp("Hours","B","Hours=" & [Hours]+1)) Is Null));

Query2:
SELECT Val(Hours) As Hrs FROM Query1
UNION SELECT Val(BgreaterA) FROM Query1;

[rustynails]

Query1:
SELECT A.Hours, DMin("Hours","B","Hours>" & [Hours]) AS BgreaterA FROM A
WHERE (((DLookUp("Hours","B","Hours=" & [Hours])) Is Null) AND ((DLookUp("Hours","B","Hours=" & [Hours]+1)) Is Null));

Query2:
SELECT Val(Hours) As Hrs FROM Query1
UNION SELECT Val(BgreaterA) FROM Query1;

You are amazing June7. Thank you