table relationships

[Guest]

I have a main table [ClientInfo] and several other tables [Screening]
[Intake] [Medical]. The main table has a one to many with each of the other
tables (pk id ClientID). Each of the sub tables has a pk (ScreeningID,
MedicalID, IntakeID). A client here can be admitted multiple times so each
ClientInfo record can have multiple records in each of the sub tables. My
question is how do I link the sub table records so that record1 from
Screening is linked to record1 from Intake, etc. It seems to me that if they
arent linked it will cause a problem in some of my reports because they are
based on queries that pull data from all tables. Any suggestions on how my
relationships should be set up?

 

[John W. Vinson]

I have a main table [ClientInfo] and several other tables [Screening]
[Intake] [Medical]. The main table has a one to many with each of the other
tables (pk id ClientID). Each of the sub tables has a pk (ScreeningID,
MedicalID, IntakeID). A client here can be admitted multiple times so each
ClientInfo record can have multiple records in each of the sub tables. My
question is how do I link the sub table records so that record1 from
Screening is linked to record1 from Intake, etc. It seems to me that if they
arent linked it will cause a problem in some of my reports because they are
based on queries that pull data from all tables. Any suggestions on how my
relationships should be set up?

STOP! You're on the wrong track.

A Primary Key *IS NOT* a "record number".

A given Client may well have *many* Screenings, Intakes, Medical.

The child tables should - must!! - have a ClientID field of the same datatype
as the ClientInfo ClientID (Long Integer if that field is an Autonumber). It's
*that* field that you need to link to - not the ScreeningID or IntakeID or
MedicalID. It's called a "foreign key".

You can automate the process of linking by using a Form based on ClientInfo,
with three subforms, one for each table; use ClientID as the Master Link Field
and Child Link Field.

John W. Vinson [MVP]

 

[Guest]

All my subtables do have the ClientID field set as long integer and the
relationships are from the PK ClientID in the main table to the ClientID
field in each subtable (FK). I was pretty sure I had that right. And my forms
are set up the way you descibe as well (form based on CLientInfo, subform
based on Screening, etc, with CLientID as Master/Child Link). So I guess my
question is this all I need to do to be sure I have set this up properly? I'm
still learning about access and want to design this project right.

I have a main table [ClientInfo] and several other tables [Screening]
[Intake] [Medical]. The main table has a one to many with each of the other
tables (pk id ClientID). Each of the sub tables has a pk (ScreeningID,
MedicalID, IntakeID). A client here can be admitted multiple times so each
ClientInfo record can have multiple records in each of the sub tables. My
question is how do I link the sub table records so that record1 from
Screening is linked to record1 from Intake, etc. It seems to me that if they
arent linked it will cause a problem in some of my reports because they are
based on queries that pull data from all tables. Any suggestions on how my
relationships should be set up?

STOP! You're on the wrong track.

A Primary Key *IS NOT* a "record number".

A given Client may well have *many* Screenings, Intakes, Medical.

The child tables should - must!! - have a ClientID field of the same datatype
as the ClientInfo ClientID (Long Integer if that field is an Autonumber). It's
*that* field that you need to link to - not the ScreeningID or IntakeID or
MedicalID. It's called a "foreign key".

You can automate the process of linking by using a Form based on ClientInfo,
with three subforms, one for each table; use ClientID as the Master Link Field
and Child Link Field.

John W. Vinson [MVP]

 

[John W. Vinson]

All my subtables do have the ClientID field set as long integer and the
relationships are from the PK ClientID in the main table to the ClientID
field in each subtable (FK). I was pretty sure I had that right. And my forms
are set up the way you descibe as well (form based on CLientInfo, subform
based on Screening, etc, with CLientID as Master/Child Link). So I guess my
question is this all I need to do to be sure I have set this up properly? I'm
still learning about access and want to design this project right.

Based on what you said (here - the first post was a bit sparse on details!)
you sound like you're doing fine. Try it and see!

John W. Vinson [MVP]