Setup Companies with multiple contacts but now also need contacts to link to multiple companies

[kgoepel]

We have a database where the Company Data has a Company ID that links up to Contact Data by each contact record containing the Company ID. This has been fine up to now where I am faced with an issue where I have contacts who need to link up with more than one company. I don't want to have 2 records for the same person so I though I could maybe create a separate table that contains just and Contact ID and Company ID and edit the relationships to use the new table to link the two.



At the moment it works nicely in the Company Data Form where I have a page tab with contact subform to add contacts and it automatically gives the contact a new reference and automatically fills in the correct Company ID and ideally I would like to not lose this so I thought I need to keep autonumber for contact ID on the new separate table (with just Contact ID and Company ID). I have tried this and everything is still working apart from the part where I add multiple company ID's to the contact record in my separate contact form. I thought adding a page tab to this form with a subform for the new table (just Contact ID and Company ID) would work, it allows you to add a new record but automatically allocates a new contact ID rather than picking up the contact ID from the record your in. How do I make this allow a new record and keep the same contact ID?

[ranman256]

contacts can have many companies....and vice versa

tPeople table
PersonID (auto)
name,etc

tCo table
CoID (auto)

tContacts table
PersonID (long)
CoID (long)

[orange]

This is the sort of design you will need since a Contact could be associated with more than 1 company.

Good luck.
(ignore the mis-spelling Conatact)

[kgoepel]

Thank you so much for your help. I hope you have a great weekend!