Multiple Exclude / "Unmatched" Query | Access 2010

Question

I am using Access 2010, trying to do an exclude via the UI (2nd join type and then "Is Null") but am trying to do that for 3 fields.

Specifically:

I need access to return any records from one table where a match is not found in 3 fields.

Phone, Email and Company are the fields and I need access to return any records in table 1 where Phone does not match in table 2 AND Email does not match in table 2 AND Company does not match in table 2.

Any ideas?

I have tried to do a triple type-2 join with "Is Null" in each criteria like a typical exclude but it is returning more not fewer matches (acting like an OR instead of AND by the looks of it)

Update: thank you to Hans for the solution. I have included a picture of it to help others in my situation

Answer 1

Do you want to return all records for which there is no match on the combination of the fields Phone, Email and Company? If so, create a triple outer join as you have done.

But if you want to return all records for which there is no match on Phone, and also no match on Email and no match on Company, you need to add the second table three times to the query.

Create an outer join to the first instance on Phone (with Is Null in the criteria), an outer join to the second instance on Email (also with Is Null in the criteria) and an outer join to the third instance on Company (ditto).

Answer 2

I need access to return any records from one table where a match is not found in 3 fields.

Phone, Email and Company are the fields and I need access to return any records in table 1 where Phone AND Email AND Company do not match in table 2.

You need to join the tables on all three columns, using a LEFT OUTER JOIN (option 2 in the dialogue), but you only need to test for NULL on the key of table 2, or on any column which is defined NOT NULL (Required property = True in Access-Speak).  If you switch to SQL view the query should look something like this:

SELECT [Table1] .*

FROM [Table1] LEFT JOIN [Table2]

ON [Table1].[Phone] = [Table2].[Phone]

AND [Table1].[Email] = [Table2].[Email]

AND [Table1].[Company] = [Table2].[Company]

WHERE  [Table2].[CustomerID] IS NULL;

where CustomerID is the Primary key of Table2.

Answer 3

You sir are a genius. Thank you!

Answer 4

Hi Hans,

Since you were so helpful with my previous problem I was wondering if you could help me out with what is basically the opposite problem.

I now have a table that I need to join to another table based on 3 fields and I need records returned where either(OR) field matches.

So if I am matching on Phone # and Company Name, for example, I want to see records returned where the Phone # matches between the tables OR the Company Name.

If I simply join these two pairs of fields, the join acts as an AND and only shows records that matches for both fields instead of either field.

Any ideas?

Answer 5

Like this:

The SQL statement is:

SELECT TableOne.ID, TableOne.Company, TableOne.Phone

FROM TableOne, TableTwo

WHERE TableOne.Company=TableTwo.Company OR TableOne.Phone=TableTwo.Phone

Any name containing spaces or punctuation/unusual characters must be enclosed in square brackets [ ], e.g. [Table One] and [Phone#]