Help with exclude of two fields against another table

[accessmatt]

Hello,



I have two fields in table A that I want to match against table B in order to exclude any matches and only find the unique in column A. I tried the Query Wizard for Find Unmatched and then added my second field because that only matches one as standard but that didn't work. Can anyone have a look at my query and advise. Basically I'm trying to exclude based on a check of two fields.example.accdb

Thanks,

[ItsMe]

You were using two left joins and including both tables in the where clause. So you got unexpected results.

Maybe
WHERE ((([Table 2].[Unique ID Field A]) Is Null) AND (([Table 2].[Unique ID Field B]) Is Null));

or this using the Or operator
WHERE ((([Table 2].[Unique ID Field A]) Is Null) OR (([Table 2].[Unique ID Field B]) Is Null));

Is what you desire

[accessmatt]

Hello, I used OR but it's still not working as expected which is that nothing would show if there was a match on *either* of those fields. Anyone else have any ideas?

[ItsMe]

...nothing would show if there was a match on *either* of those fields...

WHERE ((([Table 2].[Unique ID Field A]) Is Null) AND (([Table 2].[Unique ID Field B]) Is Null));

[accessmatt]

nope. here's my code:

SELECT Union_ALL.[Unique Field A], Union_ALL.[Unique Field B], Union_ALL.[Vendor Unique Id], Union_ALL.PRODUCT, Union_ALL.Content, Union_ALL.[Content 2], Union_ALL.Date, Union_ALL.METRIC, Union_ALL.Vendor, Union_ALL.Key
FROM Union_ALL LEFT JOIN [Target List] ON (Union_ALL.[Unique Field A] = [Target List].Unique Field A) AND (Union_ALL.[Unique Field B] = [Target List].Unique Field B)
WHERE ((([Target List].Unique Field A) Is Null)) AND ((([Target List].Unique Field B) Is Null));

[ItsMe]

You did not mention that you are employing a UNION ALL statement and it was not in your sample. Perhaps if you review the sample with the WHERE clause I provided it will shed some light. If a UNION is created, perhaps you can nest the following SQL within, as an alias.

SELECT [Table 1].ID, [Table 1].[Unique ID Field A], [Table 1].[Unique ID Field B]
FROM [Table 1] LEFT JOIN [Table 2] ON ([Table 1].[Unique ID Field B] = [Table 2].[Unique ID Field B]) AND ([Table 1].[Unique ID Field A] = [Table 2].[Unique ID Field A])
WHERE ((([Table 1].[Unique ID Field B]) Is Null) AND (([Table 2].[Unique ID Field A]) Is Null));

[accessmatt]

ItsME, i appreciate your trying and I see that it's working in your example but this isn't working in my real query:

SELECT Union_ALL.[Vendor Unique A], Union_ALL.[Vendor Unique Id], Union_ALL.PRODUCT, Union_ALL.Content, Union_ALL.[Content 2], Union_ALL.Date, Union_ALL.METRIC, Union_ALL.Vendor, Union_ALL.Key, [Target List].Vendor Unique B
FROM Union_ALL LEFT JOIN [Target List] ON (Union_ALL.[Vendor Unique B] = [Target List].NPI_NUM) AND (Union_ALL.[Vendor Unique A] = [Target List].Vendor Unique A)
WHERE ((([Target List].Vendor Unique B) Is Null) AND (([Target List].Vendor Unique A) Is Null));

[ItsMe]

I thought you were using the UNION ALL operator and did not realize you had a table named Union_All. Take a look at your left join. You have your union_all table described as the table with the extra records and the Target List table as the table with less records. Then, in the WHERE clause, you have it opposite.

Maybe
WHERE ((([Union_ALL].Vendor Unique B) Is Null) AND (([Union_ALL].Vendor Unique A) Is Null));