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

[Oreynolds]

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

Node	Loop	Address	Zone No	Device Text	Device Type	Device Info
1​	1​	1​	1​	Corridor 999 Exit	Call Point	6​
1​	1​	2​	1​	Corridor 999 Exit	Optical Smoke	23​
1​	1​	2​	1​	Corridor 999 Exit	Sounder	12​
1​	1​	3​	1​	Corridor 999 Adj Ramp	Optical Smoke	23​
1​	1​	4​	1​	Corridor 037 Adj Ramp	Sounder	12​

After duplicate check and value amendment:

Table9

Node	Loop	Address	Zone No	Device Text	Device Type	Device Info
1​	1​	1​	1​	Corridor 999 Exit	Call Point	6​
1​	1​	2​	1​	Corridor 999 Exit	Optical Smoke	23​
1​	1​	2.1​	1​	Corridor 999 Exit	Sounder	12​
1​	1​	3​	1​	Corridor 999 Adj Ramp	Optical Smoke	23​
1​	1​	4​	1​	Corridor 037 Adj Ramp	Sounder	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]

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]

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]

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]

CREATE A QUERY (Query11 on my sample).

    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

 

[Oreynolds]

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]

CREATE A QUERY (Query11 on my sample).

    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]

CREATE A QUERY (Query11 on my sample).

    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]

that is already taken for in the code.
you should try it to see.

 

[Cronk]

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.