Relating Suppliers to Customers through contracts

I have a table design problem regarding contracts that I'm trying to track.
Each contract may have multiple suppliers and multiple customers. Thus, I've
set up a table for suppliers, customers and contracts. I am supposed to send
a notice to each customer listing the suppliers on the contract. Throughout
the year it is common to have the same suppliers and customers as parties to
mulitple contracts. As you can see, it is the contract that links the parties
together.
Is this a simple one-to-many between tblContracts and tblCustomers and
another one-to-many between tblContracts and tblSuppliers, or am I looking at
creating a couple of many-to-many tables?

What you have is fine, but it might be simpler if you combined the customers
and suppliers into one table of 'clients.'

You could then create a table with fields:
ContractID relates to the p.k. of your contract table.
ClientID relates to the p.k. of your client table.
RoleID relates to the p.k. of a role table.

The little Role table would have 2 records, for Customer and Supplier. As
you can see, you can now identify multiple companies with one contract,
specifying them as customers or suppliers in one table. Further, when
someone else dreams up another way that clients could be related to the
contract, you can do that just by adding other roles.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"CDM" wrote in message
...
I have a table design problem regarding contracts that I'm trying to track.
Each contract may have multiple suppliers and multiple customers. Thus,
I've
set up a table for suppliers, customers and contracts. I am supposed to
send
a notice to each customer listing the suppliers on the contract.
Throughout
the year it is common to have the same suppliers and customers as parties
to
mulitple contracts. As you can see, it is the contract that links the
parties
together.
Is this a simple one-to-many between tblContracts and tblCustomers and
another one-to-many between tblContracts and tblSuppliers, or am I looking
at
creating a couple of many-to-many tables?

If I set up a role table, when notifying customers would I create a recordset
on clients/contracts with a where clause for role=customer and loop through
another recordset creating notices for related client/contracts whose role
was supplier?

"Allen Browne" wrote:

What you have is fine, but it might be simpler if you combined the customers
and suppliers into one table of 'clients.'

You could then create a table with fields:
ContractID relates to the p.k. of your contract table.
ClientID relates to the p.k. of your client table.
RoleID relates to the p.k. of a role table.

The little Role table would have 2 records, for Customer and Supplier. As
you can see, you can now identify multiple companies with one contract,
specifying them as customers or suppliers in one table. Further, when
someone else dreams up another way that clients could be related to the
contract, you can do that just by adding other roles.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"CDM" wrote in message
...
I have a table design problem regarding contracts that I'm trying to track.
Each contract may have multiple suppliers and multiple customers. Thus,
I've
set up a table for suppliers, customers and contracts. I am supposed to
send
a notice to each customer listing the suppliers on the contract.
Throughout
the year it is common to have the same suppliers and customers as parties
to
mulitple contracts. As you can see, it is the contract that links the
parties
together.
Is this a simple one-to-many between tblContracts and tblCustomers and
another one-to-many between tblContracts and tblSuppliers, or am I looking
at
creating a couple of many-to-many tables?

You could do that if you want 2 separate lists.

Or you could combine them into one list if that suits your postage costs
better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CDM" wrote in message
...
If I set up a role table, when notifying customers would I create a
recordset
on clients/contracts with a where clause for role=customer and loop
through
another recordset creating notices for related client/contracts whose role
was supplier?

"Allen Browne" wrote:

What you have is fine, but it might be simpler if you combined the
customers
and suppliers into one table of 'clients.'

You could then create a table with fields:
ContractID relates to the p.k. of your contract table.
ClientID relates to the p.k. of your client table.
RoleID relates to the p.k. of a role table.

The little Role table would have 2 records, for Customer and Supplier. As
you can see, you can now identify multiple companies with one contract,
specifying them as customers or suppliers in one table. Further, when
someone else dreams up another way that clients could be related to the
contract, you can do that just by adding other roles.

Many thanks!

"Allen Browne" wrote:

You could do that if you want 2 separate lists.

Or you could combine them into one list if that suits your postage costs
better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CDM" wrote in message
...
If I set up a role table, when notifying customers would I create a
recordset
on clients/contracts with a where clause for role=customer and loop
through
another recordset creating notices for related client/contracts whose role
was supplier?

"Allen Browne" wrote:

What you have is fine, but it might be simpler if you combined the
customers
and suppliers into one table of 'clients.'

You could then create a table with fields:
ContractID relates to the p.k. of your contract table.
ClientID relates to the p.k. of your client table.
RoleID relates to the p.k. of a role table.

The little Role table would have 2 records, for Customer and Supplier. As
you can see, you can now identify multiple companies with one contract,
specifying them as customers or suppliers in one table. Further, when
someone else dreams up another way that clients could be related to the
contract, you can do that just by adding other roles.