A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Establish relationship between tables using TWO fields



 
 
Thread Tools Display Modes
  #1  
Old October 29th, 2008, 03:54 AM posted to microsoft.public.access.tablesdbdesign
Ann Scharpf
external usenet poster
 
Posts: 126
Default Establish relationship between tables using TWO fields

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.
--
Ann Scharpf
  #2  
Old October 29th, 2008, 05:47 AM posted to microsoft.public.access.tablesdbdesign
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Establish relationship between tables using TWO fields

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" wrote in message
...
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.
--
Ann Scharpf



  #3  
Old October 29th, 2008, 05:56 AM posted to microsoft.public.access.tablesdbdesign
Ann Scharpf
external usenet poster
 
Posts: 126
Default Establish relationship between tables using TWO fields

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


"Jeanette Cunningham" wrote:

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" wrote in message
...
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.
--
Ann Scharpf




  #4  
Old October 29th, 2008, 05:58 AM posted to microsoft.public.access.tablesdbdesign
Ann Scharpf
external usenet poster
 
Posts: 126
Default Establish relationship between tables using TWO fields

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.
--
Ann Scharpf


"Jeanette Cunningham" wrote:

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" wrote in message
...
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.
--
Ann Scharpf




  #5  
Old October 29th, 2008, 05:59 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Establish relationship between tables using TWO fields

On Tue, 28 Oct 2008 19:54:06 -0700, Ann Scharpf
wrote:

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).
--

John W. Vinson [MVP]
  #6  
Old October 29th, 2008, 06:15 AM posted to microsoft.public.access.tablesdbdesign
Ann Scharpf
external usenet poster
 
Posts: 126
Default Establish relationship between tables using TWO fields


"John W. Vinson" wrote:


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).
--

John W. Vinson [MVP]



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


  #7  
Old October 29th, 2008, 06:21 AM posted to microsoft.public.access.tablesdbdesign
Ann Scharpf
external usenet poster
 
Posts: 126
Default Establish relationship between tables using TWO fields

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.
--
Ann Scharpf


"John W. Vinson" wrote:


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).
--

John W. Vinson [MVP]

  #8  
Old October 29th, 2008, 07:06 AM posted to microsoft.public.access.tablesdbdesign
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Establish relationship between tables using TWO fields

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

"Ann Scharpf" wrote in message
...
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.
--
Ann Scharpf


"John W. Vinson" wrote:


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).
--

John W. Vinson [MVP]




  #9  
Old October 29th, 2008, 09:45 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Establish relationship between tables using TWO fields

On Tue, 28 Oct 2008 22:15:01 -0700, Ann Scharpf
wrote:


"John W. Vinson" wrote:


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).
--

John W. Vinson [MVP]



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

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.