Issue with a Composit Key

[JimO]

I have a table with names (actors, directors, producers etc.) with [Prefix], [FirstName], [MiddleName], [LastName], [Suffix]. I am trying to prevent duplicate entries so I want to set up a composite key based on all of the fields but I am unable to get more than 2 fields to work.

As an example there could be:

Alan Hale
Alan Hale Sr.


Alan hale Jr.
Sir Alan Hale
Alan G. Hale

so some fields will be blank. As I said I can get the first and last names to prevent duplicates but when I try to include other fields it stops working and allows duplicate entries.

Jim O

[orange]

Do you consider your example names to be duplicates? Access won't.
What exactly do you want to prevent?
You may have to review records where first and last name match anther record, then go to address etc to determine if duplicates.
This info may help with unique composite index

[JimO]

As I have it set up now, Alan Hale Jr. has to be entered as [FirstName] Alan [LastName] Hale Jr. and the same for Hale Sr. I would like to enter each as Alan Hale and then in the Suffix field 'Jr.' or 'Sr.' respectively. I have to make each last name unique.

Jim O

[orange]

And what if you have 2 or 3 Jim Smith, or Tom Brown?

You can get duplicate names eg FirstName LastName
Not everyone will have or use a suffix, so it will be NULL quite often.
The Sir will be very rare.

I've dealt with names and addresses and think you might want to have some logic/code to review other attributes when you
get a duplicate on first and last name. You may the want to see state, zip, full address, phone number, email..... to ensure this is a duplicate, then put in to a rejected file with date and reason.