Automically Delete Duplicates when Appending

[CodenameAter]

Hello,

I have many duplicate records where all the fields in the records are identical. On average I have about 2 to 4 duplicate records, but there are thousands of different duplicates. The create duplicate query wizard works quite well and pulls all the duplicates however, that query has hundreds of different duplicates.

I just want all the duplicate records to be deleted so that every record(row) is unique. Any one field can have duplicate values (ie. the same date, same ID tag, same time, etc.) however no record can have all the fields been identical to any other record.

As of now, I would have to manually delete hundreds of lines of data from the query. I have tried to follow a number of the tutorials online but they either ask for primary keys but all the fields have duplicate data (for example, the same date) or they don't delete the duplicates.

Let me know. Thank you.

 

[Micron]

The right approach is to not have the duplicates in the first place if you will continually have to delete them. Aside from that, here's what might work. Create a delete query and use your duplicates query as the data source. Back up your table before running the delete query in case it does not perform like you expect.

 

[CodenameAter]

The right approach is to not have the duplicates in the first place if you will continually have to delete them. Aside from that, here's what might work. Create a delete query and use your duplicates query as the data source. Back up your table before running the delete query in case it does not perform like you expect.

Yes, obviously not having duplicates is ideal but not a reality given the way the data gets collected.

I ran this query and then a delete query; it deleted everything.

 

[Micron]

I have tried to follow a number of the tutorials online but they either ask for primary keys but all the fields have duplicate data (for example, the same date) or they don't delete the duplicates.
Are you referring to this method: you create a copy of your table (structure only - no data); open it in design view and make one field a primary key, then run an append query on that table using your table with the duplicates as a source? If so, it matters not how many filelds in your source table are duplicated. Ensure that whatever field you choose for the primary has no missing data in any row. When you run the append, it will tell you that it cannot insert x rows due to the primary key. What you will get is every record once in the new table with the others being left out. If you use this table as the original, you would remove the primary key if it was not appropriate.
I stand by what I said regarding the gathering of data. If you will have this situation continually, something is wrong with the process that gets the data, or puts it in the table in the first place.

 

[CodenameAter]

Primary key usually requires one row to be unique. All rows have data and I want rows that are identical to each other to be deleted. Excel had a button in the ribbon that does this automatically, I hoped access would have something similar.

In regards to the duplicate data, I couldn't agree more and stand by it all you want but there is nothing I can do about it at this point. Next year when I setup the stations I will do it differently but given Excel as a built in function that does it automatically, I figured the effort to reprogram and redesign the stations backup would take longer than a push of the button. I just wanted to avoid having to use two different programs to tackle something as simple as removing duplicates.

 

[Micron]

Primary key usually requires one row to be unique.

Incorrect. Primary key only requires that all values in that field are unique for the whole table, or if a composite primary, the combination of the fields must be unique. It doesn't matter what is in the rest of the fields of a row. Every other field can be exactly the same for all rows as long as the foregoing is true. The only way your statement would be true is if the primary field was the only field in the table. So the technique should work as described.