MINUS operator

[mahmudich]

Hi guys!

It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:

Table1

Book Agent
----------
b1 a1
b2 a2
b3 a3

Table2

Book Agent
----------
b1 a1
b3 a3

The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.

Any comments welcome, thank you

 

[Jon K]

After building a query using the Find Unmatched Query Wizard, you can switch the query to SQL View and add the link for the other field in the ON Clause i.e.

SELECT [Table1].[Book], [Table1].[Agent]
FROM Table1 LEFT JOIN Table2 ON [Table1].[Book] = [Table2].[Book] AND [Table1].[Agent] = [Table2].[Agent]
WHERE ([Table2].[Book] Is Null);
.

 

[mahmudich]

...I should've tried that..
Thank you