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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|