Remove Duplicate value from Query (1 Viewer)

[zezo2021]

Hello

How can I remove duplicated rows from the query?

I need to keep one record only

Thanks for help

 

[silentwolf]

Hi in a query either you can use DISTINCT

https://www.w3schools.com/sql/sql_distinct.asp


or Group BY

Hope that helps

Albert

 

[zezo2021]

Thanks
but I want to remove delete
Not hide

 

[isladogs]

Deleting records is not usually recommended. Better to mark as archived or similar. But if you really must do so ....

The easiest way is to create a duplicate table but index whichever fields are required setting allow duplicates = false.
Then write an append query to copy all records to the new table. Only the first of each duplicate will be appended.
The original table can be deleted once you've checked this worked correctly.

Alternatively, create a find duplicates query and modify to only list the first of each record. Call that qryA
Now create an unmatched query on the original table and qryA to list all records not in qryA.
Change that to a delete query and run.

I sometimes add a boolean 'Tag' field to mark records listed for deletion

Whichever method you use, make sure you do check carefully before deleting records as once they're gone...they're gone forever.

 

[zezo2021]

Deleting records is not usually recommended. Better to mark as archived or similar. But if you really must do so ....

The easiest way is to create a duplicate table but index whichever fields are required setting allow duplicates = false.
Then write an append query to copy all records to the new table. Only the first of each duplicate will be appended.
The original table can be deleted once you've checked this worked correctly.

Alternatively, create a find duplicates query and modify to only list the first of each record. Call that qryA
Now create an unmatched query on the original table and qryA to list all records not in qryA.
Change that to a delete query and run.

I sometimes add a boolean 'Tag' field to mark records listed for deletion

Whichever method you use, make sure you do check carefully before deleting records as once they're gone...they're gone forever.

Done
duplicate = false