Comparing Data

[Daniel Biancuzzo]

I am maintaining a large amount of data with respect to Product and Pricing
information, and as such we continually update products/prices from over 300
separate vendors.

The problem I am having is with respect to updating the data. When I update
the data I receive a master list of over 100,000 updated products and
prices. Those products and prices consist of the following categories with
respect to the old data; products that are still carried, products that are
discontinued from certain vendors, and new products offered by certain
vendors. What is the best way to compare the two tables in order to
determine what category each item falls into?

 

[hcj]

The basic strategy is going to be using joined tables in a
query, but it would help to have a bit more information.

1. Is your "old data" list a composite of all vendor
lists, or do you maintain separate lists for each vendor?
2. Do the product records contain some unique field (e.g.
part number) or unique combination of fields (e.g. vendor
ID+part number) that is the same in BOTH the old data AND
the vendor update lists (in other words, can you uniquely
match an old record with a new one)?
3. Do discontinued products actually drop off the new
vendor lists or are they still listed but with a flag that
says "discontinued" or some such thing?

Would like to know more and try to help if I can. It may
take a couple of passes.
hcj