Question

Locked

how to delete duplicate rows in ms access

By jazzygodfrey ·
How can i delete duplicated rows (the row is fully duplicated) with out using cursors or temp tables?

Is it possible?

sample table
1 AAA 50
1 AAA 50
2 AAA 50
3 CCC 20

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

answer

by jck In reply to how to delete duplicate r ...

write a VBA tool in Access that reads your table, looks at each record, and compares it to all other records in the table.

order both sets the same way

if you look at another record and all fields are identical, delete the other record and do that to eof.

remember to refresh your data in your main query.

good luck

Collapse -

deleting duplicate records in access

by jazzygodfrey In reply to answer

thanx for your reply.
Im using vb.net i was able to create a query in access that returned a set of duplicate records, what im looking for is for a way to delete one of the duplicate and retain the other.
thanks

Collapse -

Process...

by jgarcia102066 In reply to deleting duplicate record ...

There is no way to do this using a simple query because the duplicate records are identical.

Follow this process to have unique records. It would be a good idea to do this when nobody is working in the database.

1. Create an empty copy of the table (no data).
2. Write a SQL query using DISTINCT to insert the data from the "DUPLICATED" table into your copy.
3. Verify the data in the copy is correct.
4. Delete the original table.
5. Rename your copy to the original table.

You might consider adding an AutoNumber field to your table so that at least that column will be unique if you ever need to delete duplicate records again.

Collapse -

select distinct

by jazzygodfrey In reply to Process...

thanx for your reply.
when i do select distinct it gets the unique records that i need which is fine but the problem is it returns only one column (the column that has duplicates) what i want is to display the remaining columns not only one column. how can i reconstruct my query?
thanx

Collapse -

Select Distinct

by jgarcia102066 In reply to select distinct

You mentioned that the entire record is a duplicate. In this case you just need to name all of the columns you wish to return.

SELECT DISTINCT Column1, Column2, etc.
FROM TABLE

Collapse -

Easy way

by Tony Hopkinson In reply to how to delete duplicate r ...

Create an empty copy of your table
Do an append query to fill it

insert into NewSample Select Distrinct row F1,F2 From Sample

for instance.

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums