Relating multiple fields in table to multiple fields in another ta

[RandallMJ]

I am trying to relate two tables together by 4 fields in each table as follows:
Table 1: Contract No., Project No., Supplement No. & Task No.
Table 2: Contract No., Project No., Supplement No. & Task No.
It works fine when I relate only 3 of the fields but when I relate 4 I get
an error message. Is 3 the limit? I need to pull data from Table 2 based on
all 4 relationships.

 

[John Vinson]

I am trying to relate two tables together by 4 fields in each table as follows:
Table 1: Contract No., Project No., Supplement No. & Task No.
Table 2: Contract No., Project No., Supplement No. & Task No.
It works fine when I relate only 3 of the fields but when I relate 4 I get
an error message. Is 3 the limit? I need to pull data from Table 2 based on
all 4 relationships.

The limit is documented at ten fields. How are you "relating" them?
What is the actual error message, and under what circumstances do you
get it? Do your fieldnames contain blanks and periods (unwise) or are
you using ContractNo, ProjectNo etc. as fieldnames?

Just for simplicity, it may be preferable to add a unique four-field
Index in Table1, and a (formally redundant) Autonumber primary key
field; you can then put a Long Integer field in Table2, link using it,
and retrieve the four numbers by joining to Table1. This is a common
alternative to the multifield join but as I say is not a *necessary*
getaround - multifield joins do work, they're just more of a hassle
sometimes!

John W. Vinson[MVP]

 

[TC]

Care to say what the message /is/ ?

TC [MVP Access]

 

[RandallMJ]

My fields are: EC_No, JP_No, SupplementNo and TO_No with no blanks or
periods. I have these fields in both a Details table and a Cost table which
are joined by all four fields. I have created a form with the Details
information and want to add a subform from the Cost table to show each record
when the four fields contain the same data. I am using the Subform Wizard and
receive the following message: Subform Wizard: "Subscript out of range" when
I try to add the subform whether it's from an existing form or table. If I
change the table relationship to just 3 fields it works fine but the records
do not appear as I want them.