Maintaining unique ID number

[S Molly T]

Not sure if this is the place for this but seemed to be the closest fit since this is maybe a data entry (input) issue??

Currently working on a very simple database that consists of one table with seven fields.

recID -number field auto enter serial number

dateCreated - auto enter time stamp

dateModified - auto enter time stamp

name - text field

nameID - number field auto enter serial number

phoneNumber - text field

date contacted - date field

Data entry happens one of three ways

1) manually by creating a new record typing in the name, phone number and date contacted

2) importing data from a text file

3) duplicating an existing record and changing the date contacted

People using the database have no knowledge if the name already exists in the database or not. They are simply typing names, phone numbers and date contacted (or importing them from a text file).

All design issues aside, what is the best way to ensure that the same nameID number is applied to each occurrence of the same name.

Example

John Cecil Browns was contacted for the first time on 1/1/2011 and given a nameID of 0034.

The next time John Cecil Browns was contacted was on 2/1/2011 and is given the nameID of 0082. Now what should happen is he should be given the same nameID of 0034.

Is this somehow done using some of the Validation options?

[dansmith65]

Ideally, the name should exist in a related table, and the ID should be used to reference it. Which means the data entry process should involve searching for an existing name before entering a new one in the related table, then referencing it in your current table.

Otherwise, the way you have it setup, you can pretty much just drop the nameID field and do a text match to find people, which is going to be inaccurate because John Cecil Browns will end up being entered many different ways:

Jon Cecil Browns

John C. Browns

John Cecil B.

etc.