Compare 2 Tables 1

[RJL1]

I read most of the threads regarding comparing 2 tables but I cannot find any solution to my situation. I have 2 tables for an inventory application tblACount and tblBCount. Both tables have the same fields.

fldID – Record ID (autonumber & primary key)
fldUPC – Part number
fldBatch – batch number
fldLocation – Location where the product was counted
fldQty – Quantity counted
fldTeam – The team that counted the stock
fldSheet – the sheet number the team used to count

We counted the stock twice an A and a B count by different teams per section. My oroblem lies when trying to compare the counts from A to the counts from B.

Example:

Product 20944
A Count B Count
Location – Qty Location – Qty
S5C1 – 27 S5C1 – 27
1732B – 80 1732B – 80
1738C – 1008

I would like to get a query that would return the information like it is above. What I get when I do a query with both tables is

A Count B Count
Location – Qty Location – Qty
S5C1 – 27 S5C1 – 27
S5C1 – 27 1732B - 80
1732B – 80 S5C1 - 27
1732B – 80 1732B – 80
1738C – 1008 S5C1 – 27
1738C – 1008 1732B - 80

I’m afraid that my original design of the tables might be the cause but I’m not sure. Is there any way that I can get all of the location for an item from A and all of the location from B without duplicates.

Thanks
RL

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Select A.fldLocation
     , SUM(A.fldQty) As [A Qty]
     , SUM(B.fldQty) As [B Qty]

From ACount As A INNER JOIN BCount As B
     ON A.fldLocation = B.fldLocation

Group By A.fldLocation

HAVING SUM(A.fldQty) <> SUM(B.fldQty)

Will show you the Locations & Counts where the two teams got different counts. Remove the "HAVING" clause to see the counts for ALL locations.

 

[RJL1]

Thanks that works great I will play with this from here and see if I can fix the errors from my original design.

a star for you

Thanks
RL

 

[Golom]

From the perspective of this issue at least, I don't see that your original design had errors. You captured basic data ... not derived quantities and you didn't create repeating fields.

If I were to frown on anything in the basic design it would be that

- You have used an autonumber as the primary key. That sort of defeats the purpose of ensuring that only unique records (apart from the autonumber) can be entered. With this scheme you could enter two records that were identical in all the other fields but the DBMS wouldn't catch that because it generated a unique key for the record (i.e. an autonumber.)

- You chose to create two tables with identical structures when you already have a "fldTeam" field that would allow you to keep them all in the same table.

 

[RJL1]

Thanks for the pointers. That is the kind of errors I was thinking I had made. Once we had captured the data and I had two tables I realized that I should have captured the data in one table and not two. For our next inventory I have made some revisions and will make the cards (sheets) the counters use one of the unique field so there will not be duplicate cards (sheets) entered. I have also built some more data validation into it so I think with the help of everyone that has responded it will turn out to be a better application

Thanks
RL