sumif function in Access query, possible?

  • Follow


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
0
Reply Utf 4/26/2010 6:32:01 PM

Cam wrote:
> 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)
-- 
HTH,
Bob Barrows


0
Reply Bob 4/26/2010 6:44:42 PM

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" <Cam@discussions.microsoft.com> wrote in message 
news:21DAF77A-5B5F-480C-A5B3-C8FFE4619E18@microsoft.com...
> 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 

0
Reply vanderghast 4/26/2010 7:02:08 PM

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.

-- 
Daryl S


"Cam" wrote:

> 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
0
Reply Utf 4/26/2010 7:07:02 PM

3 Replies
2426 Views

(page loaded in 0.029 seconds)


Reply: