Show Related Item on Form

[crawfish124]

Right now I have a database set up in the following manner:



Franchise - List of all properties of a Franchise
Contact - List of a point of contact for that Franchise
Relation - Relationship between contact and Franchise (a Franchise can have many contacts and a contact can be a contact for many Franchise)

I have a form (created off of the Contact Table) that displays all of the information for the contact, for example their first and last name, website, phone number, etc. I am looking to also include all of the Franchise names that the specific person is a contact for. I am having trouble connecting this information because the properties are not on same table (contact information is in one table and the Franchise information is on another). I assume I have to use some sort of join using the Relation Table to allow the cross of information, but I am not sure.

Please let me know if there are any pointers I can have to help get this done.

[June7]

What is purpose the Contact form - data entry to Contact table? To create new record in Relation that associates Contact with another Franchise? Both? Depending on what you want the form to do, options:

1. form bound to Contact table with a subform bound to Relation with a combobox to select franchise

2. form bound to Contact table with a listbox that has a RowSource joining Relation and Franchise and has filter parameter dependent on the Contact ID.