Help with relating multiple records...

[B.Gordon]

I am having a mental block or maybe I just don't know how to do this...

I made a simple contact management database and included it for yout parusal. This DB is just to help me understand how to do this.

I have a contact record (will be used to record anonymous crimestopper tips). In another table, I store names. In the last table I store addresses.

My problem is that I need to link names and/or addresses to the contact record. The same name and/or addresses will likely be linked to multiple contact records over time. In addition, there could be several names or addresses that need to be linked to the contact record.

I am unsure how to make the relationship work and display the linked data (fields or portals) on the contact record. Seems easy enough in concept but I am stuck. I can get 1 name and 1 address, but when I add more, I need a new field to hold the new id or some portal magic that is beyond me. Somehow I need to store more that one PersonRecordID and AddressRecordID in each contact record. I don' t know how many I might need, but could be 10 or 15...

Please see the attached file.

Thanks gurus. The solution to this will apply to several databases I am trying to make.

contact_history.zip

[mr_vodka]

You will need a many to many join table that sits between your contact and people. You will also need one to do the same for addresses. It should store the ContactID and PeopleID.

There are many examples on here regarding many to many join tables on here that you can download and pick apart.