Separating out new data from two tables

[Jonti]

Hi all!

I have a quick problem. I am using Access 97

Every week by e-mail from a supplier one I get an excel file with 7000 or so data entries (special offers).

I import this into Access and then merge it with Word to produce a printed catalogue. A lot of this data duplicates week from week and the manual job of sorting through and deleting out previous entries all 7000 entries is getting too much (and is not reliable - I'm only human after all)!!

Is there a way (probably with a query) that I could automatically compare the latest table with the previous week' s table and produce a new table that only contains the changes (i.e. only the new data that has been added since the previous week).

This would make my job so much easier!!!

A primary key (?) could be the bar code or unique product reference.

Any advice would be greatly appreciated.

Cheers!

Jonti

 

[stallyon]

if the unique product reference (UPR) is indeed unique then you can link the source and destination table together via the UPR (select the UPR on the source table and drag onto the UPR on the destination table), double click on the line that links the 2 fields and select option 2 to show all fields from the source table and only those from the destination table that match, then add the UPR from the destination table and add Is Null in the criteria for that field.

This will then show you a list of all the records from the source table that can not be found in the destination table - this will give you the new records to add.

 

[Jonti]

Thank you for that...

...but being non technical could I possibly ask for a step by step 'dummies' guide as to how I should do this?

I start with importing the excel file as a table in access 97 for the first week and then do the same for the second weeks excel file.

So I now have table 1 & 2 - how do I then extract the unique data into a new table?

Any advice would be much appreciated

 

[The_Doc_Man]

OK, perspective time...

You MUST lay this out carefully before you really start it or you will think I have taken you down a garden path somewhere.

In order to support your needs, you need some type of unique identifier that would be invariant from week to week, such as stallyon's unique product code. Then if the spreadsheet contains UPCs that are guaranteed to be unique, you can just append to a table for which the UPC is a key field marked "No Duplicates." What this will do is...

Import the spreadsheet to an intermediate table that you will erase before each new dataset comes in. Write a delete query to remove records that are headers or are otherwise not to be treated as offered items. What is left is the catalog for the week. Write an append query to add these items to the master item table.

The first week, unless there are duplicates in the spreadsheet, everything will be imported. The second week, if there are duplicates and you attempt to append them, the append query will give you a warning that due to key violations, some number of records cannot be imported. It will also ask "Do you want to continue" and you will get a Yes/No dialog box. Select YES (I.e. damn the errors, full speed ahead.) The warning is just Access dynamically removing the duplicates. What is left, if the key was set up correctly, is the new offers only, appended to the older part of the catalog.

You didn't mention offer retirement or offer expiration. I'm not going to try to guess how you want to do it, that is a business rule sort of thing and any guess I take is likely to be off. Just remember, if you want to retire an offer (take it out of your current catalog), you need a way to know it has expired. But having a unique (No Dups) key makes that easier.