Establishing a many to many relationship and writing data to a “bridge” table

[melbushman]

Hello!

I have been tasked to build a database that is way over my head. My apologies if I am not posting this in the correct forum

I am trying to make a database that holds contract information. For every vendor there can either be a Master Contract with linked sub-contracts, or a vendor can simply have a sub-contract. I have created a “bridge” table that contains VendorID, ContractID and MSAContractID (Master Contract ID). If a contract is selected as a Master Contract (lookup value 6), how do I force access to write the VendorID, ContractID and MSAContractID to the bridge table? If a sub-contract is created, how should I deal with the fact that there is not a Master Contract?


I have three tables that I am trying to work with:

tblVendor (VendorID PK)
tblContract (ContractID PK, VendorID FK)
tblContractBridge (BridgeID PK, ContractID FK, VendorID FK, MSAContractID FK)

I want to make a form that displays the vendor with the linked Master Contracts and the sub-contracts that may or may not be linked to a Master Contract.

Does anyone have anyone have any advice on how to set this up? Any advice on how to write the appropriate IDs to the “bridge” table?

Thank you for your time!

 

[KenHigg]

Can a vendor only have one master contract?

 

[melbushman]

No, a vendor can have more than one master contract. Each Master Contract can have several sub-contracts. But, a vendor doesn't have to have a Master Contract at all, it can only have only sub-contracts.

It's a mess!

 

[KenHigg]

Ok, so does a master contract apply to only one vendor or can multiple vendors work under the same master contract?

 

[melbushman]

The master contract applies to only one vendor.

 

[KenHigg]

Do sub-contracts only apply to one vendor?

 

[KenHigg]

See if this would work

 

[melbushman]

I will try that, thank you!