Combining these fields ..2nd try Sorry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JustMe - 23 Apr 2004 14:00 GMT

My last question didn't make it through the translation. MayI try again?

Structure of table below:

CustID (?? What to set this to ??)
Last4SSAN T
LName T
FName T
MI T

I want to combine [Last4SSAN] + [LName] to create CustID.
What do I set the CustID field to and how do I combine these
two fields and save it to the table?
I have set last4ssan and LName to Primary Key field.

I recieved a response from my orig post but I lost
something in translation.(Sorry Steve) If I set these two fields (seperatly)
to index no dups then it will not allow me to enter another person with the
same last name. (ie; smith)

Last recieved response:In table design view, hold down shift key then click
on
the fileds to highlight, now click on PK icon. Set the
index to unique with no duplicates.
This is known as a composite key.
Two days ago I had a similar post.
SteveAlb

Thanks for your help

Reply to this Message

Tim Ferguson - 23 Apr 2004 17:13 GMT

> I want to combine [Last4SSAN] + [LName] to create CustID.
> What do I set the CustID field to and how do I combine these
> two fields and save it to the table?

You don't. You have two bits of information, so keep them separate. If you
want to _see_ them together, you can do that in a query or in a textbox.

> I have set last4ssan and LName to Primary Key field.

That's fine then -- correct method (but see below).

> If I set these two fields
> (seperatly) to index no dups then it will not allow me to enter
> another person with the same last name. (ie; smith)

That's right -- that is what "no duplicates" means. In any case, if the
Last4SSAN was unique, you wouldn't need the LastName component in the
primay key.

> Last recieved response:In table design view, hold down shift key then
> click on
> the fileds to highlight, now click on PK icon. Set the
> index to unique with no duplicates.
> This is known as a composite key.

But if you have already set this combination to the Primary Key, you don't
need to do it again. The PK is only a slightly superior form of unique
index. You _can_ have other unique keys in a table but it's not usual, and
you certainly don't gain anything by doing the same key twice.

One comment on using names in primary keys -- it's often not a very good
idea. Names do change: apart from marriage and divorce, users spell things
wrong and have to go back an change McGuinness to MacGuiness. When this is
buried in several layers of child tables you find yourself carrying out
major updates too often. If the only unique feature of this table is the
(LastName, Last4SSAN), then you shoud seriously consider using an
artificial key (for example, an autonumber, but something else may be
better instead).

Hope that helps

Tim F

Reply to this Message

John Vinson - 24 Apr 2004 02:42 GMT

>My last question didn't make it through the translation. MayI try again?
>
[quoted text clipped - 7 lines]
>
>I want to combine [Last4SSAN] + [LName] to create CustID.

Sorry, BUT THIS IS A VERY BAD IDEA for several reasons.

It's not guaranteed to be unique. Social Security Numbers are not
reliable - even the full ten-digit number is not guaranteed to be
unique over time, people have false SSAN's, some people legitimately
do not have SSAN's.

Storing two disparate pieces of information in a single field is
called an "intelligent key" - NOT a compliment. It does not provide a
guaranteed unique ID and it also does not help the user uniquely
identify a customer (is SMIT3313 Bill Smith, or Janet Smitowsky, or
who? You need to look at the rest of the data in the table to see -
and you can do that ANYWAY).

>What do I set the CustID field to and how do I combine these
>two fields and save it to the table?

Use an Autonumber as the CustID and display firstname, lastname, and
(perhaps) SSAN or Address along with it in a combo box or on the form
to allow the user to select the desired person.

>I have set last4ssan and LName to Primary Key field.
>
>I recieved a response from my orig post but I lost
>something in translation.(Sorry Steve) If I set these two fields (seperatly)
>to index no dups then it will not allow me to enter another person with the
>same last name. (ie; smith)

That's exactly what you asked it to do, and not what was suggested.
You need ONE unique index on the two fields; you can open the Indexes
window by clicking the index icon in table design view. You can add up
to ten fields in the right column adjacent to and below the name of
the index.

>Last recieved response:In table design view, hold down shift key then click
>on
>the fileds to highlight, now click on PK icon. Set the
>index to unique with no duplicates.
>This is known as a composite key.
>Two days ago I had a similar post.

And I did see a couple of replies to it.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message