Intermediate Relationships - What are they?

[sanders720]

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]

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]

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.

 

[JimHorton]

sanders,
An intermediate relationship is where neither field (or set of fields) in the relationship is unique. What is probably the case here is that Customer ID in the customer table is not a primary key or is not Unique (it needn't be the PK, but it must be unique). An intermediate relationship is essentially a many-to-many, which is not very useful in most cases; consider it meaningless for all intents and purposes--basically with this relationship, Access will automatically create a join when both tables happen to be put together in a query. Big deal. It's more of an informational link than a practical one.

However you can't enforce ref. integrity on linked tables, so you can't update the *key* in the customer table from one db and have the info in the related tables (the Many side) in the other db be updated (cascaded). BUT--this should not matter in most cases because you rarely change the KEY (customerid). If you update the name, address, or whatever, the other db will all access that updated info from the same source, as long as the key hasn't changed--and again--this is rare that you would be changing Cust. ID's. If it is common in your app ot change the CustomerID of an existing customer with related records, then you may need to rethink the logic in the app.

So the bottom line is that if you make the Customerid the key in it's .mdb, then you should see the relationship change to One-to-many--you shouldn't even need to recreate it, Access will pick up on the fact that the linked table has a unique key now.

Now, for the other tables...for the inquiryInfo to InquiryDate relationship. (assuming the 'One' side is InquiryInfo) If you have the key of InquiryInfo being compound--Customerid+InquiryNo, then InquiryDate table should also include Customerid (I recommend against this compound method, but to make this work with what you have, this will do just fine) as well as InquiryNo in that table. Then make sure in the 'One' side of this relationhsip, the InquiryInfo, is unique, and drag any of the key fields from InquiryInfo to InquiryDate. Then in the comboboxes of the relationship editor, select the Customerid field in each table as well. Now, you should get the "One-to-Many" relationship, and if these are in the same .mdb, the "Enforce Referential Integerity" box should be available.

You should create a compound index in INquiryDate on the foreign key for performance---don't just index both fields, this does nothing--go into the index editor and name an index and add both fields.
--Jim

 

[sanders720]

Okay, I got the one to many relationship, but it is going the wrong way. I want the one on the InquiryInfo Table and the Many on the InquiryDate table.

I also do not follow how to create a compound index. I went into the editor, but it is not very interactive. Where is there an option to index more than one field by an index name - and where is the index name referred to in the table design?

Thanks again...

 

[JimHorton]

sanders,
OK, for the InquiryInfo, the PrimaryKey should be CustomerID AND InquiryNo.

Then for InquiryDate the Key should be CustomerID AND InquiryNo AND:
Either InquiryDate if you'll only have one inquiry in this table for each date,
OR
InquirySequence--a new field you create that is just a number 1,2,3, etc, based on which record for that particular Cust/Inq#
..either way there will be 3 fields making up the PK of the INquiryDAte table.

Now, for the Compound index, in the index editor (the lighning bolt icon in table design). You are right, it is not a very intuitive interface. The Index Name is in the left column, and to make a compound index, enter a Name, then tab to the Field, and add a field, then just add another field directly below but *leave the Name blank*. Access will just assume that any fields entered directly below a field, are part of that same index.

Now, let's assume the InquiryDate (many side) has 3 fields as key:
CustomerID
InquiryNo
InquirySeq (you could use autonumber for this but I highly discourage Autonumber for *anything*)

Now, getting into some nerdy nitty-gritty with Query Execution plans, (and this is partly why I discourage the compund keys where possible)...you can ignore this if you like, it's just some extra low-level info which usually wont' amount to a hill of beans in this crazy world...
...when you join the InquiryInfo to InquiryDate using CustomerID and InquiryNo, if you have criteria on the Many side Access will execute this is to *first* read the Many table, then for each record there Access will fetch the One side using the One sides PK...
BUT...
...if you have criteria on the One side, then Access will change it's plan and now scan the One table first, but now there is no matching index--so access will use the Many table's PK, which index that Access will use will be the PK, but this is not as efficient as it would be if the first 2 components were indexed as well (in a compound index)--which Access will use if it exists, you can use SHOWPLAN to verify this.

And if you have no criteria, Access will use a Merge Join on the matching indexes, which is going to be faster than a regular join.

This is almost splitting hairs, however, since the performance difference usually will be minimal...If the compound index (aside from the PK) on the Many side doesn't exist, then Access scans the PK of the Many side (which is slightly larger), but it must do extra logic and look at the first 2 fields of the pk to match with the 2 key fields of the One table, whereas if it used a separate compound index on the first 2 fields of the Many side, it would just match the full index value with the PK on the One side.
--Jim

 

[sanders720]

Can I email you this thing. Now that I've implemented the things you've mentioned, I'm back to Intermediate Relationships.

 

[JimHorton]

sanders,
Sure...email it to jimojimo@hotmail.com (you'll probably need to compact it then zip it because hotmail has a 1 meg limit)
--Jim