Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-08-05 : 07:26:06
|
| Ed writes "Hi,I did a Google Search, and came across your information on how to delete duplicate records. I have a quick question which will be enormously valuable to my organization. We are a small not for profit environmental organization located in Albany, NY....If you require a small payment for this information - please let me know how much would be required.My question is as follows:When using the method below - ALL of the duplicates are removed, whereas - I NEED TO KEEP ONE COPY!! How do I do this?Thank you very, very much for whatever help you might provide.Sincerely,Ed ParkerMembership Services & Sales DirectorEBA/NYS(518) 432-6400 x227Fax: (518) 432-1383www.eba-nys.org1) In the main database window, from Objects, choose Tables. 2) Select the original table and click Copy from the toolbar. 3) Click Paste. In the Paste Table As dialog box, enter a new table name, select the Structure Only radio button, and click OK. 4) Open the new table in Design view and using the row selector, highlight all rows corresponding to the fields that you want to base your duplication criteria on. You can multi-select adjacent rows by clicking and dragging the pointer. You can select nonadjacent rows by using the control key. 5) After all rows are highlighted, click the Primary Key button in the toolbar. A key should appear next to each of the rows. This step requires this combination of fields to be unique for all records. Save and close the table. 6) Copy the original table again and choose Paste. In the Paste Table As dialog box, enter the name of the new table, select the Append Data to Existing Table radio button, and click OK. Access will warn you that it was unable to append all data to the new table. Click Yes when asked if you want to proceed anyway. 7) Open the new table in Design view, select the AddressID row selector, and press the Primary Key button. Save and close the table. 8) Delete the original table. 9) Right-click the new table, choose Rename, and rename the table to the name of the original table." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-05 : 07:29:37
|
Ed,This is even easier than what you posted. In Access, create a new query, but DO NOT add any tables to the design grid, just click Close. Then change to SQL view, and type the following:SELECT DISTINCT * INTO myNewTable FROM myTable;Run that, and you will get a new table (myNewTable) with one copy of each distinct row. You can then drop the old table and rename the new one to match.SQL Team only accepts payment in beer, as in  . We leave the number of beers up to you.  |
 |
|
|
|
|
|
|
|
MS Access - Duplicate Records