Forum Discussion
Delete Duplicates (leave 1)
To use the method you described, you should use a unique index for the fields that should not contain duplicates. Your suggestion of a composite primary key isn't necessary
True, either way works...if you're only using one field to identify duplicates. If you have to combine several fields to determine a unique record then as far as I know you'd need to create a multi-field PK
Not true.
Setting a unique index works on a single field or multiple fields.
There is no need to set those fields as a primary key for this purpose.
In general, it is usually better to avoid the use of composite primary keys.
- I don't follow; how do you set an index in MS Access across multiple fields? For example, if I want to avoid duplicates and I have records with First Name, Last Name, City, and State, but I don't want that same person at that location entered twice, how do I set up an index to prevent that?
Also, you can create a composite PK just temporarily to append the records and weed out the dupes, then change back to using a different PK.
Can you explain why a composite primary key should be avoided? Is it a processing thing? Does it matter with record sets below a certain size? Does it appreciably slow down processing when adding records? I'd like to see documentation to determine when this is or isn't a concern.PMFJI:
You set a unique composite index like this in Access:
Of course, you could also include the multiple fields in a Composite Primary Key.
In my experience, the main reason NOT to use composite Primary Keys is the difficulty they present in multi-field queries. If you have to create joins on multiple fields, the SQL statement grows to an unwieldy and confusing state. I don't know if it impacts performance, though.
