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!
3 27053 zmbd 5,501
Expert Mod 4TB
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 5,501
Expert Mod 4TB
@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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ross |
last post by:
i have less hair now than i used to...i have a database with many
duplicate records in it with only one exception, there is one field
with the date it was put in the database and that is different....
|
by: Emmett Power |
last post by:
Hi,
I have an Access table with a number of records which refer to the
same person but with data in different fields. So for example the
table would look like this:
Name..............Field...
|
by: skennd |
last post by:
Here's my problem in exact replication:
I have used the find duplicate query in Access, and the query
determined the following duplicate values by the following query:
In (SELECT FROM As...
|
by: vinod |
last post by:
dear friends,
suppose i've one table, it has only to rows.the two rows are are same as it is.how can i delete one row from that table?
thank you verymuch
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night)...
|
by: pbrown |
last post by:
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this:
Name, Location,...
|
by: matrekz42 |
last post by:
Hello Guru's
(DELETE QUERY)
I have a table that has an account number field and a dollar amount associated with that field. I want to delete one of the fields only, if the dollar amount is the...
|
by: Dilip1983 |
last post by:
Hi All,
I want to delete duplicate records from a large table.
There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state.
First of all when i tried to rebuild...
|
by: watertraveller |
last post by:
Hi all. My ultimate goal is to return two columns, where no single value appears anywhere twice. This means that not only do I want to check that nothing from column A appears in column B and...
|
by: madhavateja |
last post by:
i want remove duplicate records from the base table with out using rowid concept.i have one solution this is..
first selecting unique records from the table.
now creating a new table with this...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |