I’m trying to take 2 tables and exclude the data on one from the other. For example, I have a master list Tbl_ReportDetail and I want to exclude those in a different list ARC_List. I believe I can do this with a UNION query but my query pulls in both fields. Can you tell me where I might be going wrong?
SELECT Tbl_ReportDetail.SoldId, ARC_List.CustomerNumber FROM Tbl_ReportDetail LEFT JOIN ARC_List ON Tbl_Detail.SoldId = ARC_List.CustomerNumber UNION SELECT Tbl_ReportDetail.SoldId, ARC_List.CustomerNumber FROM Tbl_ReportDetail RIGHT JOIN ARC_List ON Tbl_Detail.SoldId = ARC_List.CustomerNumber WHERE ARC_List.CustomerNumber IS NULL;
UNION is a set operator, and returns a result set that contains all the distinct rows in the two result sets. The ISO SQL set operator you want is called MINUS, but Access does not support this.
Nay mind - there's an alternative:
SELECT Tbl_ReportDetail.SoldIdFROM Tbl_ReportDetail
WHERE EXISTS (SELECT * FROM ARC_List WHERE Tbl_Detail.SoldId = ARC_List.CustomerNumber)