How to delete duplicate row from table?

[sonikudi]

Hey all!

If in my table i have exactly 2 same rows then i would like to delete one of them. Luckily, if the rganum is the same for any 2 rows then the rest of the fields are exactly the same..

Here is the query i have so far..

DELETE *
FROM TempTable AS t1
WHERE t1.rganum = (SELECT t2.rganum   FROM [STEP 3: PickALLMatchingRows] as t2 WHERE COUNT (t2.rganum)>1 GROUP BY t2.rganum);

This gives me an error saying "An action query cannot be used as a row source"..

Not sure what this means and how to fix the code.

Thanks in advance for any suggestion.

 

[jedraw]

One thing you can do in Access is to build a second table, same structure as your current table, but with no records. Then, create a primary key on field rganum. Append your existing records to the new table.

Access, because of the primary key, will prevent the duplicate records from being added(inserted) to the table.

Steps:
Copy table A to B (structure only).
Modify design of B, add PK rganum
Create an append query
append A.* to B.*
You'll get a message that some records can not be inserted because they would cause key violations.

Now B will contain unique records.

Hope this helps.