Two tables one data entry problem (1 Viewer)

[Bobp3114]

I have this situation: one table holds all the contact people for Manufacturers [tblSuppliers], another table holds the contacts for Distributors [tblDistributors]. The problem is that a few companies are both Manufacturers of products and Distributors of other company's products. I need to ensue that when a user enters a Supplier's new contact person's details (eg first name, surname, mobile etc) these details are saved to the Distributors table as well if that company (Supplier) is also a Distributor. Pardon the fact that what originally was called supplier is now manufacturer..database evolving to boss's needs!
Thanks in advance
Bob

 

[CJ_London]

why not just have the one table with a field to indicate whether the record is for a manufacturer, a distributor or both?

 

[Neros]

I have this situation: one table holds all the contact people for Manufacturers [tblSuppliers], another table holds the contacts for Distributors [tblDistributors]. The problem is that a few companies are both Manufacturers of products and Distributors of other company's products. I need to ensue that when a user enters a Supplier's new contact person's details (eg first name, surname, mobile etc) these details are saved to the Distributors table as well if that company (Supplier) is also a Distributor. Pardon the fact that what originally was called supplier is now manufacturer..database evolving to boss's needs!
Thanks in advance
Bob

Hello, Bob.

To ensure that both tables will be filled with the same information, we need a field that must contain the same information (like a company legal register or tax ID). Then we can make a trigger in tblSuppliers to update tblDistributors when the first is updated. Of course we cant use the id field because - as you have stated - only a few suppliers are distributors.

Besides, I'd like to extend CJ_London good suggestion. If it's possible, you could bring all distributor's data on tblSuppliers and create a table like "tblType" that can contain a role field (eg. "Manufacturer", "Broker", "Distributor") and with this 2 tables you can create a query to select all supplier by type (qryManufacturers and qryDistributors). This way you can make sure to have only one record by company.

 

[Bobp3114]

why not just have the one table with a field to indicate whether the record is for a manufacturer, a distributor or both?

Thanks Mate, working on it
Bob

 

[Bobp3114]

Hello, Bob.

To ensure that both tables will be filled with the same information, we need a field that must contain the same information (like a company legal register or tax ID). Then we can make a trigger in tblSuppliers to update tblDistributors when the first is updated. Of course we cant use the id field because - as you have stated - only a few suppliers are distributors.

Besides, I'd like to extend CJ_London good suggestion. If it's possible, you could bring all distributor's data on tblSuppliers and create a table like "tblType" that can contain a role field (eg. "Manufacturer", "Broker", "Distributor") and with this 2 tables you can create a query to select all supplier by type (qryManufacturers and qryDistributors). This way you can make sure to have only one record by company.

Thanks for the help...at least i can see a way forward
Bob