Query with Multiple IIF

[rdemyan via AccessMonster.com]

I'm creating an SQL statement for use in code. One of the fields that is
retrieved is facility area, AREA. The area unit of measure (UOM) could be SF
(square feet), SY (square yards), or AC (acres). I want to be able to do a
sum in the SQL statement and convert everything to SF.

So how would I do a SUM(AREA) in the SQL statement and at the same time
convert all area values to SF if the UOM (unit of measure) is either SY or AC.
SF would not need a conversion.

I've used IIF in the past, but never with two different situations (i.e. AC
and SY).

So

SUM(IIF(UOM='SY',9*AREA, AREA))

This should take any value for AREA where the associated UOM is square yards
and multiply by 9 to convert it to square feet and then sum everything. But
how would I add the additional condition if UOM = 'AC' in which case the AREA
needs to be multiplied by 43560 to get square feet.

Thanks.

 

[Duane Hookom]

I like to use lookup tables. Consider creating a table
tblSFMultipliers
===============
Unit Multiplier
SF 1
SY 9
AC 43560

You can add this table to your query/sql and join the UOM fields. Create a
new field in the query
CalcArea: [Multiplier] * [AREA]

 

[rdemyan via AccessMonster.com]

Okay. So for an SQL statement for use in code, something like


SELECT SUM(A.AREA*B.MULTIPLIER) FROM TABLEA As A, TABLEB AS B
WHERE B.UNIT= A.UOM

I like to use lookup tables. Consider creating a table
tblSFMultipliers
===============
Unit Multiplier
SF 1
SY 9
AC 43560

You can add this table to your query/sql and join the UOM fields. Create a
new field in the query
CalcArea: [Multiplier] * [AREA]

I'm creating an SQL statement for use in code. One of the fields that is
retrieved is facility area, AREA. The area unit of measure (UOM) could be

[quoted text clipped - 24 lines]

Thanks.

 

[rdemyan via AccessMonster.com]

No need to answer. I got it to work. This is much better than what I had
planned.

Thanks!

Okay. So for an SQL statement for use in code, something like

SELECT SUM(A.AREA*B.MULTIPLIER) FROM TABLEA As A, TABLEB AS B
WHERE B.UNIT= A.UOM

I like to use lookup tables. Consider creating a table
tblSFMultipliers

[quoted text clipped - 13 lines]