Linking First and Last Name Fields

[neetkleat]

[FONT=Verdana, Arial, Helvetica]Right now I have a table with first and last names of customers as separate
fields.

I have a few other tables with their first name, last name, and other
fields. These tables have to remain separate for some queries I am running.

How do I link the First Name and Last Name fields so that when I enter a
last name in another table, the first name(s) that go with that last name
show up as well?

They are combo boxes right now, but that could change if it would make things easier. Unfortunately the fields exist separately so if I have:

Last Name: First Name:
Smith John
Anderson David
Jackson Michael

so in another table, if I sort by ascending order for each of the fields, I could have:
[/FONT][FONT=Verdana, Arial, Helvetica]
Last Name: First Name:
Anderson David
Jackson John
Smith Michael[/FONT][FONT=Verdana, Arial, Helvetica]
[/FONT]

 

[NigelShaw]

Hi

why do you have first & last names in more than one table? this isnt good practice really. you should re-think your table designs and have only 1 table eith names etc then link to other data tables with a primary & foreign key


NS

 

[neetkleat]

It IS a good practice when you are creating a database that has these tables:

Staff Names
Positions Names
Training Courses
Position Course Requirements
Staff Courses Completed

so that you can created a query to show you what courses staff have not taken in order to be qualified for your position. Or so at least 20 people have told me. They can't all be in the same table.

If I have Last Name and First Name fields in the Staff Names table, I am trying to get them to come as linked, but still separate Last and First Name fields into the Staff Courses Completed table.

 

[ghudson]

Add a unique ID number to the Staff Names table so that you can reference the staff members to pull their first and last names. You would add the staff member ID number to each table that needs their names. That is how you avoid adding their names to each table. Normalization is the key to properly designing a database.

 

[neetkleat]

thanks ghudson!!