Totals

[Katja.Buckley]

I have a report that lists whether a suite of a building is occupied
(Tenants's name) or vacant (vacant).

I am trying to create a total of all sqft that are currently occupied.
Meaning I need the sum of all sqft minus the sqft of those entries
where the Occupant name is "vacant".

And then I would like to creat a sum of all sqft where the occupant
name is "vacant".

Can anybody help with this???

Thanks!

 

[Clifford Bass]

Hi Buckley,

Try:

select Sum(IIf([Occupant]="vacant", sqft, 0) As Unoccupied_SqFt,
Sum(IIf([Occupant]="vacant", 0, sqft) As Occupied_SqFt from ....

Clifford Bass

 

[KARL DEWEY]

SELECT Sum(IIF([Tenant] <> "Vacant", [sqft], 0) AS Occupied, Sum(IIF([Tenant]
= "Vacant", [sqft], 0) AS Vacant
FROM YourTable;

 

[Duane Hookom]

try:
=Sum(Abs([Occupant]<>"vacant") * [sqft])
=Sum(Abs([Occupant]="vacant") * [sqft])

 

[Clifford Bass]

Hi Duane,

I like your approach; it avoids the use of the IIf() function.
However, I would suggest moving the Abs() function to the outside, which I
presume would change the number of times it gets executed from n down to 1.

Abs(Sum(([Occupant]<>"vacant") * [sqft]))

Clifford Bass