Multiple field line relationship in access

[Wabamdo]

Why and where do we create a multiple field relationship in Microsoft access



The relationship between CompanyContacts and CompanyProducts.(Adopted from Microsoft access 2013 inside out by John Viescas)
What was the main purpose of using such a relationship here and where are such relationships frequently created

[ItsMe]

Looks like a basic Many to Many relationship managed by a Compound Key in a Junction table.

[June7]

I avoid compound keys as much as possible. Have only used once.

Looks like designer was trying to accomplish:

1. set a primary key in CompanyContacts

2. prevent duplicate pairs of CompanyID and ContactID

Both goals can be accomplished with an autonumber field in CompanyContacts as primary key (saved as foreign key in ContactProducts) and setting a compound index on CompanyID and ContactID fields in CompanyContacts. Then ContactProducts could have a compound index on CompanyContactID and ProductID

[orange]

I'm not sure I'm following the model.
In table Contacts there is a field Company?
What does a record in Contacts mean?? Are all Contacts company contacts? If so, then why the CompanyContacts table?
ContactEvents make little sense--where is the Event?
I think the model is missing a table Category (based on Category description in the Products table)
I don't believe that this model came from John Viescas. It may have started there, but there are too many inconsistencies/issues in my view.

We really should have an English description of the business to make any sense or comments on the appropriateness of the model.
It would also be helpful if the OP would tell us what was the original and what was the adaptation.

[ItsMe]

I looked in the book and it looks like it matches the Relationships Window for the Contact Tracking database. I think I see where the OP is confused. There is not much explanation why there is a Composite Key used in CompanyContacts. The book just says, "look at the linking lines". I am sure there are other explanations about it in the book. Not sure where though. I just use the book as reference material.

I think the last Access Inside Out John Viescas authored was 2007 (co-authored). Although, he is mentioned in 2010 and 2013 as providing support.

[orange]

@ItsMe,

Do you concur with the observations I made? Hopefully there is a business description with which to vet that model, since it seems to have many shortcomings. Again, the business may have these requirements, but it just seems so incomplete...

[ItsMe]

@orange

When I look at the model I think the Company field in the Contacts table is not necessary and I do not know why it is there. I never downloaded the example DB so I do not know what sort of "Real World" scenarios it is supposed to manage. Maybe it is not supposed to manage any real world scenarios and simply be there to act as an example for features within Access.

As I look at the model more, it seems that there are two junction tables. So it seems fine to me (aside from the fact that I don't enforce Constraints and Referential integrity via the relationships window). I would just want to know what the reason for the Company field is.

[orange]

Hmmm.. It may just be a teaching aid with no real world business intended or implied.
When I look at ContactProducts -- seems you need to know Contact to get CompanyProduct.
There are 2 junction tables ContactProducts and CompanyContacts, but these are not realistic.
ContactProducts has a DateSold and SoldPrice, but no Quantity.

I find it confusing at best.

For the original OP wabamdo, "multiple field lines" represent joins between multiple fields in respective tables.

There are instances of junction tables where the designer decides to not use the PK from each of the related tables and the PK of the junction table. He/she uses an autonumber (surrogate) PK and uses that to form relationships between other tables. However, he/she may make a unique composite index from those original PKs to prevent duplication.
You could join tables using the autonumber PK, or you could use the fields making making up the composite index. A 2 field composite index joined to another table would have 2 "field lines" in your terms.