table relationships

[robertm600635]

I have set up several table that I want to link together using the field
[SSN] as the primary key. Do I need to put [SSN] on each table and make it
the primary key or does the primary key need to be a different field on each
table?

 

[tina]

you only join two tables by their respective primary key fields when the
tables have a one-to-one relationship. this is a valid entity relationship
in relational design, but not a common one; one-to-many and many-to-many
relationships are the norm. to link tables that have a one-to-many
relationship, you link the *primary* key field(s) of the "parent" table to
the matching *foreign* key field(s) of the "child" table.

if this is unclear or even unfamiliar to you, suggest you read up (or read
more) on the principles of relational design. for an excellent list of
resources, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

hth

 

[C.Evans]

It really depends. A primary key does not allow duplicates. If you will
have more than one entry in a table for a given social security number, then
you need to have something else as the primary key. If, however, you would
only have one record in a table for the social security number, then you can
use it as a primary key.

After you have set each table up with all necessary primary keys, you set
the relationships in the relationship manager window. If table A has social
security number as a primary key, and table B has social security number in
it, then when you match it you will have a one to many relationship. If
tableB has social security number as a primary key, then you will have a one
to one relationship.

As a side note - a primary key should be something which doesn't change and
every record will have. A social security number could be entered
incorrectly. It is also possible that a person might not have a social
security number (for example, noncitizens here on a work permit). Perhaps it
would be better to use something else - even an autonumber.

 

[Speedy]

Each table should have its own primary key, does not have to be the same one
in the first table, they could have different names, but the primary key in
the first table should be in all the other tables that will share a
relationship with the first one. This key becomes a foreign key in all links.
EXAMPLE:
CUSTOMER TABLE: PK = cust_id
ORDER TABLE: PK = oder_id
FK = cust_id
The tables will be joined on cust_id

 

[John Vinson]

I follow what you are saying, but I'm still a little unclear on the
application of it. What I need to accomplish is to have a form based on a
client infor table with [SSN] as the primary key. On the form I will need
several subforms, each related to a different table, which I also have [SSN]
as the primary key on those tables. My goal here is to be able to display all
of a client's info on these forms and subforms, my thought was that if I used
the [SSN] primary key I could pull up client data (using a combobox) by
simply typing in the SSN. So, If these tablers are linked by SSN will all the
subforms synch up with the SSN typed into the main form?

What are these related tables? Does each of them have ONE AND ONLY ONE
record, ever, for the client?

If so, why multiple tables? If there is only one value for each
client, you could just put all the fields in the client table, no?

As Tina says - one to one relationships are QUITE rare, and may
indicate improper design of your database.

That said - if you have the SSN as the primary key of your client
table; base a Form on that table; and have Subforms on that table
using the SSN as the master/child link field (whether the child table
has a one to one relationship with SSN as the primary key, or a one to
many relationship using the SSN as a non-primary foreign key), then
yes, the records will be kept in synch.

John W. Vinson[MVP]

 

[Jeff Boyce]

As tina and John have pointed out, how you relate your tables (?SSN) depends
on how your tables are related. If you'll provide a bit more specific
description, the folks responding here may be able to offer a bit more
specific suggestions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[robertm600635]

The reason I have used seperate tables is that I have about 220 fields I'm
working with and I've been told that it is not a good idea to have so many
fields in one table. Each client will have only one record. This is for a
drug rehab and we collect information during the screening and intake process
(quite a lot of info). I want to have a user friendly form that will make it
easy to input all this info. I just have never tackled a project this large
before and am trying to get some direction.

I follow what you are saying, but I'm still a little unclear on the
application of it. What I need to accomplish is to have a form based on a
client infor table with [SSN] as the primary key. On the form I will need
several subforms, each related to a different table, which I also have [SSN]
as the primary key on those tables. My goal here is to be able to display all
of a client's info on these forms and subforms, my thought was that if I used
the [SSN] primary key I could pull up client data (using a combobox) by
simply typing in the SSN. So, If these tablers are linked by SSN will all the
subforms synch up with the SSN typed into the main form?

What are these related tables? Does each of them have ONE AND ONLY ONE
record, ever, for the client?

If so, why multiple tables? If there is only one value for each
client, you could just put all the fields in the client table, no?

As Tina says - one to one relationships are QUITE rare, and may
indicate improper design of your database.

That said - if you have the SSN as the primary key of your client
table; base a Form on that table; and have Subforms on that table
using the SSN as the master/child link field (whether the child table
has a one to one relationship with SSN as the primary key, or a one to
many relationship using the SSN as a non-primary foreign key), then
yes, the records will be kept in synch.

John W. Vinson[MVP]

 

[John Vinson]

The reason I have used seperate tables is that I have about 220 fields I'm
working with and I've been told that it is not a good idea to have so many
fields in one table. Each client will have only one record. This is for a
drug rehab and we collect information during the screening and intake process
(quite a lot of info). I want to have a user friendly form that will make it
easy to input all this info. I just have never tackled a project this large
before and am trying to get some direction.

If you have 220 fields - that's about 180 more than I like to see in
any record.

I very strongly suspect that you've fallen into the very common error
of "committing spreadsheet", storing data in fieldnames. If you have
fields like (say) the names of drugs used, questions that you ask the
client, etc. then you should reconsider your design. Let's say you
have 10 fields of basic biographical data, and 210 subjects about
which you need information.

You could have a table of Subjects, with 210 rows, one for each
subject. You'ld then have a table of Answers, with fields for the
ClientID, the SubjectID, and that client's answer to that question.
"Fields are expensive, records are cheap" - this lets you easily enter
data using a subform (just TWO forms, a mainform for the client and a
subform for Answers!); it also lets you add, change, or remove
questions without needing to redesign your tables, forms, reports and
queries when you need to add or delete a fieldname.

If you'ld like to discuss the data structure here, please feel free;
if you would like to take it offline, I'd be willing to give some free
consultation. Just email me at jvinson <at> wysard of info <dot> com.
I've had some good friends whose lives were saved by drug rehab and
I'd like to pay some of that back.

John W. Vinson[MVP]

 

[Darryl Kerkeslager]

Particularly in a drug rehab application, I would suggest you not use SSN as
your primary key.

1. Primary keys are used to efficiently link tables and create
relationships.
2. Once you create a relational structure around a key, the key cannot be
changed, only deleted.
3. Therefore, primary keys should never be changeable.
4. SSN are outside your control, and can in fact be changed by the SSA.
5. Not only can SSN be changed, but the individual providing the SSN may
provide an incorrect SSN, deliberately or accidentally.
6. Drug rehabs, by nature, would tend to have a high percentage of criminal
clients, who are also most likely to deliberately provide an incorrect SSN
(criminal intent) or accidentally (lost ID, memory issues).

You may want to consider that each of your drug rehab clients may have more
than one phone number, more than one family contact, more than one address
(over time), more than one drug of choice, etc. Each one of these "more
than one" items should be a separate table.

--
Darryl Kerkeslager

The reason I have used seperate tables is that I have about 220 fields I'm
working with and I've been told that it is not a good idea to have so many
fields in one table. Each client will have only one record. This is for a
drug rehab and we collect information during the screening and intake
process
(quite a lot of info). I want to have a user friendly form that will make
it
easy to input all this info. I just have never tackled a project this
large
before and am trying to get some direction.

I follow what you are saying, but I'm still a little unclear on the
application of it. What I need to accomplish is to have a form based on
a
client infor table with [SSN] as the primary key. On the form I will
need
several subforms, each related to a different table, which I also have
[SSN]
as the primary key on those tables. My goal here is to be able to
display all
of a client's info on these forms and subforms, my thought was that if I
used
the [SSN] primary key I could pull up client data (using a combobox) by
simply typing in the SSN. So, If these tablers are linked by SSN will
all the
subforms synch up with the SSN typed into the main form?

 

[BruceM]

Duane Hookum's At Your Survey database may be of help:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'
Watch out for line-wrapping in your newsreader window. It should be all on
one line.

The reason I have used seperate tables is that I have about 220 fields I'm
working with and I've been told that it is not a good idea to have so many
fields in one table. Each client will have only one record. This is for a
drug rehab and we collect information during the screening and intake
process
(quite a lot of info). I want to have a user friendly form that will make
it
easy to input all this info. I just have never tackled a project this
large
before and am trying to get some direction.

I follow what you are saying, but I'm still a little unclear on the
application of it. What I need to accomplish is to have a form based on
a
client infor table with [SSN] as the primary key. On the form I will
need
several subforms, each related to a different table, which I also have
[SSN]
as the primary key on those tables. My goal here is to be able to
display all
of a client's info on these forms and subforms, my thought was that if I
used
the [SSN] primary key I could pull up client data (using a combobox) by
simply typing in the SSN. So, If these tablers are linked by SSN will
all the
subforms synch up with the SSN typed into the main form?

What are these related tables? Does each of them have ONE AND ONLY ONE
record, ever, for the client?

If so, why multiple tables? If there is only one value for each
client, you could just put all the fields in the client table, no?

As Tina says - one to one relationships are QUITE rare, and may
indicate improper design of your database.

That said - if you have the SSN as the primary key of your client
table; base a Form on that table; and have Subforms on that table
using the SSN as the master/child link field (whether the child table
has a one to one relationship with SSN as the primary key, or a one to
many relationship using the SSN as a non-primary foreign key), then
yes, the records will be kept in synch.

John W. Vinson[MVP]