Find duplicate records and alter the second? (1 Viewer)

Oreynolds

Member
Local time
Today, 00:12
Joined
Apr 11, 2020
Messages
110
Hi everyone,

I regularly import data into my DB and when it comes in there are always odd pairs of duplicate records. The import process is automated using SQL statements in VBA. At the moment I discard these duplicate records using indexes.

However I would now like to accept them which I have done, run a duplicates check which I can do with a duplicates query and then alter the second record of every duplicate to make it unique. The check for duplicates is done across three fields (NODE/LOOP/ADDRESS) and all I need to do is to add 0.1 to the third field (ADDRESS) on the second of each duplicate record to make it unique. Example below:

On IMPORT:

Table9

NodeLoopAddressZone NoDevice TextDevice TypeDevice Info
1​
1​
1​
1​
Corridor 999 ExitCall Point
6​
1​
1​
2​
1​
Corridor 999 ExitOptical Smoke
23​
1​
1​
2​
1​
Corridor 999 ExitSounder
12​
1​
1​
3​
1​
Corridor 999 Adj RampOptical Smoke
23​
1​
1​
4​
1​
Corridor 037 Adj RampSounder
12​
After duplicate check and value amendment:

Table9

NodeLoopAddressZone NoDevice TextDevice TypeDevice Info
1​
1​
1​
1​
Corridor 999 ExitCall Point
6​
1​
1​
2​
1​
Corridor 999 ExitOptical Smoke
23​
1​
1​
2.1​
1​
Corridor 999 ExitSounder
12​
1​
1​
3​
1​
Corridor 999 Adj RampOptical Smoke
23​
1​
1​
4​
1​
Corridor 037 Adj RampSounder
12​
Does anyone know a simple way in VBA to achieve this? I am familiar with update queries but not sure how to force it to only ever amend the second record of each duplicate as above?

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:12
Joined
Sep 21, 2011
Messages
7,900
Only ever one duplicate?

I'd approach it in steps
One query to get how many of each where duplicates (count > 1)
The read that as a recordset, use the data to get a second recordset of your data for that combination.
Then .MoveFirst, then .MoveNext, then amend the rest of the records with a counter?

If only ever one duplicate, then probably an easier way, but with my method, you could still .MoveLast and then amend.

HTH
 

Oreynolds

Member
Local time
Today, 00:12
Joined
Apr 11, 2020
Messages
110
Hi Gasman, thanks for your reply. No there are potentially any number of duplicate records but they only are ever in pairs.

I have a duplicate query, could I create this as a recordset, move last, move first, then move next, update, move next, move next, then amend, etc etc, I.e to amend every other record with 2 move nexts inbetween each amanedment?

Seems a bit clunky but maybe would work? Any other thoughts?
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:12
Joined
Sep 21, 2011
Messages
7,900
When I say more than one, I meant more than one duplicate of the same data.? Now you are saying they are in pairs.?
 

arnelgp

error reading drive A:
Local time
Today, 07:12
Joined
May 7, 2009
Messages
12,050
CREATE A QUERY (Query11 on my sample).
Code:
    Dim db As DAO.Database
    Dim sngAddress As Single
    Dim sSQL As String
    Set db = CurrentDb
    sSQL = "SELECT * FROM Query11;"
    With db.OpenRecordset(sSQL, dbOpenDynaset)
        If Not (.BOF And .EOF) Then
            .MoveFirst
            sngAddress = ![Address].Value + 0.1
            .MoveNext
        End If
        While Not .EOF
            If CInt(sngAddress) <> CInt(![Address]) Then
                sngAddress = ![Address].Value + 0.1
            Else
                .Edit
                ![Address].Value = sngAddress
                .Update
                sngAddress = sngAddress + 0.1
            End If
            .MoveNext
        Wend
    End With
SHOT.png
 

Oreynolds

Member
Local time
Today, 00:12
Joined
Apr 11, 2020
Messages
110
When I say more than one, I meant more than one duplicate of the same data.? Now you are saying they are in pairs.?
Yes, only ever one duplicate of a record, but multiple records duplicated. So multiple pairs of duplicated records
 

Oreynolds

Member
Local time
Today, 00:12
Joined
Apr 11, 2020
Messages
110
CREATE A QUERY (Query11 on my sample).
Code:
    Dim db As DAO.Database
    Dim sngAddress As Single
    Dim sSQL As String
    Set db = CurrentDb
    sSQL = "SELECT * FROM Query11;"
    With db.OpenRecordset(sSQL, dbOpenDynaset)
        If Not (.BOF And .EOF) Then
            .MoveFirst
            sngAddress = ![Address].Value + 0.1
            .MoveNext
        End If
        While Not .EOF
            If CInt(sngAddress) <> CInt(![Address]) Then
                sngAddress = ![Address].Value + 0.1
            Else
                .Edit
                ![Address].Value = sngAddress
                .Update
                sngAddress = sngAddress + 0.1
            End If
            .MoveNext
        Wend
    End With
View attachment 82244

Thanks I’ll take a look at this option
 

Oreynolds

Member
Local time
Today, 00:12
Joined
Apr 11, 2020
Messages
110
CREATE A QUERY (Query11 on my sample).
Code:
    Dim db As DAO.Database
    Dim sngAddress As Single
    Dim sSQL As String
    Set db = CurrentDb
    sSQL = "SELECT * FROM Query11;"
    With db.OpenRecordset(sSQL, dbOpenDynaset)
        If Not (.BOF And .EOF) Then
            .MoveFirst
            sngAddress = ![Address].Value + 0.1
            .MoveNext
        End If
        While Not .EOF
            If CInt(sngAddress) <> CInt(![Address]) Then
                sngAddress = ![Address].Value + 0.1
            Else
                .Edit
                ![Address].Value = sngAddress
                .Update
                sngAddress = sngAddress + 0.1
            End If
            .MoveNext
        Wend
    End With
View attachment 82244

Hi, thankyou so much for this, this worked perfectly first time with no issues, very much appreciated.

Out of curiosity currently my data only has pairs of duplicates, however if there were to be a situation where there were more, say 3 or 4 duplicates for a record would there much to change with the code to name them as follows? Your code is a bit advanced for me to get my head round otherwise I'd try and change myself!

1st = 1
2nd = 1.1
3rd = 1.2
4th = 1.3
 

arnelgp

error reading drive A:
Local time
Today, 07:12
Joined
May 7, 2009
Messages
12,050
that is already taken for in the code.
you should try it to see.
 

Cronk

Registered User.
Local time
Today, 09:12
Joined
Jul 4, 2013
Messages
2,488
A better approach is not to create duplicates in the first place. Once you have sorted out existing duplicates, set up the import process to import the data into a temporary table. Then you check if any record in the temporary table is a duplicate of an existing record and change the data in the temp table before importing it.
 

Users who are viewing this thread

Top Bottom