Deleting record with smaller ID

[BarinaX]

Hello everybody,


I'm once again in need of your help.

I have a database, which is updated regularly from excel-import. Excel-importing adds new record to my table. I'm looking for a way to make the aftermath smoother by trying to create a macro, which would remove duplicate data the updating creates.

At the moment I'm using Access's built in find duplicates query with two fields, item name and ID. Of these two, Item name is the one that is duplicate. ID just tells me which ones to delete, as I always have to delete records which have smaller ID.

My question therefore is, how to create macro that would automatically delete those records from this duplicates query that have smaller ID number?

Thanks!

 

[Joe4]

You would use a Delete Query to do this.

If you need help creating that, please let us know the following information.
- The exact names of your table, item name, and ID fields
- Is the ID field a unique field?

 

[Micron]

Shouldn't your process do an update instead of just append? That way you would not have to delete duplicate records. I have done similar database updates by appending new records first (duplicates are prevented by primary keys) followed by an update query to update target table fields to updated fields from the source. Warnings are turned off during the process.

 

[BarinaX]

You would use a Delete Query to do this.

If you need help creating that, please let us know the following information.
- The exact names of your table, item name, and ID fields
- Is the ID field a unique field?

Yes Joe4, that would be great, as I don't yet have the knowledge in SQL that would enable me to do that.

Name of my table is "ItemList" and item name is just "Item". The ID list is unique field, made by access with autonumbering. That's how I also know which of the records is newer one.

I'm not certain if you need that, but the name of my duplicate looking query is just "Find duplicates for ItemList".



Thanks!

 

[Joe4]

How many other fields are there in there besides ID and Item?
When there are duplicate records, are there ever any differences in these other fields?
When there are duplicates, is there only exactly 2 records, or might there be more (3 or 4 of the same ItemList)?

I could come up with a nested query to Delete the duplicate records, but I am afraid it might be hard to understand/follow.
Micron's idea is probably a better option. I have done that one before. The question is, which other fields may change, and that needs to be incorporated into the Update Query.

 

[BarinaX]

There may be 20 other fields besides ID and Item also, but there may be (and will be) duplicate records among them.

But if there is going to be duplicates, there will be only exactly two of them, the old one and new one.


I forgot to mention that there is one more complication, as duplicate records are those which have two fields in common, "Item" and also "Supplier". So if there is two records which have both the Item and Supplier fields in common, the older one needs to be removed.


I also thought about the update query, but I don't know how its going to work with possibly 20 other fields which may need updating.

 

[Joe4]

OK, I would definitely recommend going the Append/Update query route.
Here is how I would do it.

1. Import each file into a pre-defined temporary table each time (really, just a copy of your ItemList table structure).
2. Do an Unmatched query between the Temporary Table and your ItemList table, based on your Item and Supplier fields.
3. Write all the results from the Unmatched query to your ItemList table using an Append Query.
4. Do a Matched query between the Temporary Table and your ItemList table, based on your Item and Supplier fields.
5. Use the Matched Query to create an Update Query to update all the fields in your ItemList table (don't worry about the ID field, there is no need to update this).
6. Delete all the records from your Temporary Table so it is empty and ready to use next time.

Note that steps 2-3 are really just one query. The same thing for steps 4-5 (one query).

 

[BarinaX]

Great, thanks!

It was a little hard to do as I'm not an Access-expert, but given enough time I was able to do it!


Thank you for all your help!