WHAT IS AN INTERMEDIATE RELATIONSHIP?

[sanders720]

sanders720 (Programmer)
What is an intermediate relationship? I have two tables

tblInquiryInfo - the first two fields are keyed:
CustomerID; InquiryNo; InquiryDate; InquiryDesc; PotentialYes; PotentialNo; Probable; Inactive

tblInquiryDate - the first field is keyed
AutoNum; InquiryNo; InquiryDate; Comments

When I drag InquiryNo from the first table to the second, I get an Intermediate relationship. What I really want is a one to many relationship.

Help with this is greatly appreciated.
------------------------------------------------------------
JimHorton (Programmer)
For a one-to-many relationship, you need to have one of the fields be a unique field, usually this will be a Primary Key. the other field will not be unique, but it's good practice to at least index it.

In your tables, the unique field should be InquiryID in the first table, and Customerid should be a foreign key to the customers table (not shown in your example--I'm just mentioning it on a tangent). Now you can link to the inq. date table.

However it looks like the first table may be set up with a compound key--Cust id and inq.#, which is ok, but then you need to put both custid and inq# in the second table, and make the link on both those fields. This is ok, but I'd recommend the Inquiry # in the first table be unique across ALL inquiries--not just with that customer id.

Either one of the above will work however--the unique INq#, or the combination of Custid and inq#.
--Jim
------------------------------------------------------------
sanders720 (Programmer)
There are actually going to be 5 tables, but I am trying to get three working first.

Customer is LINKED from another database, where customer is keyed. The fields are...
CustomerID
Customer
Street
Adress II
City
State
Zip

The other two tabes are mentioned previously.

I want to affect the linked table as far as updating customer / potential customer information for that application, as well as this one. In other words, the data is entered once, in either place. The other two tables define this application. I added CustomerID to the second table and still got an Intermediate Relationship. WHAT IS AN INTERMEDIATE RELATIONSHIP ANYHOW? And does it mean the relationship does not work? I simply want to track multiple contact information to the inquiry, and add the customer to the linked table for use in the other application as well.

Again, your help is appreciated.

 

[RickSpr]

Do you mean an "Indeterminate" relationship? That's what you should be getting in this case.

An indeterminate relationship happens when the field (or fields) it's based on are not uniquely indexed in either of the tables. If you create a unique index (it doesn't have to be the primary key) on InquiryNo in the table where it should be unique, and then create the relationship, it will be one-to-many. (If there is a unique index in both tables, the relationship is one-to-one.)

If, in fact, InquiryNo is not unique in either of the tables, then your expectation is wrong. For example, if you have a given InquiryNo twice in the first table, and three times in the second table, there isn't any way to tell, by looking at the data, which record in the first table goes with which record in the second. That's why the relationship is "indeterminate". Alternatively, you could conceive it that both records in the first table go with all three records in the second, but that's a many-to-many relationship, which relational database systems (including Access, SQL Server, etc.) don't support directly. (You can represent them by using an intermediate table, however.)

BTW, "indeterminate" relationships also occur if one or both of the tables is linked from another database. That's because Access can't know whether the unique indexes will still exist at the time it actually opens the table. You could, for example, create the relationship in the linking database, then go into the back end database and delete the index, making the relationship incorrect. In such cases, you should define the relationship in the database that holds both real tables, and let the linking database inherit it. (If both tables aren't in the same database, there is no choice; the relationship can only be indeterminate.) Rick Sprague