how to replace duplicate records with the most recent version

[Loki13013]

I'm a newbie to Access, have a background in SQL programming (two college classes). But I'm really trying to avoid writing code, quickly coming up to speed on the drag-and-drop environment of Access.

Here's my quest:

when adding records to a table, if a record with the same primary key already exists in the table, overwrite/replace it with the new (more recent) record. if the record has a unique primary key, append it to the table.

I have a client who gets reports on web sales for video games on a daily basis. The "transaction number" is unique and is my primary key. Each day I load the new records into a table by appending the new records to the end of the table. However, sometimes the records have to be corrected on a given day, and I need an automated way to update the table by replacing these records with their updated counterparts.

I suspect the answer is in the mass of websites and help info I've been reading for the past three hours, but I can't pull it out of the haze for the life of me. Any assistance would be greatly appreciated.

- Brian

 

[BNPMike]

Run two queries. The first you join your new data to your existing table and update all the fields. This query will only deal with the records that have keys matching existing data. Then run an append query that adds all the new data to the existing database. This will only add the non-matching records as all the matching records (which you have updted at the previous step) will be rejected as duplicates.

The key to this is to first import your daily data into a daily import table.