wwstudioinc
asked on
remove duplicates acess 2000
here i go.I have this table that i want to check for duplicate entries,the name,tblled,the fields with the duplicate entries are,dsdate,time,seq,device no, when the duplicates are found i would like to remove one of the entries to a table (tbldups) to be verified before i actually delete.I would like to attached the code to a cmdbutton,or if possilble have this done at the first of every month before i actually run reports.
thanks
thanks
qry 1 (qry_tblled_dups)...
SELECT Max([your_id_field]) row_id
FROM tblled
GROUP BY dsdate, [time], seq, deviceno
HAVING Count([your_id_field]) > 1
qry 2 (qry_tblled_listdups)...
INSERT INTO tbldups
SELECT *
FROM tblled
INNER JOIN qry_tblled_dups
ON tblled.[your_id_field] = qry_tblled_dups.row_id
qry 3 (qry_tblled_deldups)
DELETE FROM tblled
WHERE [your_id_field] IN (SELECT row_id FROM tbldups)
qry 4 (qry_tbldups_truncate)
DELETE FROM tbldups
Process...
1. run qry_tbldups_truncate
2. run qry_tblled_listdups
3. look through data in tbldups using a form or whatever. Delete any rows you want to leave in tblled.
4. run qry_tblled_deldups
5. run qry_tbldups_truncate
Notes...
1. This will only work if you have a unique id field ([your_id_field]) in the table tblled, or a group of fields that together form a unique id. Without this you have no way of isolating a single row and you wont be able to use a query or any other automated process to get rid of your duplicates.
2. tbldups must have exactly the same structure as tblled, and should ideally have [your_id_field] indexed.
3. All this is air code so check it for typos, etc., don't be surprised if there's a bug (if the cause isn't obvious, post it up here and I'll take a look) and back up your database before trying this!
Hope this helps,
s46
SELECT Max([your_id_field]) row_id
FROM tblled
GROUP BY dsdate, [time], seq, deviceno
HAVING Count([your_id_field]) > 1
qry 2 (qry_tblled_listdups)...
INSERT INTO tbldups
SELECT *
FROM tblled
INNER JOIN qry_tblled_dups
ON tblled.[your_id_field] = qry_tblled_dups.row_id
qry 3 (qry_tblled_deldups)
DELETE FROM tblled
WHERE [your_id_field] IN (SELECT row_id FROM tbldups)
qry 4 (qry_tbldups_truncate)
DELETE FROM tbldups
Process...
1. run qry_tbldups_truncate
2. run qry_tblled_listdups
3. look through data in tbldups using a form or whatever. Delete any rows you want to leave in tblled.
4. run qry_tblled_deldups
5. run qry_tbldups_truncate
Notes...
1. This will only work if you have a unique id field ([your_id_field]) in the table tblled, or a group of fields that together form a unique id. Without this you have no way of isolating a single row and you wont be able to use a query or any other automated process to get rid of your duplicates.
2. tbldups must have exactly the same structure as tblled, and should ideally have [your_id_field] indexed.
3. All this is air code so check it for typos, etc., don't be surprised if there's a bug (if the cause isn't obvious, post it up here and I'll take a look) and back up your database before trying this!
Hope this helps,
s46
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you go to query, click on the new -> choose the "Find duplicates Query view" -> Choose the table or the query name you want to find the duplicate -> put in the field name which you want to check within those fields name -> save as a new query.
Step 2:
You link the original table/Query with the duplicate data, and check on the data
Step 3:
DoCmd.RunSQL "Delete From tableName where ... (condition)"