programatically create primary key

[sea]

Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

[Mike MacSween]

So, I just ran this on a table of 4484 records, of people's names:

SELECT DISTINCT Left([FirstName],2) AS Expr1, Right([Lastname],2) AS Expr2
FROM tblPersons
ORDER BY Left([FirstName],2), Right([Lastname],2);

and got 2398 back.

That's a lot of duplicates.

I expect you'll have a lot of fun using that as a foreign key too.

Work out what the natural primary key of the table is, if you think there is
one, slap a unique no nulls index on it, then insert an autonumber as the
primary key for Access.

That's what I'd do.
"sea" <se*****@hotmail.com> wrote in message
news:f8**************************@posting.google.c om...

Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

[Allen Browne]

Names are not unique, e.g. it's not uncommon for a father and son to have
the same name and live at the same address.

If you want to create this kind of text-based, you need at least 4
characters form surname, + 4 from first name, inserting (say) the underscore
character where the name is less than 4 chars. Then try it, and if it's
already in use, drop the last 3 chars, and replace with a 3 digit number
(i.e. use leading zeros). You can use DMax() to get the highest number used
so far for the prefix.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sea" <se*****@hotmail.com> wrote in message
news:f8**************************@posting.google.c om...

Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

[Bas Cost Budde]

sea wrote:

Is it a good idea to programatically create a primary key?
Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name

Nah, as they say. Maybe you won't need all 456976 unique combinations;
and then again, since the way human names are structured, you won't get
these either. My bet is on "en" "on" and "th" to be in the top 3, for
last names.

Don't. Just don't. I agree, this is a case where the value you base the
PK on will not likely change for the entity (one very big reason for me
not to base the PK on information fields).

What is the linking part of your question? I don't understand that, yet
I feel it is a relevant element.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Andrew]

Primary Keys are a good idea. They allow for referential integrity
when using Foreign Key Relationships. That being said, a Best
Practice for creating Primary Keys is to use numeric (integer) values
that have no specific meaning to the data they are identifying.
MS-Access has the Autonumber data type for just that reason.
This simplifies several things:
• Links between tables: creating Primary Keys using Text fields that
are certain to contain duplicate values is almost never a good idea.
If you have a primary key that is the combination of 1 or more columns
then you will have to replicate ALL of the columns and their data in
EVERY table that you want to create Foreign Key Relationships to.
This creates both space and performance issues. The concept of
"natural primary key" can useful but I always define them as Unique
Indexes.
• IF you find out you have a duplicate and there are relationships
defined you only need to update the Numeric Identifier in the child
records

If you are trying to verify that you have unique people you will need
to use more than just First Name and Last Name. I can see your
proposed solution rapidly growing in scope complexity as you try to
manage this.

se*****@hotmail.com (sea) wrote in message news:<f8**************************@posting.google. com>...

Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names into a form? Maybe have
an invisible field on the form called ID that is linked to the id
field in the table? Will this be ok to do? Any other better
suggestions?

Thank you very much!

[rkc]

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:co**********@news2.solcon.nl...

sea wrote:

Is it a good idea to programatically create a primary key?

Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.

What is the point of generating a non-informational key?

Is autonumber unreliable?

[Allen Browne]

There's no problem with the AutoNumber as a unique identifier. (Well, there
was in A2000, but Microsoft fixed it in one of the earlier JET 4 service
packs.) Most of us use AutoNumbers in every application we create.

There's also no problem in using a natural key. It's a question of style,
but I regularly use a 24-char text field for look up tables (such as
categories or types).

There is also no problem using a semi-informational key (based on the client
name for example). It can be useful if you have a crowded screen, e.g. when
creating staff/volunteer rosters to have a code that you fairly easily
recognise. And it circumvents the issue that if you display a non-bound
column (such as the full name of the employee), it just goes blank if the
person is not in the combo's RowSource (e.g. because you are looking at an
old roster that includes inactive staff that you are not in the list.) There
are also drawbacks to the method, e.g. when someone gets married and changes
name.

So, although people tend to have strong committments to doing things in a
particular way, you are free to choose what suits you and experiment with
the benefits and limitations of each approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:6b****************@twister.nyroc.rr.com...

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:co**********@news2.solcon.nl...

sea wrote:

> Is it a good idea to programatically create a primary key?

Yes, I do that all the time. For numerical primary keys only. To be
honest, except for very rare cases, all my primary keys have migrated to
a non-information field.

What is the point of generating a non-informational key?

Is autonumber unreliable?

[Bas Cost Budde]

Chuck Grimsby wrote:

A non-information field? Care to expand on that?

as "informational" to the end user. PK values, index numbers, ordering
stuff, grouping keys are not informational as they do not store what the
user entered.

I have several fields that I use for tracing, like who was the last one
to modify this record, and when. Those are samples of non-information
fields, too.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea