Prev: Table structure for Course Attributes
Next: Pull Data From Multiple Tables ????
From: steflark on 29 Dec 2009 13:06
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
From: KARL DEWEY on 29 Dec 2009 13:31
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.

--
Build a little, test a little.


"steflark" wrote:

> 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
From: Clifford Bass via AccessMonster.com on 29 Dec 2009 15:43
Hi,

You really need to change your databases' structure. Right now, if you
have two John Smiths, how do you distinguish the one from the other? First
make a backup or two of the database. You should add a person ID column to
the contact table so that there is a way to identify each person in some
unique way. Typically the column would be of the AutoNumber type, which
creates unique numbers within the table. Make this the primary key. When
you save, it will automatically assign a number. Then in the project table
add a person ID column also, but make it a Long Integer Number type. This is
the type that is used for AutoNumber columns. Run an update query to set the
values in the project table to the values in the contact table. (Post back
with your modified table structures if you are not able to figure it out.)
You should also have a project ID column in the projects table that is
similar to the person ID in the contact table (i.e. AutoNumber). If you do
all this, then when you create your queries you will link on the person ID,
not the name(s). You will also be able to create and enforce a referential
integrity relationship.

Hope this helps,

Clifford Bass

steflark wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1

From: John W. Vinson on 29 Dec 2009 16:32
On Tue, 29 Dec 2009 10:06:01 -0800, steflark
<steflark(a)discussions.microsoft.com> wrote:

>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.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Table structure for Course Attributes
Next: Pull Data From Multiple Tables ????