sumif function in Access query, possible?

[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

 

[Bob Barrows]

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

TotalQty:SUM(IIF([OprStatus]=1.[qty],0)

 

[vanderghast]

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

 

[Daryl S]

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.