Compare 2 identical tables with different data inputs to spot the differences

[chrisjames25]

Hi. Its been a while so i guess that means with all your help so far my databases are working fairly well at work!!!! Big thanks for that.

At the end of our season we do a stocktake of all the inventory on the nursery. Due to the scale and how spread out it all is we get different people to do the check simultaneously. I now have 2 sets of inputted data in Tbl_StockTake1 and Tbl_StockTake2. Previously we would manually analyse the data and see where discrepancies lie and go and resolve the error.

What i would like to know is is there a way of comparing the 2 tables via a union qurery or another method where i can compare the data. Ideally this data would then be able to be exported to excel for a pivot table.

Key heading in table are Category, Variety, Location, potsize, Total Plants - i also added in a stockj check number with idea 1st check would be 1 and second would be 2 so i could append the tables together and then compare but that doesnt seem to give me the result i want and it is a bit clunky.

Any help massively appreciated.

The key for me is to be look and see for exmaple that stock take 1 counted 1500 raspberries in a 9cm pot in house 9 and stocktake 2 counted 1300 raspberries in a 9cm pot in house 9 and the table tell me there is a 200 plant deiscrepancy between the 2.

 

[Uncle Gizmo]

inventory on the nursery.

I was puzzled at first. An inventory at a nursery?

Re:- small children. ..

And "pot size" why an earth would you record the size of a potty!

But I see now it's a plant nursery!

 

[chrisjames25]

I was puzzled at first. An inventory at a nursery?

Re:- small children. ..

And "pot size" why an earth would you record the size of a potty!

But I see now it's a plant nursery!

Yep when i say i work for a nursery most people instantly go with child nursery not plant nursery!!!! Oddly enough there is a child nursery right next to our plant nursery.

 

[GPGeorge]

Hi. Its been a while so i guess that means with all your help so far my databases are working fairly well at work!!!! Big thanks for that.

At the end of our season we do a stocktake of all the inventory on the nursery. Due to the scale and how spread out it all is we get different people to do the check simultaneously. I now have 2 sets of inputted data in Tbl_StockTake1 and Tbl_StockTake2. Previously we would manually analyse the data and see where discrepancies lie and go and resolve the error.

What i would like to know is is there a way of comparing the 2 tables via a union qurery or another method where i can compare the data. Ideally this data would then be able to be exported to excel for a pivot table.

Key heading in table are Category, Variety, Location, potsize, Total Plants - i also added in a stockj check number with idea 1st check would be 1 and second would be 2 so i could append the tables together and then compare but that doesnt seem to give me the result i want and it is a bit clunky.

Any help massively appreciated.

The key for me is to be look and see for exmaple that stock take 1 counted 1500 raspberries in a 9cm pot in house 9 and stocktake 2 counted 1300 raspberries in a 9cm pot in house 9 and the table tell me there is a 200 plant deiscrepancy between the 2.

I don't have a quick and easy way to do this, but the technique I would suggest is a query with a WHERE clause that compares each field in the first table to the corresponding field in the second table.

e.g.

"WHERE (table1.Category = table2.Category AND table1.Variety AND table2.Variety AND table1.Location = table2.Location AND table1.PotSize = table2.PotSize) AND (table1.TotalPlants <> table2.TotalPlants)

Of course, if you are looking for other variations, they'll need to be included.

I'm also wondering if you might not want to look into a more robust system that connects the data collection interface to a common back end, so that they are both working against a single data source. I demoed such an approach using tablets or smart phones running a PowerApps application.

 

[plog]

The key for me is to be look and see for exmaple that stock take 1 counted 1500 raspberries in a 9cm pot in house 9 and stocktake 2 counted 1300 raspberries in a 9cm pot in house 9 and the table tell me there is a 200 plant deiscrepancy between the 2.

This is going to be a series of queries. For simplicity let's call them table A and B. First add an autonumber primary key to both tables (ID_A and ID_B). Then set up these queries:

Verify1 - A duplicates. Bring down all fields from A, make it an aggregate query (click the summation/sigma symbol in the toolbar). Leave 'Group By' under all the fields except ID_A--change that to Count. Under that ID_A in the criteria section put >1. This will show all the items in A with multiple counts. You must first deal with these and make all the duplicates go away or further queries will have way too many records.

Verify2 - B duplicates. Same as Verify1 but using table B.

Verify3 - A Null check. Bring down all fields from A. Then add this WHERE statement to the SQL:

...WHERE Category IS NULL OR Variety IS NULL OR Location IS NULL OR potsize IS NULL...

that will identify all the records in A that have missing data. You will need to manually fix these because they are wrong and NULLS won't JOIN to other NULLs so you won't be able to match them to B records.

Verify4 - B Null check. Same as Verify3 but using table B


Q1 - Exact matches. INNER JOIN your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. These are all the records that are perfect matches between the two tables.

Q2 - Unmatched table A. LEFT JOIN B to A (show all from A, just matches from B) your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. Under ID_B use the criteria Is Null. These are all the records in A that aren't in B.

Q3 - Unmatched table B. LEFT JOIN A to B (show all from B, just matches from A) your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. Under ID_A use the criteria Is Null. These are all the records in B that aren't in A.

That takes care of all the records and from there it's probably a manually effort of going through the records in Q2 & Q3 and fixing input errors. Were inputters choosing all values from a drop down or manually typing things into every field? Ask 5 random people to spell 'raspberries', I guarantee you'll get at least 3 different spellings. When that goes into a database you will not get matches because to a computer 'rasberries' is different from 'raspberries' is different from 'rapberrys'.

If that's the case I suggest you add more queries to the Verify process. I'd make an aggregate query (Summatiion/Sigma) using A and bring down each field one at a time and running it with the 'Group By' under it. That will show you all the values you have in that field. You'll see that you have a lot of 'duplicates' that aren't exact duplicates (raspberry/rasberry, 9 cm/9cm) that you should fix before you do the matching. Then do the same for every field in B making sure you choose the same correct values as you did in A.

 

[Uncle Gizmo]

Oddly enough there is a child nursery right next to our plant nursery.

I have in mind an image of a father turning up at home with some potted plants instead of the kids!

 

[chrisjames25]

This is going to be a series of queries. For simplicity let's call them table A and B. First add an autonumber primary key to both tables (ID_A and ID_B). Then set up these queries:

Verify1 - A duplicates. Bring down all fields from A, make it an aggregate query (click the summation/sigma symbol in the toolbar). Leave 'Group By' under all the fields except ID_A--change that to Count. Under that ID_A in the criteria section put >1. This will show all the items in A with multiple counts. You must first deal with these and make all the duplicates go away or further queries will have way too many records.

Verify2 - B duplicates. Same as Verify1 but using table B.

Verify3 - A Null check. Bring down all fields from A. Then add this WHERE statement to the SQL:

...WHERE Category IS NULL OR Variety IS NULL OR Location IS NULL OR potsize IS NULL...

that will identify all the records in A that have missing data. You will need to manually fix these because they are wrong and NULLS won't JOIN to other NULLs so you won't be able to match them to B records.

Verify4 - B Null check. Same as Verify3 but using table B


Q1 - Exact matches. INNER JOIN your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. These are all the records that are perfect matches between the two tables.

Q2 - Unmatched table A. LEFT JOIN B to A (show all from A, just matches from B) your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. Under ID_B use the criteria Is Null. These are all the records in A that aren't in B.

Q3 - Unmatched table B. LEFT JOIN A to B (show all from B, just matches from A) your tables on Category, Variety, Location, potsize. Bring down all the fields from both tables. Under ID_A use the criteria Is Null. These are all the records in B that aren't in A.

That takes care of all the records and from there it's probably a manually effort of going through the records in Q2 & Q3 and fixing input errors. Were inputters choosing all values from a drop down or manually typing things into every field? Ask 5 random people to spell 'raspberries', I guarantee you'll get at least 3 different spellings. When that goes into a database you will not get matches because to a computer 'rasberries' is different from 'raspberries' is different from 'rapberrys'.

If that's the case I suggest you add more queries to the Verify process. I'd make an aggregate query (Summatiion/Sigma) using A and bring down each field one at a time and running it with the 'Group By' under it. That will show you all the values you have in that field. You'll see that you have a lot of 'duplicates' that aren't exact duplicates (raspberry/rasberry, 9 cm/9cm) that you should fix before you do the matching. Then do the same for every field in B making sure you choose the same correct values as you did in A.

Will give this a try now and see how far i get and report back. Many thanks

 

[DHookom]

The solution to find unequal field values between two tables is with a long union query (depending on the number of fields to compare). For instance using the Orders table in Northwind and a copy of the table with some changes of values, the SQL would look like:

SELECT O.OrderID, "EmployeeID" as TheFIeld, O.EmployeeID AS OT, C.EmployeeID AS CT
FROM Orders O INNER JOIN Orders_Copy C ON O.OrderID = C.OrderID
WHERE O.EmployeeID & "" <> C.EmployeeID & ""
UNION
SELECT O.OrderID, "OrderDate", O.OrderDate, C.OrderDate
FROM Orders O INNER JOIN Orders_Copy C ON O.OrderID = C.OrderID
WHERE O.OrderDate & "" <> C.OrderDate & ""
UNION
SELECT O.OrderID, "OrderStatusID", O.OrderStatusID, C.OrderStatusID
FROM Orders O INNER JOIN Orders_Copy C ON O.OrderID = C.OrderID
WHERE O.OrderStatusID & "" <> C.OrderStatusID & ""

You would add all the necessary fields. The output might look like:

​

 

[Pat Hartman]

You are not using the PK of the inventory items in this process. If you could, that would be easier. However, for this task you can use a multi-column join. PlantName, PotSize, Greenhouse to the corresponding columns on the database tables. You need three variations. An equi-join, a left join excluding, a right join excluding. Then a union of the three queries.

 

[gemma-the-husky]

If you do it the other way. ...

Find items with no difference, then everything else is a difference.

 

[Pat Hartman]

Find items with no difference, then everything else is a difference.

Technically, you can use Two queries in the join. A left join and a Right join. The left join will get the = and the left only and the right join will get the right only. I was describing the logic of a full outer join.