Prev: How to make a query sort randomly each time it is run?
Next: Update query question
From: Cam on 26 Apr 2010 14:32
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
From: Bob Barrows on 26 Apr 2010 14:44
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


From: vanderghast on 26 Apr 2010 15:02
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(a)discussions.microsoft.com> wrote in message
news:21DAF77A-5B5F-480C-A5B3-C8FFE4619E18(a)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

From: Daryl S on 26 Apr 2010 15:07
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
 | 
Pages: 1
Prev: How to make a query sort randomly each time it is run?
Next: Update query question