Solved REMOVE DUPLICATES SAVING ONE VALUE

[georg0307]

Dear All,

from this simple access DB I need a way to remove duplicates, I mean the value must be unique.

So if a value is present several times, must be only one value, not deleting all of them.

Is it possible to create a button that performs this operation?

Thanks in advance.

 

[ebs17]

DELETE
FROM
   PICKSHIP AS P
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            PICKSHIP AS X
         WHERE
            X.COLLI = P.COLLI
            AND
            X.Data_ora < P.Data_ora
      )

You can certainly create a button yourself that leads to the execution of a query.

 

[CJ_London]

You talk about 'the value' but there are three values and combined, each row is unique
PICKSHIP

ID	COLLI	Data_ora	Data_giorno
1​	999999999	24/11/2022 13:52:05​	24/11/2022​
2​	5555555	24/11/2022 13:52:11​	24/11/2022​
3​	999999999	24/11/2022 13:52:16​	24/11/2022​
4​	111111111	24/11/2022 13:52:21​	24/11/2022​

Ignoring the ID column, I presume the issue is the COLLI column - and assuming the Data_ora column is just a timestamp, what if the Data_giorno value was different (e.g. for record 1 it was 22/11/2022)? Is that still considered a duplicate and you have no preference to which date should be retained?

 

[XPS35]

First thing we need to know is: what are duplicates?
Even If we leave out the ID there are no 2 records that are exactly the same (see the time differences in Data_ora).

An other point is: are there tables related to this table?
You can't remove one of the "duplicates" If the record is a "parent".

 

[gemma-the-husky]

You can have a totals query to find the duplicates, but the nature of that query means you can't distinguish between them.
What you could do is iterate a recordset sorted in order of the potential duplicate, and delete duplicates of each different record.

As others have said, there may be issues with deleting the duplicates, as they may not be true duplicates.
You also ought to add a unique index to prevent the duplicates arising in the first place.

anyway, this sort of pesudo code, for a recordset sorted by the potential duplicate field.

clear lastvalue
while not rst.eof
     if newvalue is different from lastvalue then
        store newvalue as lastvalue
    else
        delete record
    end if
    get  next record
wend

also interesting that @ebs17 got this in a single query. I always iterate a record set to do stuff like this.
With a record set, I know I can simulate it and test it, and make sure that it works correctly.

 

[ebs17]

In order to avoid new duplicates, you should of course put a unique index on the relevant field or field combination.

 

[arnelgp]

this will also delete duplicates and leave the first record:

DELETE *
FROM PICKSHIP
WHERE ID <>  (SELECT Min(ID) AS MinOfID FROM PICKSHIP AS Dupe     
   WHERE (Dupe.COLLI = PICKSHIP.COLLI));

 

[georg0307]

Thanks, to all, solved: