Primary key question

[Anthony]

I am creating a database and I would like to create a unique primary
key by combining part of one field with another. For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234. I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field. So
this unique field (my primary key) will be SA1234. Is this possible?

 

[Jeff Boyce]

Anthony

!STOP!

A basic principle of database design is "one fact, one field". You do not
want to try to stuff two facts into a single field.

If what makes your records unique fits in two fields, use two fields as your
(multi-column) primary key. You are not limited to using just one field.

By the way, the names of people are notorious for not being unique. And
even if you are using something in your [last 4] field that you believe to
be unique (e.g., social security number), you risk trying to add duplicate
rows.

If you need a unique (and non-meaningful) row identifier, take a look at the
Autonumber data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bernard Peek]

In message

I am creating a database and I would like to create a unique primary
key by combining part of one field with another. For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234. I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field. So
this unique field (my primary key) will be SA1234. Is this possible?

It's certainly possible but whether it's desirable is another question
altogether. You could extract the first two characters of one field and
store them in a new derived field then either append the contents of the
second field or just create a composite key.

This would de-normalise the table structure but although generally
undesirable that's not a hanging offence. The real problem is that there
are many different names that have the same first two characters, so
this part of the key doesn't contribute towards the uniqueness of the
key value. And if the numeric field alone is sufficient to guarantee
uniqueness then you don't require those two characters as a prefix.

There may be some reason why you particularly want to have a PK with
that format, but it isn't obvious from your post. Whatever the aim,
there are probably better ways to achieve it.

 

[Anthony]

Anthony

!STOP!

A basic principle of database design is "one fact, one field".  You do not
want to try to stuff two facts into a single field.

If what makes your records unique fits in two fields, use two fields as your
(multi-column) primary key.  You are not limited to using just one field.

By the way, the names of people are notorious for not being unique.  And
even if you are using something in your [last 4] field that you believe to
be unique (e.g., social security number), you risk trying to add duplicate
rows.

If you need a unique (and non-meaningful) row identifier, take a look at the
Autonumber data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am creating a database and I would like to create a unique primary
key by combining part of one field with another.  For example, in the
last name field is the name SAUNDERS and in the last four field are
the numbers 1234.  I would like to take the first two letters of my
last name and combine them with the 1234 to create a unique field.  So
this unique field (my primary key) will be SA1234.  Is this possible?- Hide quoted text -

- Show quoted text -

Thanks Jeff. So you do not suggest combining two fields. The field
that I was trying to create is what we call a Soldier's battle roster
number and it combines the first one or two letters of a last name
(depending on your command) with the last four of the SSN. These
battle roster numbers are typically unique but I can see that there is
a chance there could be a duplicate every now and again. I will use
the autonumber as a primary key. Thanks again.

 

[BruceM via AccessMonster.com]

You can combine the fields in a query or other expression whenever needed.
For instance, in a blank column in query design view:

SoldierNumber: Left([Lname],2) & Right([SSN],4)

I suppose you could do something like this in a query:

SELECT DISTINCT Left([Lname],2) & Right([SSN],4)
AS SoldierNumber
FROM YourTable

Then compare the number of records with the total number of records in
YourTable (assuming one record per person in YourTable). If the counts are
different (i.e. fewer in the query than in the table), there is duplication
somewhere. However, that could be troublesome to maintain.

If the PK is autonumber you can still use an expression to generate the
SoldierNumber value, but there is no guarantee it will be unique. Most
likely it will be, but you will have to be vigilant if you choose to go that
route, and you will need an alternative if duplication happens to occur.

Anthony

[quoted text clipped - 27 lines]

- Show quoted text -

Thanks Jeff. So you do not suggest combining two fields. The field
that I was trying to create is what we call a Soldier's battle roster
number and it combines the first one or two letters of a last name
(depending on your command) with the last four of the SSN. These
battle roster numbers are typically unique but I can see that there is
a chance there could be a duplicate every now and again. I will use
the autonumber as a primary key. Thanks again.