Relating various tables to use in portals
I have a database containing six tables. 1. Suppliers 2. Order; 3. Products; 4. Assortments; 5. ProductLineItems; 6. AssortmentLineItems.
I have created the following key fields,
Suppliers Table:- __pkSupplierID;
Orders Table:- __pkOrderID, _fkSupplierID, _fkProductID, _fkAssortmentID,
Products Table:- __pkProductID and _fkSupplierID;
Assortments Table:- __pkAssortmentID and _fkProductID;
ProductLineItems Table:- _fkOrderID, _fkProductID and _fkAssortmentID
The Products table needs to be related to the Suppliers Table so as to capture only the products from a particular Supplier and show
them in a Portal.
The Assortments Table needs to be related to the Products Table so as to capture only the Assortments relating to a particular product and show them in a Portal.
How do I create the field options, table relationships and properties of each relationship so that I don’t capture Products from all
Suppliers or Assortments from all Products?
How do I use the above so that the Portals for Products and Assortments work correctly?
I do hope someone can help me with this. I’ve been spending weeks searching for the solution without success.