field from other fields as primary key

[gaubahn via AccessMonster.com]

Hi,

Im new at using Access and I was wondering how do I create a field in the
Table Design View wherein the value would automatically be the combination of
two fields. i need to do this because the combination of the two fields will
provide a unique variable that i can use as the primary key of my table.

To further explain my situation lets say...
Field1 - FirstName
Field2 - LastName
Field3 - FullName]

I need Field3 to show as (FirstName LastName) so i can make this the primary
key and at the same time I can use only the FirstName or LastName for
querying or sorting.

Thanks and regards,
gau

 

[Rick Brandt]

Hi,

Im new at using Access and I was wondering how do I create a field in the
Table Design View wherein the value would automatically be the combination of
two fields. i need to do this because the combination of the two fields will
provide a unique variable that i can use as the primary key of my table.

To further explain my situation lets say...
Field1 - FirstName
Field2 - LastName
Field3 - FullName]

I need Field3 to show as (FirstName LastName) so i can make this the primary
key and at the same time I can use only the FirstName or LastName for
querying or sorting.

You don't. That's a bad idea and violates database design rules. You can
create a primary key that consists of BOTH fields and that would be the proper
way to do it. Just hold the control key so you can select both fields at once
and then press the "key" button in the toolbar.

Many would use a single "surrogate" key for the primary key in a situation like
this. In that case you would still not want a combined field like you are
describing but would instead use a meaningless numeric field like AutoNumber.
You would still need a unique index on the table consisting of both of your data
fields to prevent duplicates.

 

[David F Cox]

Names are not good choices for primary keys.
google on John Smith
How often have you seen "gaubahn" misspelt?
It is common practise to use an autonumber field as a primary key.

 

[John Vinson]

Im new at using Access and I was wondering how do I create a field in the
Table Design View wherein the value would automatically be the combination of
two fields. i need to do this because the combination of the two fields will
provide a unique variable that i can use as the primary key of my table.

To further explain my situation lets say...
Field1 - FirstName
Field2 - LastName
Field3 - FullName]

I need Field3 to show as (FirstName LastName) so i can make this the primary
key and at the same time I can use only the FirstName or LastName for
querying or sorting.

I'd just like to agree with, and emphasize, David and Rick's points.

A good Primary Key has three main attributes: it should be unique; it
should be stable; and it should preferably be short. Names fail on ALL
THREE of these.

I know three people named Fred Brown, here in little Parma. I once
worked in a big corporation with Dr. Lawrence David Wise and his
colleague, Dr. Lawrence David Wise.

Names change: people change their names when they marry, or for other
reasons.

I knew a former director of the National Cancer Institute named
Srinivasan Venkataraghavan. Names can be long!

Treat names AS DATA; use a short, stable, unique Long Integer or
all-digits text field (like a Social Security Number, though it's best
NOT to use a SSN for legal reasons) as an ID.

John W. Vinson[MVP]

 

[Tim Ferguson]

A good Primary Key has three main attributes: it should be unique; it
should be stable; and it should preferably be short.

The third rule is that it should always be known and available. That's one
more reason why SSNs or HospitalRecordNumbers etc are bad choices.

Tim F

 

[John Vinson]

The third rule is that it should always be known and available. That's one
more reason why SSNs or HospitalRecordNumbers etc are bad choices.

Tim F

That rule, taken literally, would also rule out Autonumbers (or any
other meaningless computer-generated arbitrary key) - unless of course
you can assume that the database itself can look up the value of the
ID given some unique combination of fields in the record.

I guess you could argue that LastName + FirstName + MiddleName +
Suffix + Address might be the "real" primary key, and the autonumber
PersonID is a surrogate key introduced as a sop to program
efficiency... but that's getting into database theological arguments!
<g>

John W. Vinson[MVP]

 

[Tim Ferguson]

That rule, taken literally, would also rule out Autonumbers (or any
other meaningless computer-generated arbitrary key) -

I think we must be at cross purposes here. You know my views on natural
keys vs surrogates; nevertheless even I agree that the huge advantage of
autonumbers is that they are always available -- the db engine just spits
out a new one at you whenever you want one. Compare that to "oh, I've
left my NI card at home with the number on it" or "this patient doesn't
have a HRN number yet because she hasn't been registered with the main
hospital"... that's what I meant by not available.

I guess you could argue that LastName + FirstName + MiddleName +
Suffix + Address might be the "real" primary key,

I think we both agree it's a poor choice, though, as it doesn't fit the
first or the second rules.

but that's getting into database theological arguments!
<g>

But I just love datbase theol..... <sound effect="strangling" />

All the best


Tim F