Deleting Duplicate Records in Access 2016

[EverettMiller]

Hey Forum!

In my project there is a table containing archival data. This table currently has more than 3 million records covering a period of 11 years. Each month our team scans equipment for inventory. Unfortunately, many times during the month the same piece of equipment is inventoried multiple times. What I am looking to do is to delete records in which the serial # is inventoried more than once in the same month and year.



Any help is greatly appreciated!

[zmbd]

Good Morning EverettMiller - Welcome To Bytes.

I cannot stress enough that you create a backup of your database AND a working_copy of your database before attempting anything suggested here...

Instead of deleting your records initially from such a huge data-table I propose that you use a "Make Table" query
I've attached a simple example where I take a group_by query where it finds the first entry (based on the smallest primary key value whereas you could possibly use the date/time field given that primary keys really shouldn't have any significance as related to the record data)
Two tables - one with the assets one with the pseudo-scan
five queries - only two qry_ReturnFirstOfEntries and MakeTable_qry_ReturnFirstOfEntries are of any concern.
The first is the logic I used to return the records of interest. I tend to make simple select queries first to make sure the returned data is correct.
The second is the actual action query that creates a table named tbl_CleanDataSet within the current database, you can of course have it create a new table in a new database - which is what I would most likely do... see below.
(oh, the other queries, I was just playing and thought you might find them relevant to something else later down the road)


Delete the tbl_CleanDataSet and rerun the maketable action query. Access will delete and remake the table automagically... but what's the fun in that ?

Why this approach,
Once you have tbl_CleanDataSet you have a chance to go back against the original data table to be sure that all of your records are there that you need to have from the original data table. Once sure, then rename the orginal to something like OLD_tbl_data, rename the [tbl_CleanDataSet]![MinDataPK] to [tbl_CleanDataSet]![data_pk] and finally rename tbl_CleanDataSet to tbl_data
Once you're all happy happy - delete the old record table - Did I mention - make sure you have a backup first ?!!
(this will all make sense when you open the Database 😊 )

Now what I might do is the following...
1) Create a secondary database that holds all of the raw scans
2) Run the maketable action query in the secondary database to create the clean data set
3) From the production database run an append action query to bring the cleaned data into the production records
4) Once the new records are appended to the production records delete the records in the secondary database holding table.
4a) why use a holding table? Access likes to bloat so you can compact and/or create a new secondary database as needed without putting the production records at risk.

Ideally you would have a business rule that prevents duplicate scans of the same asset within the same month of the same year... working on that... for my databases I usually use VBA to see if the item already has an entry; however, I'm not dealing with millions of records!
Perhaps you can start a new thread detailing how the business is "scanning" the assets - maybe some logic that can be enforced without a huge lagg in data-entry.

> Neopa or Rabbit will be along shortly - they might have a better solution... usually do 👾

[zmbd]

@Jimmywick
I am well aware of the find duplicates wizard and might have suggested it for a smaller data-set
The method I proposed is inherently safer in a production database in that it preserves the original dataset
1) Preservation of the original dataset is paramount when dealing with such a large amount of data just in case some needed record is in advertently deleted, or worse, the dataset is corrupted
2) Preservation of the original dataset may be required under certain federal and/or state laws, i.e. sarbanes-oxley act, certain FDA requirements, and/or ISO/ASTM or other accrediting bodies.
3) The find duplicates wizard is not usually very optimized for such large datasets

[jameswalter]

On the Design tab, click Run. Verify that the query returns the records that you want to delete. Click Design View and on the Design tab, click Delete. Access changes the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.

Regards,
Jimmy