CODEWITHSUNDEEP
excelms-access
cookiemonster
cookiemonster

Reputation: 368

How to do a calculation by evaluating several conditions?

I have this table in access called invoices.

It has a field that performs calculations based on two fields. that is field_price*field_Quantity. Now I added two new columns containing the unit of measure for field_weight and field_quantity named field_priceUnit and field_quantityUnit.

Now instead of just taking the multiplication to perform the calculation, I want it to see if the units of measures match, it doesn't match then it should do a convertion of the field_quantity into the unit of measure of field_priceUnit.

example:

Row1: ID:1|Field_Quantity:23|field_quantityUnit:LB|Field_weight:256|field_priceunit:KG|field_price:24| Calculated_Column:

the calculated_column should do the calculation this way. 1. if field_quantityunit=LB and field_priceunit=LB then field_quantity*field_price

else

if field_quantityUnit=LB and field_priceUnit=KG THEN ((field_quantity/0.453592)*field_price) <<

Please help me.

I have to do this for multiple conditions. Field_priceunit may have values as LB,KG, and MT Field_quantityUnit may have field as LB,KG, and MT

if both units don't match, then I want to do the conversion and calculate based on the new convetion as seen in the example.

Thank you

Upvotes: 0

Views: 27

Answers (1)

JNevill
JNevill

Reputation: 50034

The following formula should get you running if your units are only lb and kg and you only have to check one direction:

iif(and(field_quantityunit='LB', field_priceunit='LB'), field_quantity*field_price, (field_quantity/0.453592)*field_price)

This doesn't scale well though as you may have to convert field_price or you may add other units. This iif formula will grow WAY out of hand quickly.

Instead create a new table called unit_conversion or whatever you like:

unit | conversion
lb   | .453592
kg   | 1
g    | 1000
mg   | 1000000

Now in your query join:

LEFT OUTER JOIN unit_conversion as qty_conversion
    ON field_quantityunit = qty_conversion.unit
LEFT OUTER JOIN unit_conversion as price_conversion
    On field_priceUnit = price_conversion.unit

Up in your SELECT portion of the query you can now just do:

 (field_quantity * qty_conversion.conversion) * (field_price * price_conversion.conversion)

And you don't have to worry what the units are. They will all convert over to a kilogram and get multiplied.

You could convert everything over to a pound or really any unit of weight here if you want but the nice thing is, you only need to add new units to your conversion table to handle them in any sql that you write this way so it's very scalable.

Upvotes: 1

Related Questions