Multi field link in table relationship

I am going to try to repost my question and give all the pertinent
information. What I really need is instructions on how to create a TWO field
relationship between tables to ensure that a user cannot enter a child record
that does not have a matching parent record.

I am creating a database for Project Peanut Butter in Malawi, the 10th
poorest country in the world. PPB treats children who are suffering from
malnutrition. Treatment lasts a total of about eight weeks. There are a
total of five visits per child (Weeks 0, 2, 4, 6 and 8). Between the short
treatment time and the poverty of the families involved, I can assure you
children really ARE seen in just one clinic. None of these people have cars
to drive their child to a distant clinic. Most are walking and carrying
their child for an hour or more to get to the one that is nearest their
village.

The graduate students who are running the research have set up a Child ID
naming convention that is the two letter clinic code plus a three digit
number. Thus you have MY001 for the first child seen at the MY clinic, NA001
for the first child seen in the NA clinic. There is absolutely no validation
in their Excel “database� to ensure that someone does not fat-finger and
invert the letters for the clinic. I want to split the ClinicID out to a
separate field to ensure that the proper abbreviations are always used.

I have set up a Clinics table with a Key of ClinicID. It has a one to many
relationship to the ChildID table.

The ChildTable is set up with two fields together as the key: ClinicID and
ChildID. Given the circumstances, I feel this is appropriate.
The ClinicVisits table has a three field key: ClinicID, ChildID and
WeekNumber.
I would like to establish a relationship between the ChildTable and the
ClinicVisits table that includes both the ClinicID and the ChildID so that
the data entry person cannot make a typo and enter visit data for a
non-existent ChildTable record.

John Vinson tells me that the relationship window will accommodate up to 10
fields in a relationship. I must be doing something wrong. When I try to
link the ChildID fields, I get an error that says:

A relationship already exists.
Do you want to edit the existing relationship? To create a new
relationship, click No.

If I click No, I get a ChildData_1 table added in the relationships. If I
click yes, I get a dialog box where I cannot access the ChildID field on the
VisitData table.

Can you please give me guidance on what I might be doing wrong when I try to
establish this two field relationship? Thanks VERY much for your help! I
really appreciate it.

--
Ann Scharpf

Build all links at the same time. Open the existing link and add the other
fields.
--
KARL DEWEY
Build a little - Test a little


"Ann Scharpf" wrote:

I am going to try to repost my question and give all the pertinent
information. What I really need is instructions on how to create a TWO field
relationship between tables to ensure that a user cannot enter a child record
that does not have a matching parent record.

I am creating a database for Project Peanut Butter in Malawi, the 10th
poorest country in the world. PPB treats children who are suffering from
malnutrition. Treatment lasts a total of about eight weeks. There are a
total of five visits per child (Weeks 0, 2, 4, 6 and 8). Between the short
treatment time and the poverty of the families involved, I can assure you
children really ARE seen in just one clinic. None of these people have cars
to drive their child to a distant clinic. Most are walking and carrying
their child for an hour or more to get to the one that is nearest their
village.

The graduate students who are running the research have set up a Child ID
naming convention that is the two letter clinic code plus a three digit
number. Thus you have MY001 for the first child seen at the MY clinic, NA001
for the first child seen in the NA clinic. There is absolutely no validation
in their Excel “database� to ensure that someone does not fat-finger and
invert the letters for the clinic. I want to split the ClinicID out to a
separate field to ensure that the proper abbreviations are always used.

I have set up a Clinics table with a Key of ClinicID. It has a one to many
relationship to the ChildID table.

The ChildTable is set up with two fields together as the key: ClinicID and
ChildID. Given the circumstances, I feel this is appropriate.
The ClinicVisits table has a three field key: ClinicID, ChildID and
WeekNumber.
I would like to establish a relationship between the ChildTable and the
ClinicVisits table that includes both the ClinicID and the ChildID so that
the data entry person cannot make a typo and enter visit data for a
non-existent ChildTable record.

John Vinson tells me that the relationship window will accommodate up to 10
fields in a relationship. I must be doing something wrong. When I try to
link the ChildID fields, I get an error that says:

A relationship already exists.
Do you want to edit the existing relationship? To create a new
relationship, click No.

If I click No, I get a ChildData_1 table added in the relationships. If I
click yes, I get a dialog box where I cannot access the ChildID field on the
VisitData table.

Can you please give me guidance on what I might be doing wrong when I try to
establish this two field relationship? Thanks VERY much for your help! I
really appreciate it.

--
Ann Scharpf

Oh! You mean I don't do two separate drag & drops? I just add more fields
in the dialog box?

I just tried it in a database I have here at work. I see the mechanism now.
Thank you, thank you, THANK YOU!
--
Ann Scharpf


"KARL DEWEY" wrote:

Build all links at the same time. Open the existing link and add the other
fields.
--
KARL DEWEY
Build a little - Test a little


"Ann Scharpf" wrote:

I am going to try to repost my question and give all the pertinent
information. What I really need is instructions on how to create a TWO field
relationship between tables to ensure that a user cannot enter a child record
that does not have a matching parent record.

I am creating a database for Project Peanut Butter in Malawi, the 10th
poorest country in the world. PPB treats children who are suffering from
malnutrition. Treatment lasts a total of about eight weeks. There are a
total of five visits per child (Weeks 0, 2, 4, 6 and 8). Between the short
treatment time and the poverty of the families involved, I can assure you
children really ARE seen in just one clinic. None of these people have cars
to drive their child to a distant clinic. Most are walking and carrying
their child for an hour or more to get to the one that is nearest their
village.

The graduate students who are running the research have set up a Child ID
naming convention that is the two letter clinic code plus a three digit
number. Thus you have MY001 for the first child seen at the MY clinic, NA001
for the first child seen in the NA clinic. There is absolutely no validation
in their Excel “database� to ensure that someone does not fat-finger and
invert the letters for the clinic. I want to split the ClinicID out to a
separate field to ensure that the proper abbreviations are always used.

I have set up a Clinics table with a Key of ClinicID. It has a one to many
relationship to the ChildID table.

The ChildTable is set up with two fields together as the key: ClinicID and
ChildID. Given the circumstances, I feel this is appropriate.
The ClinicVisits table has a three field key: ClinicID, ChildID and
WeekNumber.
I would like to establish a relationship between the ChildTable and the
ClinicVisits table that includes both the ClinicID and the ChildID so that
the data entry person cannot make a typo and enter visit data for a
non-existent ChildTable record.

John Vinson tells me that the relationship window will accommodate up to 10
fields in a relationship. I must be doing something wrong. When I try to
link the ChildID fields, I get an error that says:

A relationship already exists.
Do you want to edit the existing relationship? To create a new
relationship, click No.

If I click No, I get a ChildData_1 table added in the relationships. If I
click yes, I get a dialog box where I cannot access the ChildID field on the
VisitData table.

Can you please give me guidance on what I might be doing wrong when I try to
establish this two field relationship? Thanks VERY much for your help! I
really appreciate it.

--
Ann Scharpf