HillTJ
To train a dog, first know more than the dog..
- Local time
- Yesterday, 21:43
- Joined
- Apr 1, 2019
- Messages
- 731
Hi,
I'm in the early stages of developing an inventory database for a production company I work for. At the moment records are retained on a bunch of excel spreadsheets!.
I wish to be able to place a Purchase order for multiple raw materials from a supplier (of which there are several) & have appropriate pricing against each line item.
I have this,
TBLRawMaterials
MaterialGrade - PK
Supplier - FK
TBLMaterialPricing
PriceID - PK
MaterialPricingLink - FK (MaterialGrade is linked to MaterialPricingLink)
Price_$
Valid_Till
TBLSupplier
SupplierID - PK & linked to TBLRawMaterials.Supplier
TBLPurchaseOrder
POID - PK
SupplierID - FK & linked to TBLSupplier.SupplierID
TBLPOItem (the sub sheet displaying the filteredd line items)
POLineID - PK
POID -FK & linked to TBLPurchaseOrder.POID
My intention is to purchase from an approved supplier & could have several lines of items per PO.
I have a FormPO with subform to display the PO line items, but I am unsuccessful in having the latest pricing appear beside each line item. On FormPO I filter for an "Approved" supplier & select a material in the sub form accordingly. That works!
Is my structure wrong? If not, how do I have the latest pricing appear beside each line item on the sub sheet?
I've received great support from the forum so far. Appreciate any advice.
Cheers Terry
I'm in the early stages of developing an inventory database for a production company I work for. At the moment records are retained on a bunch of excel spreadsheets!.
I wish to be able to place a Purchase order for multiple raw materials from a supplier (of which there are several) & have appropriate pricing against each line item.
I have this,
TBLRawMaterials
MaterialGrade - PK
Supplier - FK
TBLMaterialPricing
PriceID - PK
MaterialPricingLink - FK (MaterialGrade is linked to MaterialPricingLink)
Price_$
Valid_Till
TBLSupplier
SupplierID - PK & linked to TBLRawMaterials.Supplier
TBLPurchaseOrder
POID - PK
SupplierID - FK & linked to TBLSupplier.SupplierID
TBLPOItem (the sub sheet displaying the filteredd line items)
POLineID - PK
POID -FK & linked to TBLPurchaseOrder.POID
My intention is to purchase from an approved supplier & could have several lines of items per PO.
I have a FormPO with subform to display the PO line items, but I am unsuccessful in having the latest pricing appear beside each line item. On FormPO I filter for an "Approved" supplier & select a material in the sub form accordingly. That works!
Is my structure wrong? If not, how do I have the latest pricing appear beside each line item on the sub sheet?
I've received great support from the forum so far. Appreciate any advice.
Cheers Terry