sumif function in Access query, possible?

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have a query I am trying to calculate the sum of qty if OprStatus equal 1,
but there is no sumif function in Access. What can I do to work around it?
Thanks
 
Cam said:
Hello,

I have a query I am trying to calculate the sum of qty if OprStatus
equal 1, but there is no sumif function in Access. What can I do to
work around it? Thanks
Click to expand...

TotalQty:SUM(IIF([OprStatus]=1.[qty],0)
 
If that is the only sum you want, you can bring OprStatus in the grid,
change the GROUP BY to WHERE, and add the criteria: = 1. I assume you
already have a Total query, if not, the SQL statement should look like:

SELECT SUM(something)
FROM somewhere
WHERE OpsStatus = 1



You can add a group by clause too, if required. As it is now, the whole
table is considered to be ONE group.



Vanderghast, Access MVP
 
Cam -

In query design you just need to add the field OprStatus and down in the
criteria row put a 1. Then the query will only return records where
OprStatus is 1. The query should have the Total row showing (if not, click
on the totals button in the query design toolbar. Then in this row, select
Sum for the field you want summed.
 
Back
Top