Remove duplicate values in a field, keep the record. 1

[ponderdj]

Hey everyone. Complicated reason why we're wanting this, so I won't go there... I leave it at there are Hardware servers with some VMs on them, each therefore having the same Serial Number.

Basically, I have a field (Serial Number) that contains some duplicate values. I want to delete the duplicate values but keep all records, leaving the value intact only for the first record it occurs in.

I'm planning to automate this in VBA so I can do it in a series of SQL operations, use temporary tables whatever. Any ideas on the most efficient way?

I'm thinking of selecting the Primary Key (Hardware ID) along with the serial number into a new table with primary key set on Serial Number to eliminate duplicate records. Then run another query to delete Serial Number from the original table if it's Hardware ID doesn't exist in the second table.

Sound right? Can anyone think of a better way?

Thanks,
David

 

[dhookom]

If this is a one time operation, I would do basically what you have described by appending records to a temporary table using a totals query that groups by Serial Number and include the min or max of Hardware ID. Note: "only for the first record it occurs in" has no meaning in Access tables since records are like marbles in a box. There is no first or last.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

leaving the value intact only for the first record
Which value for the other rows ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ponderdj]

PHV, I'd keep a null value in [Serial Number] and leave the rest of the record as is.

dhookum, I ended up doing this, but without using a min. tblHardware is sorted ascending on incrementing HardwareID, so the record with the lowest HardwareID value wins and gets to keep the [Serial Number].

This look right?

        '1.3 Run insert into into tblHardwareNoDups to set columns
        Set qd = db.CreateQueryDef("", "SELECT * INTO tblHardwareNoDups" _
                                    & " from tblHardware;")
        qd.Execute
        
        '1.4 Now that its columns are set, clear tblHardwareNoDups, set primary key, select non-nulls into it
        Set qd = db.CreateQueryDef("", "DELETE * FROM tblHardwareNoDups;")
        qd.Execute

        Set qd = db.CreateQueryDef("", "ALTER TABLE tblHardwareNoDups ADD PRIMARY KEY([Serial Number]) ;")
        qd.Execute

        Set qd = db.CreateQueryDef("", "INSERT INTO tblHardwareNoDups " _
                                     & "SELECT * from tblHardware WHERE tblHardware.[Serial Number] IS NOT NULL " _
                                     & "AND tblHardware.[Serial Number] NOT LIKE """";")
        qd.Execute
        
        '1.5 Delete from tblHardware
        Set qd = db.CreateQueryDef("", "UPDATE tblHardware SET tblHardware.[Serial Number] = Null " _
                                       & "WHERE tblHardware.HardwareID Not In" _
                                       & "(SELECT HardwareID FROM tblHardwareNoDups);")
        qd.Execute
        
        '1.6 Delete tblHardwareNoDups
        Set db = CurrentDb 'This line refreshes the list of DB objects, preventing error
        db.TableDefs.Delete "tblHardwareNoDups"

 

[dhookom]

It looks like you are simply deleting the value from the serial number field to remove duplicates without changing anything else in the original table. No records are deleted.

I think you could use:

UPDATE tblHardware SET tblHardware.[Serial Number] = Null
WHERE tblHardware.HardwareID Not In (SELECT TOP 1 HardwareID FROM tblHardware H WHERE H.[Serial Number] = [tblHardware].[Serial Number]);

Duane
Hook'D on Access
MS Access MVP

 

[ponderdj]

dhookum,

Yes, that's exactly what I was trying to do.

Your way is much more succinct. I've implemented and tested it and it looks good. Thank you!