View Single Post
  #3  
Old October 29th, 2008, 04: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