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 7378 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
-
ADezii 8,834
Recognized Expert Expert Try: - Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strCode As String
-
-
strCode = "YaDa"
-
-
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 AND " & _
-
"[ErrCode] = '" & strCode & "'") > 1 Then
-
.Delete
-
End If
-
End If
-
.MoveNext
-
Loop
-
End With
-
-
rst.Close
-
Set rst = Nothing
Hi ADezii,
Apprecaite your continued help with this.
I tried your revision but it did not work. I made a small adjustment where I took out the >1 and for some reason that worked...any idea why? -
Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strCode As String
-
-
strCode = "YaDa"
-
-
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 AND " & _
-
"[ErrCode] = '" & strCode & "'") Then
-
.Delete
-
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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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:...
|
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"...
|
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,...
| |
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,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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,...
|
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...
|
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...
| |
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |