Find variation between data in 2 tables

[ponneri]

Hi All.

I have a materials table with MatID (alphanumeric text), MatDesc (text) and MatQty (number) - approx 1200 recs. And I received another table from a branch office with same structure but nearly 1750 recs.

Now, I need to compare both using a query. But the result has to be as follows.

A) What are the MatID's that are same in both tables, but have a count that is more than that of old table. (ie. multiple new records of same MatID have been added in new table)

B) What are the MatID's that are totally "new" in the New table with their count.

How can I do this ?

I think joins will work, but the exact syntax....

Can someone please help ?

 

[CJ_London]

something like

A) SELECT *
FROM (SELECT matID, count(*) as Num FROM newTable GROUP BY matID) N INNER JOIN oldTable O ON O.matID=N.matID

B) SELECT N.*
FROM (SELECT matID, count(*) as Num FROM newTable GROUP BY matID) N LEFT JOIN oldTable O
WHERE O.matID is null

 

[The_Doc_Man]

You will need at least two queries for this, and depending on how you want to approach it, maybe a third query.

For this discussion, call your table "MINE" and call the other table "BRANCH" (or something like that). I don't know or care what the actual names are.

Build two JOIN queries, one LEFT JOIN and one RIGHT JOIN "on MINE.MatID = BRANCH.MatID", then include the suspected fields in the SELECT portion, where you have, say, ... Nz(MINE.MatQty,"?") AS MyMatQty, Nz(BRANCH.MatQty,"?") as BRMatQty ... etc. for each field you want to compare. For snorts and giggles, you can add a third field as ... CStr(MyMatQty) = CStr(BRMatQty) ..., which will give you a checkbox that is checked if the two fields match.

You have to do one LEFT JOIN and one RIGHT join because you say that there can be cases where one of the tables contains records not found in the other table, but Access won't let you "free-form" that query. One table has to be the "anchor" that is the basis for the comparison; the other table entry for that MatID might be not there, thus resulting in a NULL for the query result (hence my use of NZ function). So you do it for one table to see what records the branch table DOESN'T have that you DO, and then do it for the other table to see what records you DON'T have that the branch table DOES have.

For the case where the records exist in BOTH tables, either of these queries would give you the comparisons since there would be records for the same MatID in both sources.

A third query could be written if you wanted the comparison of the common records side by side, in which case you would do an INNER JOIN between the two tables. If something is in both tables, then you can omit the NZ functions here - but not for the LEFT JOIN and RIGHT JOIN, where a mismatched MatID would lead to NULLs from the table that doesn't have a matching MatID.