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" 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