Get the data that didn't fulfill the query

Question

I have two tables that has information about some contacts, I'm trying to build some queries to match these contacts together if they exist under the following criteria:

1. Matching FirstName,LastName and Email
2. Matching FirstName and Email
3. Matching LastName and Email

I built a query to get the first criteria which is pretty straight forward, now for the second criteria I want match based on the data that was left from the first criteria(I don't want the second query to contain data that is already in the first one)

I wrote this query to get the data that was not in the first query but it broke the application (I'm using access DB as a business requirement)

I know this query is wrong but can you please help me or guide me on how to solve the second query.

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA, TableB
WHERE TableB.[UIR No] NOT IN (SELECT TableB.[UIR No] FROM TableA, TableB WHERE (((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email) And ((TableA.[First Name])=TableB.[First Name]));)
AND
TableA.[Contact ID] NOT IN(SELECT  TableA.[Contact ID] FROM TableA, TableB WHERE (((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email) And ((TableA.[First Name])=TableB.[First Name])););

Answer 1

I solved it in a really simple way, gave me the same results as the previous answer

for query 1: I used

SELECT TableA.[First Name], TableB.[First Name], TableB.[Last Name], TableA.[Last Name], TableA.Email, TableB.Email, TableA.[Contact ID], TableB.[UIR No], TableB.[Ver No]
FROM TableA, TableB
WHERE (((TableA.[First Name])=TableB.[First Name]) And ((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email));

for query 2: I used

SELECT TableA.[Last Name], TableB.[Last Name], TableB.Email, TableA.Email, TableA.[First Name], TableB.[First Name], TableA.[Contact ID], TableB.[UIR No], TableB.[Ver No]
FROM TableA, TableB
WHERE (((TableA.[Last Name])=TableB.[Last Name]) And ((TableA.Email)=TableB.Email) And ((TableA.[First Name])<>TableB.[First Name]));

for query 3: I used

SELECT TableA.[First Name], TableB.[First Name], TableB.[Last Name], TableA.[Last Name], TableA.Email, TableB.Email, TableA.[Contact ID], TableB.[UIR No], TableB.[Ver No]
FROM TableA, TableB
WHERE (((TableA.[First Name])=TableB.[First Name]) And ((TableA.[Last Name])<>TableB.[Last Name]) And ((TableA.Email)=TableB.Email));

Answer 2

In cases like this I tend to prefer breaking down the queries into individual "base queries" and saving them. Then I create "result queries" based on the "base queries". In this case I would start by creating the "base queries" for the three ways of matching:

[qry_base_1_matchFirstLastEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.Email = TableB.Email) AND (TableA.[Last Name] = TableB.[Last Name]) AND (TableA.[First Name] = TableB.[First Name]);

[qry_base_2_matchFirstEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.Email = TableB.Email) AND (TableA.[First Name] = TableB.[First Name]);

[qry_base_3_matchLastEmail]:

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA INNER JOIN TableB ON (TableA.[Last Name] = TableB.[Last Name]) AND (TableA.Email = TableB.Email);

Then I can use those as the source for the "result queries" where step (2) omits the results from step (1), and step (3) omits the results from both (2) and (1):

Step 1: Match on [First Name], [Last Name] and [Email]

We already have that as one of our "base queries" so we have to choose between simply renaming qry_base_1_matchFirstLastEmail to qry_result_1_matchOnlyFirstLastEmail (which might be confusing), or creating a new qry_result_1_matchOnlyFirstLastEmail that simply returns the results from qry_base_1_matchFirstLastEmail (which is a bit inefficient). I'm easily confused, so I'll just go with the second option

[qry_result_1_matchOnlyFirstLastEmail]:

SELECT qry_base_1_matchFirstLastEmail.*
FROM qry_base_1_matchFirstLastEmail;

Step 2: Match on just [First Name] and [Email]

We want to take the results from qry_base_2_matchFirstEmail and eliminate the ones that we already identified in step 1:

[qry_result_2_matchOnlyFirstEmail]:

SELECT qry_base_2_matchFirstEmail.[Contact ID], qry_base_2_matchFirstEmail.[UIR No]
FROM qry_base_2_matchFirstEmail LEFT JOIN qry_base_1_matchFirstLastEmail ON (qry_base_2_matchFirstEmail.[UIR No] = qry_base_1_matchFirstLastEmail.[UIR No]) AND (qry_base_2_matchFirstEmail.[Contact ID] = qry_base_1_matchFirstLastEmail.[Contact ID])
WHERE (((qry_base_1_matchFirstLastEmail.[Contact ID]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[UIR No]) Is Null));

Step 3: Match on just [Last Name] and [Email]

We want to take the results from qry_base_3_matchLastEmail and eliminate the ones that we already identified in steps 1 and 2:

[qry_result_3_matchOnlyLastEmail]:

SELECT qry_base_3_matchLastEmail.[Contact ID], qry_base_3_matchLastEmail.[UIR No]
FROM (qry_base_3_matchLastEmail LEFT JOIN qry_base_2_matchFirstEmail ON (qry_base_3_matchLastEmail.[UIR No] = qry_base_2_matchFirstEmail.[UIR No]) AND (qry_base_3_matchLastEmail.[Contact ID] = qry_base_2_matchFirstEmail.[Contact ID])) LEFT JOIN qry_base_1_matchFirstLastEmail ON (qry_base_3_matchLastEmail.[UIR No] = qry_base_1_matchFirstLastEmail.[UIR No]) AND (qry_base_3_matchLastEmail.[Contact ID] = qry_base_1_matchFirstLastEmail.[Contact ID])
WHERE (((qry_base_2_matchFirstEmail.[Contact ID]) Is Null) AND ((qry_base_2_matchFirstEmail.[UIR No]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[Contact ID]) Is Null) AND ((qry_base_1_matchFirstLastEmail.[UIR No]) Is Null));

(That last one may look a bit scary, but paste the SQL into the Access query designer and you'll see that it's really nothing to be afraid of.)

Answer 3

A join may be better.

SELECT DISTINCT TableA.[Contact ID], TableB.[UIR No]
FROM TableA
INNER JOIN TableB
ON TableA.FirstName=Tableb.FirstName 
AND TableA.Email=Tableb.Email
WHERE TableB.[UIR No] NOT IN 
   (SELECT TableB.[UIR No]
    FROM TableA
    INNER JOIN TableB
    ON TableA.FirstName=Tableb.FirstName 
    AND TableA.LastName=Tableb.LastName 
    AND TableA.Email=Tableb.Email);

Note that INNER JOIN includes only records that match in both tables.