Lookup column that stores first and last name in record?

[Deltadog]

Can I create a lookup column in a table that will look up first and last
name, plus store each of them on a record?

 

[Duane Hookom]

IMHO, lookup fields in tables are evil
http://www.mvps.org/access/lookupfields.htm. Also storing "looked up" values
in related tables is evil.

Do you have a good reason for using lookup fields and for storing the
related field values?

 

[Deltadog]

Yes, thie form/table records childrens information for a family homeless
shelter. I would like to be able to look up the parents name and have it
added to the childs record.

 

[Duane Hookom]

The normal way to handle this is to store the primary key from the parents
table in the child table. There is there a good reason why you don't want to
use this standard solution?

 

[John W. Vinson]

Yes, thie form/table records childrens information for a family homeless
shelter. I would like to be able to look up the parents name and have it
added to the childs record.

No. You really DON'T want to do that! <g>

Access databases (all relational databases actually) use the "Grandmother's
Pantry Principle" - "a place - ONE place! - for everything, everything in its
place". The parent's information should be stored in the parent's record; the
child's information should be stored in the child's record. All that should
exist in the child's record would be the numeric (probably) unique ID of the
parent. You would use a Query, or a Form and Subform, or a Combo Box to
*display* the parent's name, but you should not *store* the parent's name.

John W. Vinson [MVP]

 

[Deltadog]

Sooooo.Create a lookup that contains first and last name, but store say the
SS# of the parent rather than the name. I had succeeded in doing that much,
but to make it user friendly for the group of females I am writing this
database forname would of been better in that regard.Thanks

 

[John W. Vinson]

Sooooo.Create a lookup that contains first and last name, but store say the
SS# of the parent rather than the name. I had succeeded in doing that much,
but to make it user friendly for the group of females I am writing this
database forname would of been better in that regard.Thanks

You're missing my point.

The user SHOULD NOT see the table. It makes no difference to the user whether
the table contains a name, a social security number, a meaningless Autonumber,
or even an unreadable GUID.

The user will instead be interacting with a Form. That Form can use tools such
as Subforms and Combo Boxes to *DISPLAY* human meaningful information, such as
names, while it *STORES* the unique ID.

First and last names are *NOT* reliable keys in any case; I know three
gentlemen named Fred Brown, right here in the little town of Parma. You *do*
need a unique ID of some sort.

John W. Vinson [MVP]

 

[Deltadog]

OK I see where you are going....I taught myself how to use Access and there
are still a lot of things I am not good at, or even know what Access is
capable of for that matter. Thanks

 

[John W. Vinson]

OK I see where you are going....I taught myself how to use Access and there
are still a lot of things I am not good at, or even know what Access is
capable of for that matter. Thanks

It might help, then, to look at some of the tutorials my esteemed colleagues
have developed. They can get you off on the right foot and prevent you from
going up a stagnant polluted side channel...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]