Table Relationship Using A Multiple-field Primary Key

[Jim Watkins]

I wish to define a one-to-many relationship between two
tables. The foreign key is in a single field, but in the
other table, the matching primary key is in two distinct
fields. In the Relationship window I finally got
the "picture" to look as if it were correct (I selected
both fields as the first step; the end result is two
lines -- one from each field in the multiple-field key --
that merge together at the single foreign key field of the
other table. But is this relationship going to FUNCTION
CORRECTLY in a join?

 

[Douglas J. Steele]

I don't quite understand the setup. How can it be only one field in one
table, but two in the other? Is it two separate values concatenated in the
first table? If so, change it to two separate fields.

I don't believe it'll work otherwise.

 

[John Vinson]

I wish to define a one-to-many relationship between two
tables. The foreign key is in a single field, but in the
other table, the matching primary key is in two distinct
fields. In the Relationship window I finally got
the "picture" to look as if it were correct (I selected
both fields as the first step; the end result is two
lines -- one from each field in the multiple-field key --
that merge together at the single foreign key field of the
other table. But is this relationship going to FUNCTION
CORRECTLY in a join?

No, it will not.

The Foreign Key must consist of the same number of fields, of matching
datatypes and sizes, as the Primary Key to which it is linked. Note
that a PK can consist of up to ten fields - if you're concatenating
two pieces of data into a single field in order to get a one-field PK,
don't!