Software
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
by jazzygodfrey Accepted solution
by jck Accepted solution
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
by jazzygodfrey Accepted solution
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
by jgarcia102066 Accepted solution
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.
by tony hopkinson Accepted solution
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.
by jazzygodfrey Accepted solution
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
by jgarcia102066 Accepted solution
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
This discussion is closed to new replies.
Sort by Trending