Forum Discussion
Solved
Delete Duplicates (leave 1)
Hello, I have duplicates in my table and I want to delete only 1 of them. I have searched on the net for some code and found the below and fashioned it to my case. I have an error though and it appe...
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.
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.