Multiple key field problem: (Long field with potential errors) 1

[Sherman6789]

We have a table with the key field called Program-ID. This is a unique field as required by Access.

My problem is that some items are coming in without an assigned Program-ID. This means that I can not put them in the table. However, I have been told to put them in THE table anyway. I have found that without the Program-ID, the only way I see to make the records unique is to combine three fields as the key. The three fields are: 1. Reviewer's Last Name, 2. Reviewer's First Name, and 3. The Program Title. With these combined we will have uniqueness.

Problem: 1. Program Title can be as long as 70 characters, Possible duplicate Reviewer names but the complete combination will eliminate duplicates.

Problem: 2. Names and Titles are manually typed in by humans. This means that one person may put an extra space somewhere or by accident, abbreviate some words and these can defeat the "no dups" rule.

Any suggestions?

 

[dhookom]

Is there a reason why you don't use an autonumber primary key? Regarding problem 2, can you create a combo box that allows users to enter values not in the list?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sherman6789]

To Dhookom,

The autonumber is being used; however, that will not insure that the Program-ID is not duplicated.

 

[dhookom]

I don't understand what the first and last names and program title have to do with identifying uniqueness when you have an Autonumber.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sherman6789]

Thanks Hookom.

Based on the established procedures from our headquarters, the Program-ID is the official identification for each item. When advanced items come in before a Program-ID number has been assigned, I must have a unique way to identify each item. The Autonumber only prevents another item from being entered into the system with the same autonumber assigned. This does not prevent the same item from being introduced into the system. Without a Program-ID number assigned, it seems that the only way to uniquely identify each item is a combination of the review's name and the title. Reviewers can be in the table several times. The same title can be in the table several times. However, no one user can have the same item title in the table. That is why I figured that we needed both the item title and reviewer's name together to make the items unique when they don't have Program-ID numbers. Additionally, since the name is broken into first and last name fields, I thought that I needed use both since there can be two or more reviewers with the same last name.