Hi, I have been working on a database that has 3 major sections. Reporting, Customers and Orders.
I need to let my end user have a list of the previous products a customer has been ordered.
I had no problem creating this overview with a portal in the customers section, as the portal points to the Order table and all is well.
Now for clarity I need to have this same order history show up on my ordering page... This is the problem.
I realize I need to start working with line items to make this happen.
I have the following Tables: Customers, ProductTracker, and ProductLineItems
The new table, ProductLineItems, has the following fields __FKOrderID as well as __FKCustomerID
My relationships are listed for the tables below Prettty much in this order Customers-LineItems-Product
Customers:_PKCustomerID = ProductLineItems:__FKCustomerID
ProductLineItems:__FKOrderID = ProductTracker:_PKOrderID
For some reason I have also drawn a second instance of the customers table Called Customers2Products
Customers2Products:PKCustomerID = ProductTracker:_fkCustomerID - Is this instance nesscary?
How can i use the same layout (product tracker) to create an overview for my orders.
If i use these relationships assuming they are correct. I need to create a record in the Line item table, which I would like to reflect the info which is entered on the ProductTracker table. Users will be using the Track a produt layout and I dont want them to have to leave this layout. My concern is if i get creative and make scripts to brute force values into my Lineitem table im not really setting up the database right and if a user simply clicks the new record button in the ProductionTracker (or product tracker) my coded relationship will be broken and a new record wont be created in the line items.