<snip>
Regarding how people work with the data and forms, I'd rather not have the users have to use a drop-down for the project name when they enter it. We won't have any two projects with the exact same name.
<snip>
So a form/subform is the way to go.
<snip>
However I would like for the users to be able to select team member names and position types from a drop-down list. I understand now why I shouldn't use lookups in a table; I take it I can set up a drop-down in a form?
<snip>
Yes, you would use a combo box (aka " drop-down") in a form. Even if you had "look up field" defined at the table level, you would *still* have to create the combo boxes in a form!
<snip>
Also what would be the detriment of going back to a list where the last and first are in the same field?
Part of the normalization process is to have all data "atomic" (no multiple values in one field).
You can put the first and last names in one field, but if you ever want to use just the first name, if can get difficult to separate it from the last name.
If you have the first and last names in separate fields, you can concatenate them either way: first & last or last & first. For selecting the personnel and position for a project, in the combo box for the row source you could use:
Code:
SELECT tblPersonnel.PersonnelID, [PersonnelLName] & ", " & [PersonnelFName] AS FullName FROM tblPersonnel;
"PersonnelID" (the PK) would still be stored in the table, but you would see "Smith, John" instead of two fields "Smith" and "John". If you feel comfortable with first and last names in one field, its your choice.