So, I have an access database that I use to generate our price lists for various product which are defined as Inventory items in our ERP system. We also have objects called assembly items in the ERP system. Assembly Items are not a child or derived object of the Inventory items, despite the fact that each assembly is a single bun (inventory item) cut to a specific thickness. This is a cluster needless to say. Currently I am attempting to auto calculate the assembly item prices based on my pricing data base that has the prices for all of the inventory items.
I have split both the inventory and assembly items product codes into three fields, prefix(product type), middle (product thickness), and suffix (Color Code). This has allowed me to create a relationship between the inventory item and the assembly item by removing the middle vale from each string and build a query based on it.
The problem I am having is that our products (giant sheets of foam) comes in two default sizes, 3" and 4" each of which has a different price. And for even more fun the price levels are not linearly related to each other. I need a way to check the bun yield price for each assembly item and compare them between the two default thickness and than return the lowest price per sheet. Bun yield is (stock sheet / assembly thickness) drop remainder, than compare to other stock thickness. And by return I mean have it calculated in a query that links the inventory to the assembly items.
EXAMPLE
Inventory Item: Foam-3"-Charcoal = 117.96
Foam-4"-Charcoal = 134.00
Assembly Items: Foam-1.25"-Charcoal = (3" / 1.25") = 2.4, Floor(2.4) = 2, 117.96/2 = 58.98 per sheet
Foam-1.25"-Charcoal = (4" / 1.25") = 3.2, floor(3.2) = 3, 134.00/3 = 44.66 per sheet
return = 44.66 per sheet.