I'm attempting to build a database to streamline paperwork in the coming year.
Items are purchased together on a Product Order. Each Product Order can have multiple Lots with a Lot Price and each lot can have multiple products. (The price of the products is split at our own discretion from the price of the lot; this product amount is more important than the lot amount but the sum of multiple products in a lot must equal the amount of the lot with rounding rules). All lots purchased are new as well as all products (products and lots are unique and cannot be re-ordered). Each purchase order can have one lot or many hundreds of lots; each lot may be one product or hundreds of products.
Our structure is:
tbProductOrder (Main Table)
tbOrder Details (tbProductOrderFK)
tbLot Details (tbOrderDetailFK)
tbProduct Details (tbLotDetailFK)
I figured out how to put together a Product Order with Order Details but am getting lost on how to add Lot Details to the Order Details with relationships like nesting dolls.
The goal is to have a PurchaseOrder Form with a Tab for inputting the PurchaseOrder Details, with two stacked sub-forms in datasheet view (one to input the Purchase Details & one to input the related Lot Details), and then a final Tab to input the financials and display the associated financial queries.
My guess as how this is accomplished are two subforms (Purchase Details & LotDetails) with master/child linking the Purchase Detail ID’s. Then, these stacked on a third subform where if the purchasedetailID is pressed the associated LotDetails open below (the master of this form linked to ProductOrder of the main form). This could be way off (maybe this is why it’s not working).
Attached is a test database to illustrate what’s being attempted. PO Streamlined.zip