Correcting my DB with the correct PK and Relationship

[gtslabs]

I need help in reconfiguring my tables. I made an access 2003 db file
a few years ago and muttled thru it until now.
I have one table for my clients with the [first] and [last] as the PK.
This table is linked to a projects table via [last] in the
relationships. There is no PK in my Projects Table.

The problem I have is that when I assign a project to a client it goes
to all the clients with the same last name.
How can I fix this and still have the projects with the correct person?

 

[Douglas J. Steele]

First and Last name is seldom a good choice for PK: there are dozens of
cases where people with the same first and last name work together. And it's
rare that you can link tables on only part of the PK.

I think you'd be best off choosing another PK for your Clients table (try an
AutoNumber), and then ensure that the entire PK is used as the FK in the
second table.

Are you sure that linking your Projects table directly to your Clients table
is correct? Will you never have projects that involve more than one client?
I suspect that you need a third table to resolve the potential many-to-many
between the two tables. The PK for that third table would be the entire PK
from both of the other two tables.

 

[gtslabs]

First and Last name is seldom a good choice for PK: there are dozens of
cases where people with the same first and last name work together. And it's
rare that you can link tables on only part of the PK.

I think you'd be best off choosing another PK for your Clients table (tryan
AutoNumber), and then ensure that the entire PK is used as the FK in the
second table.

Are you sure that linking your Projects table directly to your Clients table
is correct? Will you never have projects that involve more than one client?
I suspect that you need a third table to resolve the potential many-to-many
between the two tables. The PK for that third table would be the entire PK
from both of the other two tables.

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

I need help in reconfiguring my tables. I made an access 2003 db file
a few years ago and muttled thru it until now.
I have one table for my clients with the [first] and [last] as the PK.
This table is linked to a projects table via [last] in the
relationships.  There is no PK in my Projects Table.

The problem I have is that when I assign a project to a client it goes
to all the clients with the same last name.
How can I fix this and still have the projects with the correct person?- Hide quoted text -

- Show quoted text -

Doug, regarding the 3rd table,
I send 1 transmittal report per project to 1 Project Manager.
Should I have set up my client DB with COMPANY being one table and a
separate table for the people working there?

 

[gtslabs]

First and Last name is seldom a good choice for PK: there are dozens of
cases where people with the same first and last name work together. And it's
rare that you can link tables on only part of the PK.

I think you'd be best off choosing another PK for your Clients table (tryan
AutoNumber), and then ensure that the entire PK is used as the FK in the
second table.

Are you sure that linking your Projects table directly to your Clients table
is correct? Will you never have projects that involve more than one client?
I suspect that you need a third table to resolve the potential many-to-many
between the two tables. The PK for that third table would be the entire PK
from both of the other two tables.

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

I need help in reconfiguring my tables. I made an access 2003 db file
a few years ago and muttled thru it until now.
I have one table for my clients with the [first] and [last] as the PK.
This table is linked to a projects table via [last] in the
relationships.  There is no PK in my Projects Table.

The problem I have is that when I assign a project to a client it goes
to all the clients with the same last name.
How can I fix this and still have the projects with the correct person?- Hide quoted text -

- Show quoted text -

I was wrong in my OP. I also have an autonumber in my Clients Table
that is also the PK.
So I have 3 for some reason.

 

[Douglas J. Steele]

I can't see any point in having additional fields other than the AutoNumber
in the PK.

You might want to create an index on the first and last name fields, but
they certainly don't need to be part of the PK.

As to your other question, it's difficult for me to comment without knowing
your business. Right now, your set up allows you have multiple projects for
the same client. If you'll never have projects that involve more than one
client, there's likely no need to change.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First and Last name is seldom a good choice for PK: there are dozens of
cases where people with the same first and last name work together. And
it's
rare that you can link tables on only part of the PK.

I think you'd be best off choosing another PK for your Clients table (try
an
AutoNumber), and then ensure that the entire PK is used as the FK in the
second table.

Are you sure that linking your Projects table directly to your Clients
table
is correct? Will you never have projects that involve more than one
client?
I suspect that you need a third table to resolve the potential
many-to-many
between the two tables. The PK for that third table would be the entire PK
from both of the other two tables.

- Show quoted text -

I was wrong in my OP. I also have an autonumber in my Clients Table
that is also the PK.
So I have 3 for some reason.