Eliminate duplicate rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field1 and field2 that together make a unique key. First, how can I
set them up as a combined key. Second, how can I code an SQL to eliminate
duplicate rows.

Thanks

Brenda
 
To make a compound primary key, select one field in Design view, then hold
the control key and select the other. Then click the button with a key icon
on it.

To delete rows with duplicate values, go to my website
(www.rogersaccesslibrary.com) and look for a sample database called
"RemoveDuplicates2k.mdb".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I have a field1 and field2 that together make a unique key. First, how can I
set them up as a combined key. Second, how can I code an SQL to eliminate
duplicate rows.
Click to expand...

You won't be able to create a unique key in a table which has
duplicates. I'd suggest that you copy and paste the existing table,
*design mode only*, to a new table. You can then create the unique
index in this new (empty) table. To do so click the Indexes icon
(which looks like lightning hitting a datasheet); type a name for the
index in the left column and select the two fields, one on the same
line and one on the next, in the right-hand column. Specify that this
index is unique using the checkbox.

Then run an Append query from your existing table into this new one.
One arbitrary record of each set of duplicates will be added; you'll
get a warning message that "xxx records were not added due to indexing
violations" or the like. These are the duplicates which are being
discarded.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top