VBA function: Selecting and returning a field based on a price compare between to obj

[zylosan]

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.

[June7]

Comparison calc shouldn't be difficult but not sure what you mean by 'links the inventory to the assembly'. Don't know the data structure well enough.

Int() function will drop the remainder.

Price3Inch: 117.96 / Int(3 / 1.25)

Price4Inch: 134 / Int(4 / 1.25)

[orange]

I don't understand your business either.

I'm not clear on your wording/meaning of this

I have split both the inventory and assembly items product codes into three fields, prefix(product type), middle (product thickness), and suffix (Color Code).

but recommend that you use atomic fields (1 field 1 fact)
Seems to me you have a Product. That product can be represented by some ProductCode, the product has a thickness and also a Color. These are attributes of Product and would be separate fields in the Product table. If you want to concatenate these for some codification that makes sense to you and your business so be it. But, keep individual attributes in your tables.

I recommend you watch the videos at https://www.accessforums.net/databas...e-40206.html#6