Removing Duplicate Records - Keep Unique Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig Walker - 18 May 2005 02:59 GMT

Hi All:

I have a 500,000 record table, but there are 5 of each entry so really only
100,000 unique records. I want to remove the duplicate data. I know in
Excel, I would do a pivot table, but the pivot table in Access didn't seem to
work the same.

Any help would be greatly apprciated.

Thanks,
Craig

Reply to this Message

Allen Browne - 18 May 2005 03:12 GMT

1. Create a query into this table

2. Drag the fields you want into the grid.

3. Open the Properties box (View menu), and set the Unique Records property
to Yes. (Alternatively, set the Unique Values property instead if you just
want the deduplication based on the fields chosen in the query, not all
fields.)

If you want to create a table that does not have the duplicates:

4. Change your query to a Make Table query (Make Table on Query menu).
Access will ask for a new name for the query.

5. Run the query. (Run on Query menu.)
After checking the results, you can delete the original table.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi All:
>
[quoted text clipped - 9 lines]
> Thanks,
> Craig

Reply to this Message