Conditional Totals?

  • Thread starter Thread starter Andy Pennell via AccessMonster.com
  • Start date Start date
A

Andy Pennell via AccessMonster.com

My apologies if this has been answered before, however I cannot seem to find any answer that actually answers my query.

First time I have used Access and I have created a Libraries Inventory of IT equipment using A97 and I am now trying to put together some reports that provide statistical analysis. I am trying to count the number of staff and public workstations.

I have used a simple query which breaks them down and counts the number of staff and also the number of workstations at each location.
Based upon this query I have then created a report which displays this information (with the fields "StaffPublic" and "CountOfStaffPublic"). Using the expression "=Sum([CountOfStaffPublic])" in the report I can also successfully get a total number of workstations for each library (in the library footer) and even a grand total (in the report footer).

What I can't work out how to do however is how to get a grand total of "Public workstations" and a grand total of "Staff workstations"

Anyone got any simple ideas?
 
Hi Andy.
You did not give much of a clue as to how your table is structured.
I hope it is a normalized table something like
Location; Quantity; Staff-Public
A 1 S
A 3 P
B 6 S
etc.

Than you can get total of Qty by S-P
=Sum(Abs([Staff-Public]="S") * [Quantity])
or
=Sum(Abs([Staff-Public]="P") * [Quantity])
and for a grand total
=Sum([Quantity])
Hope this helps
Fons
 
Fons,

Thanks for taking the time to answer my query. Apologies for not being clear. My table looks like this:

Site A
Staff 10 workstations
Public 7 workstations
(Total for Site A = 17 workstations)

Site B
etc etc

However, the formula you gave me works great!! Thanks very much
 
Back
Top