If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumif function in Access query, possible?
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 |
#2
|
|||
|
|||
sumif function in Access query, possible?
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 |
#3
|
|||
|
|||
sumif function in Access query, possible?
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 ... 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 |
#4
|
|||
|
|||
sumif function in Access query, possible?
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 |
Thread Tools | |
Display Modes | |
|
|