combine a new primary key value as the fullname initial

[Guest]

I have a data Insert form with FirstName, MiddleInitial, LastName,
Email,Address,
City,State,WorkPhone. There is one more field not included in the form which
is AgentID as the primary Key. I want to insert one record in the table using
these fields and values by computing and combining the first letter of the
firstname,middleinitial,and lastname as the primary key when i execute the
the data it say cannot be null.Please a need some suggetion how to use the
SQL query to include the AgentID in the table by abbriviate the agent full
name as the AgentID
i been trying to figure it out and i'm stuck
Thanks.

 

[Guest]

What if you have the same name, or 2 names with the same first letters, in
that case you'll get a duplicate key error and the record won't be added.

You must find a unique key field (like id) that can't repeat itself for two
people.
If you don't have a unique field, use a field, AutoNumber, that will be the
key field for the table.

 

[John W. Vinson]

I have a data Insert form with FirstName, MiddleInitial, LastName,
Email,Address,
City,State,WorkPhone. There is one more field not included in the form which
is AgentID as the primary Key. I want to insert one record in the table using
these fields and values by computing and combining the first letter of the
firstname,middleinitial,and lastname as the primary key when i execute the
the data it say cannot be null.Please a need some suggetion how to use the
SQL query to include the AgentID in the table by abbriviate the agent full
name as the AgentID
i been trying to figure it out and i'm stuck
Thanks.

Well... it's A Very Bad Idea.

What would be the primary key values for

David Elvin Rockwell
Doris Elaine Rodman
Dulce Elisa Rodriguez

And what would be the value if (as is often the case) the person does not
have, or does not use, a middle name?

And what will you do with LGA - Louse G. Anderson - when she marries Bob
Zimmerman and becomes Louise Anderson Zimmerman?

Names - and artificial keys generated from names - *are not unique*. They are
also not stable. A Primary Key must be unique, and should ideally be stable.
Your suggestion fails on both counts!

I'd use either an Autonumber or a sequential meaningless numeric ID, together
with some simple code on your form to check for duplicate names. Note that
it's not at all unusual for two people to have the same name, so duplicate
names aren't necessarily an error... but they should generate a warning.

John W. Vinson [MVP]