Recurring entry deletion query

[Rowland77]

Hi all. I am trying to wade my way through a patchwork Access DB that I've inherited. I am attempting to take a database (DB1) that contains product info (both new products and updates to existing products) and import it into an existing table of products (DB2), all are organized by item number. As I have it constructed right now, I have:

1) A make-table query to take data from DB1
2) An append query to add data from DB2 > DB1
3) A find-duplicates query to filter only duplicate
entries by item number

My problem is that I do not know how to then run a delete query to get rid of the older of the entries when duplicates occur. There are many thousands of entries, and manual search/deletion is not possible. I apologize, I realize this is kids' play, but I was a hardware guy before this job, and I'm brutal with Access. Any help would be greatly appreciated.

 

[DanChard]

OK so once everything's DB1, how are your tables organised? I assume you must have some kind of date column regarding the date a particular item was released or whatever? Otherwise, how will you know which rows are old and which rows are new when you do find duplicates? Based on something like this you can then delete any duplicate items where the date field is less than the maximum date found for that item.

Sorry if I haven't explained this very well but I'm sure I could do a better job if you post the details of the layout of your DB...

Good luck!
Dan

 

[Rowland77]

Hi Dan, thanks for the reply. I wish it were so easy as having a date column, but I don't. Basically, we import this update database into our larger one, but neither has the date as a field. I was told to assign identifiers (which I assume could serve the same function as having a date) to each occurrence in my duplicates table, but of course I have no idea how this is achieved.

 

[tracy1234]

Well, if you're going to add an identifier then you might as well make it something like "Date Imported". If you don't have too many tables, you could add the column, then run an update query to set the initial value as today's date, then you could add today's date to future append queries.

This would give you a chance to identify which item iteration is old and which one is new.

-Tracy