Concatenate in Access 2002?

  • Thread starter Thread starter phoric
  • Start date Start date
P

phoric

Does anyone know how to Concatenate in Access 2002?
I need to take two fields, say field INITIAL and SURNAME, and
concatenate them into a third field - FULL NAME. I then need to use
FULL NAME as the 'Primary Key' seeing that it will be unique.

Any help will be GREATLY appreciated due to the nature of my
project!!!

:confused:
 
Yes, don't. At least not in a table as you are asking.

In your case, if you want the Primary Key to be based on both names, you can do that. A
Primary Key can be based on 2 fields. Select both fields using Ctrl+Click then click the
Primary Key button on the toolbar. However, do you expect these two fields to remain a
unique combination as people leave and new ones arrive?
 
Initial and surname are rarely unique. In my own family, myself, my brother
and our children have the same initial and surname. Additionally, NEVER make
a field which has dependency on another value in the same table to COMPOSE
its value. A key can be composed of multiple fields (called a compound
index) but not concatenated into 1. That is especially true of names which
often change. What happens to your key when Jane Doe JDOE marries and
becomes Jane Smith?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
In my many years of experience in DB design and application one rule always
stands out. Using a persons name is never a choice for a "Key Field" in a
table. Use some type of unique identification other than a name. I used to
think my name was unique until I starting designing Databases. Henry B.
Smith is by no means unique in America or Europe or the rest of the world.
I strongly suggest you devise some other means to identify an individual.
For example: employee ID number (unique because some other DB has created
this number and checked that it is indeed unique), SSN (by definition
unique) , Automumber, unique combination of initials and portion of SSN or
some other idea that will ensure a unique number/text combination that will
identify the person. You will have to put on your thinking cap on and
collaborate with your co-workers to come up with a scheme to create a unique
ID for your people relative to the industry you are in, your goals for the
database, ease of use and other circumstances related to your particular
situation.

Put on your thinking cap!

Hope these ideas help.
Cheers,
Henry
 
Henry Smith said:
In my many years of experience in DB design and application one rule always
stands out. Using a persons name is never a choice for a "Key Field" in a
table. Use some type of unique identification other than a name. I used to
think my name was unique until I starting designing Databases. Henry B.
Smith is by no means unique in America or Europe or the rest of the world.
I strongly suggest you devise some other means to identify an individual.
For example: employee ID number (unique because some other DB has created
this number and checked that it is indeed unique), SSN (by definition
unique) , Automumber, unique combination of initials and portion of SSN or
some other idea that will ensure a unique number/text combination that will
identify the person. You will have to put on your thinking cap on and
collaborate with your co-workers to come up with a scheme to create a unique
ID for your people relative to the industry you are in, your goals for the
database, ease of use and other circumstances related to your particular
situation.
Click to expand...

I used to think my name was unique. After all, how many Arvin Meyer's can
there be? Well, there are at least 5 that I know of. Although, I am the only
one doing databases.

I must take issue with your statement that an SSN is by definition, unique.
It is not, nor has it ever been. (Well maybe the first few days they were in
existence. but I wouldn't guarantee that). First, data entry errors will
make up from 1% to 3% of all data entered. That means that in the US, there
are at least 3 million multiplied by the number of times that the number is
used, that are in error. That would equate to 40 or 50 million errors at the
very minimum. Now add to that somewhere around 7 million illegal aliens,
about 4.5 million of which have SSN's. Guess which number they have?

We're not done yet! Since 1996 there have been 33 million cases of identity
theft (That's only what's been reported. Estimates run at around 40
million).

Put it this way: Since the average family moves about once every 5 years,
presumably your phone number is more accurate in any 5 year period than your
SSN.

BTW, How many times have you dialed your own phone number incorrectly?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top