Efficient way to compare data in 3 tables and only show the discrepancy

[beginneraccess]

Is there a way to triple check that the data is consistent between the three tables and show if there are any discrepancies in any of the three tables? Each table is not completely the same but they all have an EmployeeID, amount, datepaid, and CID column. When I did with two tables I thought I had the SQL as:

SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.CID
FROM tbMaster LEFT JOIN tbCID ON (tbMaster.CID = tbSUID.CID) AND (tbMaster.Amount = tbCID.Amount) AND (tbMaster.DatePaid = tbCID.DatePaid)
WHERE (((tbMaster.[Advance/paid])="Paid Respondent") AND ((tbCID.CID) Is Null));

It worked but if the tbCID had any discrepancies it did not catch it...

So for the three tables I thought:

SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.[Advance/paid], tbMaster.CID
FROM tbMaster, tbCID, table3
WHERE tbMaster.CID <> tbCID.CID
OR  table3.CID <> tbMaster.CID
OR  table3.CID <> tbCID.CID
OR tbMaster.Amount <> tbCID.Amount
OR  table3.AMOUNT <> tbMaster.AMOUNT
OR  table3.AMOUNT <> tbCID.AMOUNT
OR tbMaster.Datepaid <> tbCID.Datepaid
OR  table3.DATEPAID <> tbMaster.DATEPAID
OR  table3.DATEPAID <> tbCID.DATEPAID
 

But there is only 30 entries yet I get 5x or 6x copies and get over 3000 rows/entries in the query...

[twinnyfo]

BA,

You would NEVER want to create tables like that. You should never have three tables that have duplicate entries like that. All you need is the one table that has that information and have the EmployeeID be the foreign key to that table from any other tables.

As you have demonstrated, this is the reason you don’t duplicate values, because now you have to determine if there are any discrepancies.

[beginneraccess]

Thank you for your response twinnyfo. I agree I would rather just handle one table. Unfortunately I do not control the tables being added and was informed they wanted to triple check the amount, date, and ID so have three tables where they will ask the employees to fill 3x...
I guess I can append query all three tables into one table then do an unmatch query?

[twinnyfo]

1. You are working on the DB, correct? How come you do not control the tables?

2. If the data is in the tables once, there is no need to triple check any data.

3. If I was an employee I would be asking, "Why do I have to enter this three times? It's the same data!"

4. If you are running an append query, with the same data across three tables, again, there is no need to either triple check or run an "unmatch" query--whatever that may be, becauase the data is (should be) the same.

[beginneraccess]

Hi twinnyfo
I am just someone who makes the frame and am told what to try to make it do. The tables are one for before they pay, when they pay, and a table of receipts to check if what they say they will pay/have paid is what the receipt says? if that makes sense. Yes we are hoping that the data should be the same but I guess there were instances where someone fat fingered an extra digit and got paid extra.

[beginneraccess]

I think I got it!

SELECT tbMaster.EmployeeID, tbMaster.Amount, tbMaster.DatePaid, tbMaster.[Advance/paid], tbMaster.CID
FROM (tbMaster LEFT JOIN tbCID ON (tbMaster.DatePaid = tbCID.DatePaid) AND (tbMaster.Amount = tbCID.Amount) AND (tbMaster.CID = tbCID.CID)) LEFT JOIN table3 ON (tbMaster.CID = table3.CID) AND (tbMaster.DatePaid = table3.DatePaid) AND (tbMaster.Amount = table3.Amount)
WHERE (((tbMaster.[Advance/paid])="Paid Respondent") AND ((tbCID.CID) Is Null)) OR (((tbMaster.[Advance/paid])="Paid Respondent") AND ((table3.CID) Is Null));
 

Thank you!

[twinnyfo]

Have fun with this. As your records and requirements expand, this structure will become your primary headache. I don't tell my bosses how to set employee policy. They don't try to tell me how to design the structure of my tables.

[Rabbit]

Be careful here, that query might not do what you think it does. That query only tells you if there are paid respondent records in the master table that does not exist in table CID or table 3. It does not check the reverse of that situation and it does not check table CID against table 3.

With 3 tables, there are 6 scenarios for missing records, your query only checks 2 of those 6.