Thanks, Doug. I didn't know a primary key could be a combination of several
fields -- I thought it had to be a number field? And if I use your design,
do I need a foreign key in Names table to connect (somehow) to the pk in the
Projects table?

"Douglas J. Steele" wrote in message
...
Actually, you need at least one additional field in the second table: the
link back to the first table.

Let's assume you currently have Table1:

Id Desc Originator Developer Approver
1 Project A Tom Jones Mary Brown John Doe
2 Project B Mary Brown Jill Roe Mary Smith
3 Project C John Brown John Brown Mary Smith

with Id as the Primary Key.

You'd change Table1 to:

Id Desc
1 Project A
2 Project B
3 Project C

still with Id as the Primary Key.

and Table2 would be:

Id NameType Person
1 Originator Tom Jones
1 Developer Mary Brown
1 Approver John Doe
2 Originator Mary Brown
2 Developer Jill Roe
2 Approver Mary Smith
3 Originator John Brown
3 Developer John Brown
3 Approver Mary Smith

with the combination of Id and NameType as the Primary Key. (If you can
have more than NameType for a particular item, you'd need more for the PK)

Depending on your actual requirements, you could have a Person table, so
that all you store in Table2 is the PersonId.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, but I just don't get it. If I have a single table, with a
col for Nametype, a col for First, and a col for Last, how can I ever
have more than one name per record?

st.

"Douglas J. Steele" wrote in message
...
No, I don't think you should have a separate table for each name type.
Have a single name table with an additional column of NameType.

That'll make queries like "Let me know all records that John Brown is
involved with", "Let me know those records for which Mary Smith was the
Approver" and "Let me know all records where the same person was the
Developer and the Originator" much, much simpler.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"zSplash" wrote in message
...
Thanks, Doug, for responding.
By "create a second table linked to that first table with one row for
each name" that I should have individual tables for each nameType?
That is leave the mainTable with the common information, and then
create a table for nameOriginator, a table for nameApprover, a table
nameDeveloper, etc, with nameData for each of those nameTypes?

"Douglas J. Steele" wrote in
message ...
You'd better explain your precise need, but in general, you wouldn't
put multiple names on a single record.

Typically when you have multiple names on a single record, it means
you've got field names like "Originator", "Approver", "Developer" etc.
That's not a good idea: you're hiding data in the field names.

Instead, you should keep the common information in the one table, and
create a second table linked to that first table with one row for each
name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"zSplash" wrote in message
...
My database needs to track several names (first and last) for each
record. My initial database had all the names (first/last) in the
main table. In an earlier request for help searching for
lastnames, someone suggested that I put all the names in a separate
Names table with a nameType comboBox. Now that I've modified my
database to do that, I see that with that design I can only have one
name per record. (To enter names, I select nameType, and then enter
first/last names for that nameType. I have no way of entering/adding
the first/lastnames for the other nameTypes. Each record has 5 or 6
first/last names to track. If they are all in one Names table, as
suggested, I can only hold one nameType in each record.

Can someone please suggest another way to solve my problem?

TIA