Hi Tim,

The MemberContacts table and LotOwned tables are not needed based on your description below therefore you only need the 3 tables: This is because you do not require a many-to-many relationship on them.

Contact (PK contactID, FK membershipID, field contactName)
Membership (PK membershipID, field membershipName)
Lot (PK lotID, FK membershipID, field lotName)

The contact and lot tables will have a foreign key for the Membership table. The foreign key on the contact table will allow null so that a contact does not have to belong to a Membership. The foreign key on the Lot table will not allow null as it appears that a Lot must belong to one and only one Membership.

To list contact names, do the following:

SELECT     Contact.ContactName, Membership.MembershipName, Lot.LotName
FROM         Contact INNER JOIN
                      Membership ON Contact.MembershipID = Membership.MembershipID INNER JOIN
                      Lot ON Membership.MembershipID = Lot.MembershipID

>I have a complex join and am over my head. I am hoping to get some advice on how to do this. Let me explain.
>
>I have 5 tables involved.
>Contact - Contains people names
>Membership - Represents a membership in an organization
>MemberContacts - is a linking table for the above
>Membership can have many Contacts but Contacts can have only one Membership. Problem is the Contact does not have to be in Membership
>Lot - represents all the lots in a given subdivision
>LotOwned - is linking table to link the lots to membership
>A lot can have only one membership, but Membership can have many lots.
>
> Lot LotOwned Membership MemberContacts Contact
>Lot.Lot_Id --> LotOwned.Lot_Id
> LotOwned.Member_Id --> Membership.Member_Id
> Membership.Member_Id -->MemberContacts.Member_Id
> MemberContacts.Contact_Id --> Contact.Contact_Id
>What I want is to have a list of Contact Names for each lot. so Lot is the parent and I need Contact as a child table.
>
>A single record in Lot should get me a single record in LotOwned which should get me only a single record in Membership but should get me one or more records in MemberContacts with each a corresponding record in Contact.
>Thanks for any help or advice.
>Tim