Establish relationship between tables using TWO fields

[Ann Scharpf]

I have a ChildID table and a ClinicVisits table. I want to ensure that the
user cannot create a ClinicVisits record that does not have matching values
for ClinicID and ChildID in the ChildID table.

When I try to establish a relationship in the relationships table, it will
only allow me to set up a link for one field between the two tables. How do
I establish a two-field relationship between tables?

I've seen posts about foreign keys and junction tables but I don't quite
understand the foreign key posts and the junction table seems to be for
many-to-many relationships and I am setting up a one-to-many relationship.

Thanks for any help you can give me.

 

[Jeanette Cunningham]

Assuming that a child can have many clinic visits, you would create a link
between the field ChildID in the ChildID table and the field ChildID in the
clinic visits table. Enforce referential integrity.
You can also set the ChildID field in the clinic visits table to Required =
Yes.
You do not need the field for ClinicID in the ChildID table.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ann Scharpf]

HI, Jeannette:

Thanks for responding. I see that it's late here and I didn't include all
the pertinent information.

In the child table, the clinic and the child id TOGETHER are the key to the
table.

I believe they have data set up so that you can have the following id's

MM100
XX100
ZZ100

Where the same child number can appear with multiple clinic codes. As in
the 100th child at XX clinic. That's why I want to include both fields in
the link.

 

[Ann Scharpf]

I should also mention that I'm developing a database to replace a very
unwieldy Excel spreadsheet. They already have data on HUNDREDS of children.
I am not going to get them to change their ID conventions at this point. I'm
stuck with what they've set up.

 

[John W. Vinson]

I have a ChildID table and a ClinicVisits table. I want to ensure that the
user cannot create a ClinicVisits record that does not have matching values
for ClinicID and ChildID in the ChildID table.

When I try to establish a relationship in the relationships table, it will
only allow me to set up a link for one field between the two tables. How do
I establish a two-field relationship between tables?

The relationships window can support up to TEN field joins.

Drag the ClinicID to the ClinicID, and the ChildID to the ChildID. Set the
enforcement on both join lines.

I've seen posts about foreign keys and junction tables but I don't quite
understand the foreign key posts and the junction table seems to be for
many-to-many relationships and I am setting up a one-to-many relationship.

Why is there a ClinicID field in the Child table, though? A clinic is not an
attribute of a person; could not the same child be seen at multiple clinics?
Your ClinicVisits table should (I would expect) have a single field
relationship to a table of Clinics (where did this visit occur) and,
separately, a relationship to a table of Children (who visited).

 

[Ann Scharpf]

Why is there a ClinicID field in the Child table, though? A clinic is not an
attribute of a person; could not the same child be seen at multiple clinics?
Your ClinicVisits table should (I would expect) have a single field
relationship to a table of Clinics (where did this visit occur) and,
separately, a relationship to a table of Children (who visited).

Actually, no, the child will not be seen in more than one clinic. I'm
developing the database for Project Peanut Butter, a small
humanitarian/medical organization that is working and doing research in
Malawi, Africa. They treat starving children in villages that are far flung;
the families are too poor to own bicycles, much less automobiles. The staff
goes every two weeks to each site and sees the children at the clinic that is
nearest to the child's village.

The researchers had set up a child id convention that consists of two
letters and three digits. My concern is that the data entry person fat
fingers the letters and enters MU instead of MY, for example. (The excel
formulas that they've built to filter data by clinic just use the first two
characters of the child id.) Using the convention they were, it would be
easy for a record to be lost for all intents and purposes by having a typo in
those first two characters. I wanted to break the clinic out to a separate
validatable (is that a word?) field so I could prevent that from happening.

Am I barking up the wrong tree?

 

[Ann Scharpf]

John:

I must be doing something wrong. I link the ClinicID fields. Then, 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.

 

[Jeanette Cunningham]

Ann,
I think you are describing a clinic that exists in multiple places when you
say
"goes every two weeks to each site and sees the children at the clinic that
is
nearest to the child's village".

Each child belongs to one site? is this going to be the way it is?
What happens when a family moves to a different village - those children
will now be at a different site.

You could have a many to many relationship.
One table for sites.
One table for children.
One table that records which children are visited, at which site the visit
occurred and the date for each visit in this table.
The last table above is the junction table between sites and children.
If this fits your data, you have a many to one relationship between the
table for sites and the table for sites, visits and date.
You would then have a many to one relationship between the table for
children and the table for sites, visits and date.

Users would choose the name of the site for a particular visit on a
particular day.
Then they could choose the name of each child that is visited at that site
on that date.

However you are the best person to make the decision about how to manage the
data - as only you will know all the details.
My description is just a suggestion.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

Actually, no, the child will not be seen in more than one clinic. I'm
developing the database for Project Peanut Butter, a small
humanitarian/medical organization that is working and doing research in
Malawi, Africa. They treat starving children in villages that are far flung;
the families are too poor to own bicycles, much less automobiles. The staff
goes every two weeks to each site and sees the children at the clinic that is
nearest to the child's village.

The researchers had set up a child id convention that consists of two
letters and three digits. My concern is that the data entry person fat
fingers the letters and enters MU instead of MY, for example. (The excel
formulas that they've built to filter data by clinic just use the first two
characters of the child id.) Using the convention they were, it would be
easy for a record to be lost for all intents and purposes by having a typo in
those first two characters. I wanted to break the clinic out to a separate
validatable (is that a word?) field so I could prevent that from happening.

Am I barking up the wrong tree?

It sounds like they have the childID and clinicID concatenated into one field,
and you have separate fields in other tables. You say elsethread:

Thanks for responding. I see that it's late here and I didn't include all
the pertinent information.

In the child table, the clinic and the child id TOGETHER are the key to the
table.

I believe they have data set up so that you can have the following id's

MM100
XX100
ZZ100

Where the same child number can appear with multiple clinic codes. As in
the 100th child at XX clinic. That's why I want to include both fields in
the link.

This isn't "both fields" - this is ONE field containing two logical parts, a
text clinic code and a numeric child code.

You would do better splitting this field in two, making the Primary Key of
your table consist of a text ClinicCode and a numeric ChildID. Neither field
would be unique itself, but the combination would; they could then be
concatenated for display purposes. You could have a table with all the
two-letter clinic codes (and the clinic or location name), and use a Combo Box
to *select* the clinic rather than trusting the user's perfection in typing.

So yes... you're absolutely on the right track. What specific problem are you
having doing so?


btw I read the article in last week's _Nature_ (or was it _Science_??) about
this program; I'm VERY impressed and would be delighted to help if you would
like to contact me offline at jvinson <at> wysard of info <dot> com (edit out
the blanks and fix the punctuation).


I would suggest splitting