Calculate a price based on unit of measure in a lookup field

Chad Ecklof 0 Reputation points
2023-01-21T12:12:06.88+00:00

I'm building a database to help with inventory and formula costing for my bakery. I have an ingredient table that I am using to input the ingredients we get from different vendors and calculate the unit cost and cost per ounce from the pack cost that we get each week.

I have a columns Pack Size, Unit Measure, Pack Cost, Unit Cost, and Ounce Cost. The Unit Measure column is a lookup that contains LB, OZ, GAL and EACH.

I am entering the Pack Size and Unit Measure and Pack Cost and I have Unit Cost as a calculated field that divides the Pack Cost by the Pack Size to get the cost per unit of each item. So, if a flour comes in a 50lb bag, then the unit cost would be the cost per pound. A 5 gallon bucket would be broken down to the cost per gallon.

The final column, Ounce Cost, is supposed to further divide down every Unit Cost to the cost per ounce.

I'm trying to figure out how to write the expression in the calculated field to use an IF, THEN, ELSEIF statement that checks the Unit Measure for its value and then divides the Unit Cost by the number of ounces in each Unit measure.

Example:

IF Unit Measure is equal to "LB" THEN divide Unit Cost by 16

ELSEIF Unit Measure is equal to "GAL" THEN divide Unit Cost by 128

ELSEIF Unit Measure is equal to "OZ" THEN make equal to Unit Cost

ELSEIF Unit Measure is equal to "EACH" THEN leave blank

I'm hoping this makes sense. I need to break every ingredient down to the cost per ounce so I can use the ingredients in another table where I'll be building recipes and costing them out to find out the cost per batch of dough and then per item that comes from the dough.

Thank you in advance for any advice on writing this expression.

Chad

Office
Office
A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.
1,436 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
333 questions
{count} votes