Append query to delete Duplicates

[jeffandtamara]

I have a series of quiers to 1st delete the table then append to the table. I have key field set up to delete duplicate Invoices and dates. What usually happens is the invocie is closed multiple times with no changes, so my process works OK. But sometimes what happens is changes are made and whenthe append occurs, I retain the latest data, and not the updated data. Is there a way to force the append qurey to get the latest data and delete the older data? The date will be the same on both raw data records....

 

[jackd]

I'm having difficulty with your post.

A DELETE query deletes records, not the table.
An APPEND query appends records to a table.

An UPDATE query modifies fields within records in a table.

You should BACKUP your database before doing any of the deletes/appends/updates.

Can you give specific examples with data of what exactly you are doing?

 

[Norie]

How can you delete a table then append records to a table you've just deleted?

Why do you even want to delete any of the data?

Surely with something like invoice data you would want to keep it?

Also, if data is going to change wouldn't it be an idea to keep the original data so you can refer back to it in the future?

 

[jeffandtamara]

The data comes in via text files that are generated when a transaction happens. The delete query runs when a report is generated, then the the text file is appended to the empty table. The key fields keep duplicate data from being appended into the database.
The problem is when there is duplicates, the first record is kept, not the latest. Is there a way I could sort the data so that the latest data gets appended first, then the problem would be solved, I think?

 

[SydneyGeek]

Here is a way to detect and delete duplicates.

Denis