Linking First & Last Names in a table to a Full Name field in anot

[steflark]

Hi. We have been using Access for a project database for years. However, we
just added our customer information (formerly in ACT) to Access. Now I have
two fields in our contact table (First Name, Last Name) but only one field in
the existing project table (Full Name). I would like to link the customer to
his active projects in our existing project table. How do I link these so
that when I view a contact I will see a list of his projects? I have created
a query that combines the First Name and Last Name but then I am not sure
what to do with that query field.
Thanks,
Stefanie

 

[KARL DEWEY]

You can use that query in another to join it with the Full Name field.

But better would be to create a NameList table with primary key and unique
index of First Name & Last Name. Populate it from both tables to build a
consolidated table, checking for spelling errors and typos. Then match it
back to your two tables by adding a foreign key field related to the NameList
table primary key.

 

[John W. Vinson]

Hi. We have been using Access for a project database for years. However, we
just added our customer information (formerly in ACT) to Access. Now I have
two fields in our contact table (First Name, Last Name) but only one field in
the existing project table (Full Name). I would like to link the customer to
his active projects in our existing project table. How do I link these so
that when I view a contact I will see a list of his projects? I have created
a query that combines the First Name and Last Name but then I am not sure
what to do with that query field.
Thanks,
Stefanie

Let me just agree with Karl and Clifford. A primary key should meet three
criteria: it *must* be unique, and should also be stable and short. Names fail
all three!

I once worked with Dr. Lawrence David Wise and his colleague, Dr. Lawrence
David Wise. People can change their names, by marriage or by legal name
change. Names can be nicknamed - is "Robert Jones" the same person as "Bob
Jones"?

Restructure your table with a unique CustomerID and use THAT to link to your
projects.