How to find matching rows in two tables

[sadhana86]

I have two Tables say Table 1 and Table 2.
Table 1 has following:

ID Date Type
1 20/09/2010 Invoice
2 21/09/2010 Credit

Table 2 has follwing:

ID Date Type
1 20/09/2010 Invoice
2 21/09/2010 Credit

So my Query has to give output if all the field that is ID,Date,Type are matching it has to throw a message box approved else it should say Not matching. Please help.

[Delerna]

You haven't provided enough info to give a complete example

will table2 be the table that has missing records that are in table 1

or is it
table1 that has the missing records

or is it
table1 might have records not in table2
and table2 might also have records not in table1

I will assume the easiest and you can adjust it to suit your situation

Basically you join the 2 tables on each field and then test for null fields to determine if the record is missing or not

Im going to assume that table1 always has every record and table2 might be missing some

SELECT a.ID,
       a.Date,
       a.Type,
       iif(b.ID is null,"Approved","Not Matching") as Status
FROM Table1 a
LEFT JOIN Table2 b
  ON a.ID=b.ID
 AND a.Date=b.Date
 AND a.Type=b.Type
 

There are "find matching" and find unmatching query wizards that you can try.
It will write a query similar to mine

[sadhana86]

Hi Delerna,Many thanks for your reply. Unfortunately its the second situation you have mentioned.That is, table1 might have records not in table2
and table2 might also have records not in table1.

If there are no mismatch then it should throw a message "Approved" or it will show not matching and highlights the records that are not matching in both the tables.

Please help.

[sadhana86]

And If i give the above coding in the query design under expression it is not working. Kindly guide me. should I write a event on this.

[Delerna]

then make a union query to get a single source of all records

SELECT *
FROM Table1
UNION select *
from Table2
 

Call this query qryCombined.



now you want my first query but like this

SELECT a.ID, 
       a.Date, 
       a.Type, 
       iif(b.ID is null or a.ID is null,"Not Matching","Approved") as Status 
FROM qryCombined c
LEFT JOIN Table1 a 
     ON c.ID=a.ID AND c.Date=a.Date AND c.Type=a.Type 
LEFT JOIN Table2 b 
     ON c.ID=b.ID AND c.Date=b.Date AND c.Type=b.Type 
 

[Delerna]

Or you can combine it all into one query

SELECT a.ID, 
       a.Date, 
       a.Type, 
       iif(b.ID is null or a.ID is null,"Not Matching","Approved") as Status 
FROM (SELECT * FROM Table1 UNION select * from Table2) c
LEFT JOIN Table1 a 
     ON c.ID=a.ID AND c.Date=a.Date AND c.Type=a.Type 
LEFT JOIN Table2 b 
     ON c.ID=b.ID AND c.Date=b.Date AND c.Type=b.Type 
 
 

Note, because I used UNION and not UNION ALL
Each unique record will only appear once in the combined query

[Delerna]

This comment

If there are no mismatch then it should throw a message "Approved"

intrigues me.

If you mean what I think you mean then you will probably need to resort to reading recordsets in VBA for that particular aspect

[sadhana86]

Many thanks for your immediate response. Sorry but I am a beginner in access can u ellaborate by step by step like after i link columns in two table then step 2 like that.

[Delerna]

in access goto the query defs tab

create a new query

close the select tables window but keep the query editor window

In the toolbar at the top there is a dropdown with "Design View","SQL View" options

Select the SQL View option

Copy and paste my code into it

SELECT a.ID,  
       a.Date,  
       a.Type,  
       iif(b.ID is null or a.ID is null,"Not Matching","Approved") as Status  
FROM (SELECT * FROM Table1 UNION select * from Table2) c 
LEFT JOIN Table1 a  
     ON c.ID=a.ID AND c.Date=a.Date AND c.Type=a.Type  
LEFT JOIN Table2 b  
     ON c.ID=b.ID AND c.Date=b.Date AND c.Type=b.Type  
 

close and save the query




Providing the table and field names in your post are the actual names then hopefully the query will run

[sadhana86]

Hi There, I tries the above query in the SQl, However it is showing a error message showing "Syntax error (Missing Operator) in query expression".

[sadhana86]

Hi there, I tried the above query however it is showing a error message as "Syntax error (Missing Operator) in Query expression ".

[Delerna]

can you post your query

[sadhana86]

Hi There, I tried the below query and it is showing a error message as "Syntax error (Missing Operator) in Query expression ".

SELECT a.ID,
a.Date,
a.Type,
iif(b.ID is null or a.ID is null,"Not Matching","Approved") as Status
FROM (SELECT * FROM Table1 UNION select * from Table2) c
LEFT JOIN Table1 a
ON c.ID=a.ID AND c.Date=a.Date AND c.Type=a.Type
LEFT JOIN Table2 b
ON c.ID=b.ID AND c.Date=b.Date AND c.Type=b.Type

[sadhana86]

can some one help mw eith this. I want a Query, where it will compare two tables say Table1 and Table2. Both have same Fields - Date, Type and Name. if both the values in the tables are same then it should throw a message saying approved.table1 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.
Note: As I am beginner request you to provide step by step reply. many thanks

[Delerna]

Sorry, this thread got overlooked mixed in with all the others I am attempting to answer

try this
I rewrote it starting with the union query and then using the wizard to join the two tables

I don't know why Access objected to the original query???

SELECT c.ID, c.Date, c.Type, 
      IIf(Table2.ID Is Null Or Table1.ID Is Null,"Not Matching","Approved") AS Status
FROM (Table1  
RIGHT JOIN (SELECT * FROM Table1 UNION select * from Table2) AS c ON (Table1.Type = c.Type) AND (Table1.Date = c.Date) AND (Table1.ID = c.ID)) 
LEFT JOIN Table2  ON (c.Type = Table2.Type) AND (c.Date = Table2.Date) AND (c.ID = Table2.ID);
 

[sadhana86]

Wow excellent this is working now. Can we add the field table name in the query where it states from which table the mismatch is. Say Record 1, (date),(type) (Not matching), (Table Name)

[sadhana86]

Hi there, Now I am replacing the coding with my actual field and table names. My table1 name is Controloperator and Table 2 name is Teamleader. My fields in both the tables are: ID, Date completed, Type of Control.

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);

But when I run this query it shows a error message showing "The Specified field 'Controloperator.Date_completed' could refer to more than one table listed in the FROM clause of your SQL statement. Please help