Hello,
I am trying to write a code that will look through a table for duplicates and delete them. I currently have a code I found on an older post in bytes that works fine for deleting duplicates but I am trying to figure out a way i can modify it to delete duplicates based on a criteria.
I have been trying to change it so that if I have strField(1) that is Not Null and another strField(2) that Is Null and strField(1) is a duplicate then delete it.
Any help would be appreciated.
Thanks,
Slen
below is the code i am working with (orginally posted by NeoPa) -
'DelDups Removes duplicate records in strTable matched on strField
-
Public Sub DelDups(strTable As String, strField As String)
-
Dim strSQL As String, varLastVal As Variant
-
-
'Recordset must be full table but sorted by the field we're checking
-
strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "]"
-
With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
varLastVal = Null
-
'For each record, check against previous value in strField
-
'If same then this is a duplicate so can be removed
-
Do Until .EOF
-
If .Fields(strField) = varLastVal Then
-
Call .Delete
-
Else
-
varLastVal = .Fields(strField)
-
End If
-
Call .MoveNext
-
Loop
-
'Ending the 'With' releases the Recordset
-
End With
-
End Sub
-
-
11 7410 Rabbit 12,516
Recognized Expert Moderator MVP
Why do it through VBA instead of a delete query?
Mostly because I have code built that is doing a few other things to the table before and after finding the duplicates.
I was trying to avoid the whole dup/append queries and then pull that information back in to the main table.
slen :D
Rabbit 12,516
Recognized Expert Moderator MVP
Well, then you pretty much translate what you said into VBA code - If Not IsNull(.Fields(strField1) And IsNull(.Fields(strField2) Then
Thanks Rabbit,
That was what I needed!
I am still finding that it is not quite deleting all the duplicates which I think its because there might be some that could be triplicates.
I will continue to play with it and see if i can figure it out. I apprecaite the help in getting me in the right direction.
Thanks again,
Slen :D
ADezii 8,834
Recognized Expert Expert
The following Demo, hard coded for the sake of simplicity, will Delete any Duplicates, Triplicates, etc. where Duplication is defined as [Field1] NOT being NULL and [Field2] Is NULL. I also posted sample Before and after Data. - Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset("Table1", dbOpenDynaset)
-
-
With rst
-
Do While Not .EOF
-
If Not IsNull(![Field1]) And IsNull(![Field2]) Then 'Check for Duplication
-
If DCount("*", "Table1", "[Field1] = '" & ![Field1] & "' AND [Field2] Is Null") > 1 Then
-
.Delete
-
End If
-
End If
-
.MoveNext
-
Loop
-
End With
-
-
rst.Close
-
Set rst = Nothing
Before Executing Code: - PK Field1 Field2
-
1 A 1
-
2 B
-
3 C
-
4 D 4
-
5 E 5
-
6 f 6
-
7 G 7
-
8 H 8
-
9 B
-
10 C
-
11 E
-
12 f
-
13 G
-
14 t 44
-
15 M
-
16 B
-
17 B
-
18 C
-
19 D
-
20 p
After Code Execution: - PK Field1 Field2
-
1 A 1
-
4 D 4
-
5 E 5
-
6 f 6
-
7 G 7
-
8 H 8
-
11 E
-
12 f
-
13 G
-
14 t 44
-
15 M
-
17 B
-
18 C
-
19 D
-
20 p
P.S. - Records with Primary Key Values of 2, 3, 9, 10, and 16 have been Deleted.
Hi ADezii,
Once again you skills are the stuff of legend. That worked a lot better. Now I just have to figure out how i can change it some to go in to a function :D
Really appreciate your help!
Is there anyway i can change your response to the correct answer?
Slen
An add on question for you,
How would i modify this to check for duplicates and then if there is one value in the duplicate that is different to delete it?
I tried something like this but i keep getting errors... -
Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset("tbl", dbOpenDynaset)
-
-
With rst
-
Do While Not .EOF
-
If Not IsNull(![strField1]) And IsNull(![strField2]) Then 'Check for Duplication
-
If DCount("*", "tbl", "[strField1] = '" & ![strField1] & "' AND [strField2] Is Null") > 1 And _
-
DLookup("*", "tbl", "[Code1]= 'No Error'") Then
-
.Delete
-
End If
-
'End If
-
End If
-
.MoveNext
-
Loop
-
End With
-
-
rst.Close
-
Set rst = Nothing
-
ADezii 8,834
Recognized Expert Expert
if there is one value in the duplicate that is different to delete it?
Kindly explain, not sure what you mean...
Going off of the code you did if I wanted to make adjustments to it so if I have a duplicate record the identifier would be strField1 (an ID #), in strField2 one record is Null and the other is Not Null and in a strField3 I could have two different values, for example code1 and code2. If I wanted to delete only the record where strField1 is a duplicate, strField2 is Null and strField3 = code2, how could I adjust this??
I can get this to run but it is not deleting anything...any ideas? -
With rst
-
Do While Not .EOF
-
If Not IsNull(![ID]) And IsNull(![Amt]) Then 'Check for Duplication
-
If DCount("*", "tblName", "[ID] = '" & ![ID] & "' >1 And [Amt] Is Null And [ErrCode] = '" & strText & "'") Then
-
.Delete
-
End If
-
'End If
-
End If
-
.MoveNext
-
Loop
-
End With
-
-
rst.Close
-
Set rst = Nothing
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: christof hoeke |
last post by:
hello,
this must have come up before, so i am already sorry for asking but a
quick googling did not give me any answer.
i have a list from which i want a simpler list without the duplicates
an easy but somehow contrived solution would be
>>> a =
>>> d = {}.fromkeys(a)
>>> b = d.keys()
|
by: Alexander Anderson |
last post by:
I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:
+-----------+
| a | b |
+-----+-----+
| 123 | 234 |
| 345 | 456 |
|
by: GlobalBruce |
last post by:
The GAC on my development computer has several assemblies which are
duplicated. For instance, the System assembly is present as two different
native images as well as the non-native version. The native images differ by
date/time stamp -- although they are only different by 3 days during Feb/2004.
Many of the System assemblies have similar...
|
by: EoRaptor013 |
last post by:
I'm having trouble figuring out how to delete some _almost_ duplicate
records in a look-up table. Here's the table:
CREATE TABLE (
(16) NOT NULL ,
(2) NOT NULL ,
(20) NULL ,
(50) NULL ,
NULL ,
CONSTRAINT PRIMARY KEY CLUSTERED
(
|
by: Smythe32 |
last post by:
If anyone could help, I would appreciate it.
I have a table as listed below.
I need to check for duplicates by the OrderItem field and if there are
duplicates, it then needs to keep the OrderItem with the highest
interval and discard all other duplicates for that OrderItem number.
If the OrderItem and the Interval are exactly the same, it...
| |
by: Mokita |
last post by:
Hello,
I am working with Taverna to build a workflow. Taverna has a beanshell where I can program in java. I am having some problems in writing a script, where I want to eliminate the duplicates in an array (String).
for (int k=0; k<myLength; k++){
boolean isthere=false;
for (int l=0; l<sizeres; l++){
if (res.equals(my)){
|
by: allingame |
last post by:
Need help with append and delete duplicates
I have tables namely
1)emp, 2)time and 3)payroll
TABLE emp
ssn text U]PK
name text
|
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 vice-versa, but I also don't want the same value appearing twice in A and twice in B.
So far I have:
--Diff the columns
INSERT INTO @Table
SELECT...
|
by: moon24 |
last post by:
Hi im working with linked list and i have to implement a function that deletes the duplicates of a number. for example if given 2 7 1 7 12 7
then the result should be 2 7 1 12
here is what I have:
#include <iostream>
using namespace std;
class NumberList
{
|
by: KimmyG |
last post by:
I'm just starting to use SQL and am much more experienced in Access.
Here is what I do in Access
Copy a table and rename the new table "copytable" also select structure only.
Open "copytable" and highlight columns where I want to delete duplicates if
there are more than one.
Put a primary key on Outreach name and days.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |