Comparing records between tables in 2000

My company is an electronic contract manufacturer. Our customers give us
parts lists and we build their product for them. I want to build a query
that will compare an updated parts list to the previous parts list finding
which records have changed. Some of the changes are little as one number or
letter. I need to compare three fields: reference designator, Mfr part
number and customer part number. The reference designator is the name of a
unique part on the circuit board, i.e. R1, R2, etc. It is also my point of
reference.

I have put together a couple queries to detect when a new reference
designator has been added or when an old one has been removed in the
transition to the updated list, but I'm not sure how to tell when either or
both of the part numbers for a particular reference designator have changed.
To complicate things a little further it is possible to have more than one of
a given reference designator, e.g. R1 may have multiple manufacturer part
numbers or even multiple customer part numbers. Here is a small sample:

Previous parts list
Ref Des MfrPN CustomerPN
R1 101A 10-25-111
R1 101B 10-25-111
R2 202x 10-24-222
R2 202x 10-24-232
R3 303 10-29-849

Updated parts list
Ref Des MfrPN CustomerPN
R1 101A 10-25-111
R1 102B 10-25-111
R2 202x 10-24-222
R2 202x 10-24-232
R3 303A 10-29-849

How can I build a query that will find the changes in R1 & R3?

--
Thank you,
Del

On Fri, 17 Oct 2008 13:11:01 -0700, Del wrote:

My company is an electronic contract manufacturer. Our customers give us
parts lists and we build their product for them. I want to build a query
that will compare an updated parts list to the previous parts list finding
which records have changed. Some of the changes are little as one number or
letter. I need to compare three fields: reference designator, Mfr part
number and customer part number. The reference designator is the name of a
unique part on the circuit board, i.e. R1, R2, etc. It is also my point of
reference.

I have put together a couple queries to detect when a new reference
designator has been added or when an old one has been removed in the
transition to the updated list, but I'm not sure how to tell when either or
both of the part numbers for a particular reference designator have changed.
To complicate things a little further it is possible to have more than one of
a given reference designator, e.g. R1 may have multiple manufacturer part
numbers or even multiple customer part numbers. Here is a small sample:

Previous parts list
Ref Des MfrPN CustomerPN
R1 101A 10-25-111
R1 101B 10-25-111
R2 202x 10-24-222
R2 202x 10-24-232
R3 303 10-29-849

Updated parts list
Ref Des MfrPN CustomerPN
R1 101A 10-25-111
R1 102B 10-25-111
R2 202x 10-24-222
R2 202x 10-24-232
R3 303A 10-29-849

How can I build a query that will find the changes in R1 & R3?


Status RefDes MfrPN CustomerPN
Previous R1 101B 10-25-111
Updated R1 102B 10-25-111
Previous R3 303 10-29-849
Updated R3 303A 10-29-849

SELECT "Previous" AS Status,
PreviousList.RefDes,
PreviousList.MfrPN,
PreviousList.CustomerPN
FROM PreviousList
LEFT JOIN UpdatedList
ON (PreviousList.CustomerPN = UpdatedList.CustomerPN)
AND (PreviousList.MfrPN = UpdatedList.MfrPN)
AND (PreviousList.RefDes = UpdatedList.RefDes)
WHERE (((UpdatedList.RefDes) IS NULL)
AND ((UpdatedList.MfrPN) IS NULL)
AND ((UpdatedList.CustomerPN) IS NULL))
UNION
SELECT "Updated",
UpdatedList.RefDes,
UpdatedList.MfrPN,
UpdatedList.CustomerPN
FROM UpdatedList
LEFT JOIN PreviousList
ON (UpdatedList.RefDes = PreviousList.RefDes)
AND (UpdatedList.MfrPN = PreviousList.MfrPN)
AND (UpdatedList.CustomerPN = PreviousList.CustomerPN)
WHERE (((PreviousList.RefDes) IS NULL)
AND ((PreviousList.MfrPN) IS NULL)
AND ((PreviousList.CustomerPN) IS NULL))
ORDER BY RefDes;

It is just two queries joined with Union.