How to find mismatches in two tables

[sadhana86]

can some one help me with this. I want a Query, where it will compare two tables say Table 1 as Controloperator and Table 2 as Teamleader. Both have same Fields - Date completed, Type of control and Name. if both the values in the tables are same then it should throw a message saying approved. might have records not in table2
and table2 might also have records not in table1
If there are mismatch then mismatches should be come as a result of the query with the table name. Say record 1 is missing in Table1. can some help.

Below is the Query :
SELECT c.ID, c.Date_completed, c.Type_of_Control, IIf(Teamleader.ID Is Null Or Controloperator.ID Is Null,"Not Matching","Approved") AS Status
FROM (Controloperator RIGHT JOIN (SELECT * FROM Controloperator UNION select * from Teamleader) AS c ON (Controloperator.Type_of_Control = c.Type_of_Control) AND (Controloperator.Date_completed = c.Date_completed) AND (Controloperator.ID = c.ID)) LEFT JOIN Teamleader ON (c.Type_of_Control = Teamleader.Type_of_Control) AND (c.Date_completed = Teamleader.Date_completed) AND (c.ID = Teamleader.ID);

It is showing error message as "The Specified field 'Controloperator.Date_completed' could refer to more than one table listed in the FROM clause of your SQL statement. Kindly help. And I also need the table name from where the mismatch happend should also reflect in my query.

[MMcCarthy]

OK I'd take a completely different approach, your query is too complicated. I think the following will give you what you want.

SELECT c.ID, c.Date_completed, c.Type_of_Control, "Approved" AS Status
FROM Controloperator c INNER JOIN TeamLeader t
ON c.Type_of_Control = t.Type_of_Control
AND c.Date_completed = t.Date_completed
AND c.ID = t.ID
UNION
SELECT c.ID, c.Date_completed, c.Type_of_Control, "Not Matched" AS Status
FROM Controloperator c LEFT JOIN TeamLeader t
ON c.Type_of_Control = t.Type_of_Control
AND c.Date_completed = t.Date_completed
AND c.ID = t.ID
WHERE t.ID Is Null
UNION
SELECT t.ID, t.Date_completed, t.Type_of_Control, "Not Matched" AS Status
FROM TeamLeader t LEFT JOIN Controloperator c
ON t.Type_of_Control = c.Type_of_Control
AND t.Date_completed = c.Date_completed
AND t.ID = c.ID
WHERE c.ID Is Null

The first query will give all records that are matching in both tables. The second will give all records in Controloperator without match in TeamLeader and the third will give all records in TeamLeader without a match in Controloperator.

[sadhana86]

Many thanks for your response, However it is asking for the parameter value for date completed and Type of control. But both the tables can have different dates and different types of control. For eg below is the data:

In "Controloperator" table :

ID Date Completed Type of Control
1 21/09/2010 Control1
2 22/09/2010 Control2
3 23/09/2010 Control3

In "TeamLeader" table :

ID Date Completed Type of Control
1 21/09/2010 Control1
2 22/09/2010 Control2
3 24/09/2010 Control4

So considering the above say 23 rd and 24 th as working day. In table Controloperator 24/09/2010 record is missing and in table Teamleader 23/09/2010 record is missing. So my Result should be as below.

First Msg Box popping up as " Not approved" as there is a mismatch.

Then the result of the query must be as below:

ID Date Completed Type of Control Table Name
3 23/09/2010 Control3 Controloperator
3 24/09/2010 Control4 TeamLeader.

If all records are matching in two tables then it has to pop up message box saying "Approved". Please Help.

[MMcCarthy]

Does this make any difference?

SELECT c.ID, c.[Date completed], c.[Type of Control], "Approved" AS Status
FROM Controloperator c INNER JOIN TeamLeader t
ON c.[Type of Control] = t.[Type of Control]
AND c.[Date completed] = t.[Date completed]
AND c.ID = t.ID
UNION
SELECT c.ID, c.[Date completed], c.[Type of Control], "Not Matched" AS Status
FROM Controloperator c LEFT JOIN TeamLeader t
ON c.[Type of Control] = t.[Type of Control]
AND c.[Date completed] = t.[Date completed]
AND c.ID = t.ID
WHERE t.ID Is Null
UNION
SELECT t.ID, t.[Date completed], t.[Type of Control], "Not Matched" AS Status
FROM TeamLeader t LEFT JOIN Controloperator c
ON t.[Type of Control] = c.[Type of Control]
AND t.[Date completed] = c.[Date completed]
AND t.ID = c.ID
WHERE c.ID Is Null

[sadhana86]

But, I need the table name. Now the result for the above query is :

ID Date Completed Type of Control
3 23/09/2010 Control3
3 24/09/2010 Control4

Nmae of the table is missing. What i want is the below result:

ID Date Completed Type of Control Table Name
3 23/09/2010 Control3 Controloperator
3 24/09/2010 Control4 TeamLeader.

[MMcCarthy]

This should do it ...

SELECT c.ID, c.[Date completed], c.[Type of Control], "Approved" AS Status, "Both" AS TableName
FROM Controloperator c INNER JOIN TeamLeader t
ON c.[Type of Control] = t.[Type of Control]
AND c.[Date completed] = t.[Date completed]
AND c.ID = t.ID
UNION
SELECT c.ID, c.[Date completed], c.[Type of Control], "Not Matched" AS Status, "Controloperator" AS TableName
FROM Controloperator c LEFT JOIN TeamLeader t
ON c.[Type of Control] = t.[Type of Control]
AND c.[Date completed] = t.[Date completed]
AND c.ID = t.ID
WHERE t.ID Is Null
UNION
SELECT t.ID, t.[Date completed], t.[Type of Control], "Not Matched" AS Status, "TeamLeader" AS TableName
FROM TeamLeader t LEFT JOIN Controloperator c
ON t.[Type of Control] = c.[Type of Control]
AND t.[Date completed] = c.[Date completed]
AND t.ID = c.ID
WHERE c.ID Is Null

[sadhana86]

Great Awesome thanks :-)