Primary Key Query

[Ben1642 via AccessMonster.com]

HELP!!!!

I'm designing a Database to record names of individuals and incidents they
have been involed in. I want the primary key to consist of, their LAST NAME
and 6 digits of their date of birth.

Example

Unique Reference No (primary key): SMIT-090189
Last Name: SMITH
date of birth: 09/01/89

I'm crap at VB so can anyone suggest a script for me??????

mail back (e-mail address removed)

Ta

 

[Douglas J. Steele]

There's no reason to have a concatenated key like that.

Access will let you specify up to 10 distinct fields in an index, so your
primary key could be the combination of the Last Name and Date of Birth
fields. However, that won't be sufficient to actually provide a primary key:
what are you going to do about twins?

 

[Jeff Boyce]

As well as Doug's twins, I know a fellow who got into serious trouble with
the authorities. It turns out that his name and DOB were IDENTICAL to
another fellow's, and THAT fellow was the culprit the authorities were
after.

Name & DOB are NOT unique.

 

[Ben1642 via AccessMonster.com]

Hmmm, never thought of that, lol!!!

So an auto generated number will be sufficient and will be different in the
instance of twins?

Regards

CPD

There's no reason to have a concatenated key like that.

Access will let you specify up to 10 distinct fields in an index, so your
primary key could be the combination of the Last Name and Date of Birth
fields. However, that won't be sufficient to actually provide a primary key:
what are you going to do about twins?

HELP!!!!

[quoted text clipped - 14 lines]

Ta

 

[Vincent Johns]

An "Autonumber" (either incremental or random) will be sufficient to
make the key values unique. But do you have the authority to set up the
unique keys? If so, your key will be the only one (as long as the
person's records are in your database) to identify the person. If not,
I suggest that you try to get a copy of whatever key is being used in
your organization as a reference number to identify persons, and use it
either instead of, or in addition to, your Autonumber field.

Concerning your putative use of "Unique Reference No (primary key):
SMIT-090189"... You could use something like this as a lookup value.
You could define a Lookup Query whose 1st field is the Table's primary
key, and whose 2nd field is "SMIT-090189", maybe followed by some other
datum from your Table that helps to distinguish persons. Then, in list
boxes, you could display this 2nd field to users trying to select
records. (With a Lookup Query, you can display the 2nd field in the
list box while you store the 1st field in the Table.) This would have a
chance of being meaningful to your users; they could choose
"SMIT-090189" to refer to record number 8856277. (The Autonumber value
stored in the Table usually by itself means little or nothing to a human
being.)

If this Lookup Query later became difficult to use because of frequent
collisions, you could easily redefine it (maybe append the last 2 digits
of the Autonumber to have it display "SMIT-090189-77") to reduce the
collisions without making any change at all to the underlying Tables.
However, you would likely need to advise your users that they'd be
seeing slightly different values in the list boxes.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.