I have a table that has four columns. In hindsight, I probably should have set it up differently. The combination of all 4 fields should only appear once.

Example:

Alpa Num State yes/no
ABC 123 CT Y
ABC 123 CT Y
CDE 456 NH N
LMN 789 CA Y
CDE 456 NH N


So I would want to delete all but one of the ABC lines and the CDE lines. Is there an easy way to do this? I can identify all the duplicates, but I don't want to have to delete them manually.

Thanks,
Bill

If all you're trying to do is a one-time cleanup, you can create a query that returns only one for:



and use that to populate a new table. (Note that yes/no really isn't a great name for a field, so I didn't repeat that name in the above)

Once you've created the new table, create a compound index on the four fields and set it to Unique. In that way, you'll never be able to add a duplicate row.

That worked perfectly, exactly what I needed to do.

Thank you!