Deleting Duplicate records

[ThornPCC]

Hi,<br>
<br>
This may be a silly question, but.........<br>
<br>
I know that Access 97 has a query to find duplicate records in a database. How would I go about having it delete those duplicates? One of my users has a Billing program that is built on dbf files using clipper. It's not y2k compliant, so I built her a access database by importing the dbf. She had to enter 380 duplicate records to get a report printed. I now want to delete them from my access table, but don't recall how.<br>
<br>
Thanks. <p>Phil<br><a href=mailto:Thorn@full-moon.com>Thorn@full-moon.com</a><br><a href= > </a><br>Most of us go through life not knowing what we want, but feeling damned sure that this isn't it.<br>

 

[DougP]

Run your query<br>
Make a copy of your table by the following:<br>
Press ctrl-C on it<br>
then Press Ctrl-V <br>
click the left side of the top record so the entire record is highlighted.<br>
hold down the shift key <br>
Press the page down button until all records are highlighted<br>
Press the delete key on your keyboard<br>
it will say are you sure click yes<br>
your done<br>
double check that you did it correctly in your table<br>
If so delete copy of table ONLY after you are sure.<br>

 

[rotschreck]

1. Create a new query based on the original table containing duplicates.<br>
2. In query Design view, click the Query Type<br>
button on the toolbar, and then click Append Query.<br>
3. In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.<br>
4. Include all the fields from the original table by dragging the asterisk (*) to the query design grid.<br>
5. Click Run on the toolbar.<br>
6. Click Yes when you receive the message that you're about to append rows.<br>
7. Click Yes when you receive the message that Microsoft Access can't append all the records in the append query. This transfers only unique records to your new table and discards the duplicates.<br>
8. To see the results, open the table from the Tables tab in the Database window.<br>
9. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Maitredudonjon's</a><br>

 

[rotschreck]

Sorry. Before you append the unique records from my last post, you should create the new table to append them to..<br>
<br>
1. In the Database window, click the Tables tab.<br>
2. Click the name of the table you want to delete duplicate records from.<br>
3. Click Copy on the toolbar.<br>
4. Click Paste on the toolbar.<br>
5. In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.<br>
6. Open the new table in Design view, and select the field(s) that contained duplicates in the table you copied.<br>
7. Click Primary Key on the toolbar to create a primary key based on the selected fields.<br>
8. Save and close the table. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Maitredudonjon's</a><br>

 

[elizabeth]

Do you have 380 dupes of one record and want to get rid of them all? If so then DougP's method works. A little shortcut...you can click on the little square just above the left of the first record and it highlights all records. If you want to get rid of just the 379 extra of course you would click on the *second* record and page down etc.<br>
<br>
If you have 190 pairs of dupes, or something like that, rotscheck's method works for me if I first put a unique index on the field(s) that allows Access to identify the dupe. <br>
<br>
<br>
<br>
<br>

 

[ThornPCC]

Thank You All.<br>
<br>
rotschreck's solution did the trick. It sure made it easier than going in and deleting each record by itself.<br>
<br>
<br>
<br>
<p>Phil<br><a href=mailto:Thorn@full-moon.com>Thorn@full-moon.com</a><br><a href= > </a><br>Most of us go through life not knowing what we want, but feeling damned sure that this isn't it.<br>