| | |
|
|
|
Comparing Data |
|---|
| message from Daniel Biancuzzo on 27 May 2004 |
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?
|
| =?Utf-8?B?VGFzaGE=?= replied to Daniel Biancuzzo on 27 May 2004 |
Collegue had same problem and this is how I solved it.
Each table needs field with classification something like n for new, o for obsolete, c for current product. First run an append query to move new products to original table. Then run update query where the two table are join based on the product number. The only field in the query would be the one you want updated. In the update to row put the [second tables name].[the field you want compared]. The SQL would look something like below.
UPDATE Table1 INNER JOIN Table2 ON Table1.part = Table2.Part SET Table1.class = [Table2].[class];
|
| hcj replied to Daniel Biancuzzo on 27 May 2004 |
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
Product and Pricing
products/prices from over 300
data. When I update
products and
following categories with
products that are
offered by certain
in order to
|
|
Archived message: Comparing Data (Microsoft Access)
