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
0
Utf
4/26/2010 6:32:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
4054 Views

Similar Articles

[PageSpeed] 43

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

Similar Artilces:

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

Can't access hotmail from home
Hello, I run windows xp pro with sp2 and all of a sudden i was unable to access hotmail.com and my hotmail account linked to this cpu through outlook express. i get this message when i try to send an e mail. The server does not support the required HTTP methods. Account: 'Hotmail (1)', Server: 'http://services.msn.com/svcs/hotmail/httpmail.asp ', Protocol: HTTPMail, Server Response: 'Method Not Allowed', Port: 0, Secure(SSL): No, Error Number: 0x800CCC35 when i try to go to the web page hotmail.com it juts keeps asking me to log in and does nothing else... I ...

Help with a query 01-23-08
Hi All, I am sure there is a simple solution to this but my brain doesn't seem to be working at the moment. In my dataset one field has the value "Data Quaternity" and the other values are NULL (but could be another string) and I want to exclude any records from the dataset which have this value. I have tried to put the following expressions in the Criteria for the query. NOT "Data Quaternity" or NOT Like "Data Quaternity" But when I do this all I get is an empty data set. I can sort on IS NULL in the criteria now, but I don't want to do this as ...

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

Query to text file cuts my leading zeros off.
Hello, this is my third attempt to post this question. I have a query that has a value of "012810" in one of the fields. When I run the query it shows up as 012810. When I export to excell it shows up as 012810. When I export to text file it always cuts off the zero and I get 12810. If I change the value to say 212810 and then export to a text file it works fine and returns 212810. Why is the zero being chopped off during a text file export? Thank you, VADIMBAR Its being treated as a numeric value. Is this column defined as numeric or text? It needs to be defin...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

Query to populate parentitem lookupcode
Hello guys, How to display parentitem lookupcode instead of id. For example : If i write : select itemlookupcode,description,parentitem from item where parentitem<>0 it will show : 100111,Marlboro Lights,5676 Instead of id i want it to show its parent item lookupcode..how do i do that? How do i join inside something that exists in one table Hi Fez I use a view to "cross join" and be able to see the item lookup code of an item's parent. I suck at cross joins - learn them once, then forget them. So views many times save my bacon. This view sets up ...

Strange CObArray access violation
I have a CObArray which I've been using for some time within a class (derived from CDocument). I've added a new member function which throws an access violation exception every time I try to access the array. The exception is thrown with the following line: int arraySize = m_AtomArray.GetSize(); The access violation shows up here: _AFXCOLL_INLINE int CObArray::GetSize() const { return m_nSize; } I have initialized the array within the Class initialization list, and successfully traced the size of that array within the class constructor. As I have already mentioned, other member...

Web query research saving
Hi there I'm really a Newbie of VBA . I would like to build a database, which keep record of value an releated time from a web query value, which is being updating every mins . I attach what I did ( .... just for laughing) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then ThisRow = Target.Row If Target.Text > " " Then Range("C" & ThisRow).Copy Range("D" & ThisRow).PasteSpecial xlPasteValues End If End If End Sub Thank -- Message posted from http://www.ExcelForum.com you might like this better If target.row &...

Function Parameter Type
Hi I'm having problem calling a method of a COM. The signature of the exposed method is like this. HRESULT GetMailLists(VARIANT* bstrLists) If I call this method from a C# code, the signature changes to void GetMailLists(out object bstrLists) In C#, I called it as object obList; users.GetMailingLists(out obList); and obList type changed to "System.Array" and was filled with an array of desired string values. Now the problem is that I have to use this method from my VC++ code and have to use late binding for using the COM. Can someone tell me how would I call this method ...

accessing rows with macros
Hi, I'm quite new to excel. I'd like to loop through all the rows in my sheet and compare the text in the cells of the first column with some string variable priorly set. How can I do that ? Also I would like to disable my cells for editing, so that only my function run when I click on a button can modify those cells. I've done it through protecting the sheet from editing, but then even my macro can't edit the cell without having to enter the password;) Thx For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"A") = "somevalue&qu...

Cannot Access CRM 1.2 from Web
Hello. Thanks in advance for any help!!!! I am having a heck of a time accessing CRM from the web. I set up a host header in IIS. "crm" I configured an alias in DNS. crm --> 192.168.3.100 Now, locally, I can get to CRM in the browser of the CRM server using the host header - so the header works. That's great. From the browser of another PC on the LAN... I can access the companyweb I can access exchange But I cannot get to crm. SO IIS is working. But it won't give me crm. I am trying the following. http://servername-crm-1/crm http://192.168.3.100/crm Neither wor...

Size of Excel file Access db exports to.
I have an Access db that exports about 350 queries to a specific Excel file. This is done on a weekly basis then monthly basis. The db and exporting works great. However the size of the file. The size of the file after the save is as large as my largest month of data which makes sense. Is there anyway to compact an Excel file like you can in Access to get reclaim space that may be used in the monthly data but not in the weekly data? The weekly data was making the file about 26 MB. After I ran the monthly data it ballooned to 36 MB. All but about 20 of the queries are created through code. Wou...

Access to RMS Database
Having issues connecting to RMS DB from backoffice computer...and not so co-incidentally I started getting some error messages on the Server regarding NetBT (Net Bios over TCP/IP) having issues (not starting due to driver init error, couldn't create driver...of course MS diagnosis is "a transient condition that can be safely ignored"...yeah right). Anyhow, I presume that RMS needs NetBt in order to facilitate SQL/DB access and transport ? (in addition to network file transfer and printing too from what it looks like...basically the server is isolated from the world save fo...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Password Change Functionality in Exchange 2003
We have an Exchange 2003 server (single server) running on W2K3 SP1 w/ OWA configured. Everything works well/ SSL is enabled. I followed the instructions described in KB Article 297121. The Change Password button is present and when clicked, a pop-up appears: "Internet Service Manager for Internet Information Server 6.0" Five fields are listed: Domain, Account, old Password, New Password, Confirm New Password....after filling in the fields and clicking on OK, I get "Error number: 5" Even if I leave all the fields blank, same error. I did notice when I logged on dire...

query very slow
i have a query that takes about five minutes to run. i think that the delay is not justified: the table contains around 40000 records and the query is based on two separate queries whose performance is ok. below is the sql of my query if anyone could help me please! SELECT [Quotation-Suppliers-qry].[Order Number], [Quotation-Suppliers-qry].[PART number], [Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].Description, [Net Price]-[MinOfNet Price] AS Expr4, [Pricelist-qry-0].[MinOfNet Price], [Quotation-Suppliers-qry].[Net Price], ...

How do I throw in an ISERROR function on a complicated VVLOOKUP?
Hi, I have a working VLOOKUP below. Of course the desitination cell says #N/A until I put something in A6. I tried ISERROR but it says too many arguments. Did I do this wrong? Can anyone help? Thanks. =IF(VLOOKUP(A6,CSM,8)="X","X",IF(VLOOKUP(A6,NPM,8)="X","X",IF(VLOOKUP(A6,PM,8)="X","X"," "))) How about =if(a6="", "",formula or a6=0 -- Don Guillett SalesAid Software donaldb@281.com "KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message news:C4469806-8831-4631-B74D-687...

SUMIF or SUMIFS help
I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

Copy Functionality Lost
Up until a day or two ago, I could select a cell to copy, paste it into another cell and the original copied cell would remain available to be copied again (it would be highlighted with the dotted border). Now, after one paste the copy cell reverts to normal, and I have to re-select it to copy into another location. I know I can use CTRL or SHIFT, but I find the retention of the copy ability useful. This is a networked office PC and imagine I might have to ask our IT Dept (as Administrator) to do something. Any ideas appreciated, thanks If you paste by pressing Enter you'll exit ...

between date and dateadd query
Hi all, I have just noticed that when i use the following query it is showing all results and disregarding the date. I am 99% sure this worked in the past and have not edited the query. The only thing that has changed is that we are now in the year 2008. I use the following, Between Date() And DateAdd("d",[Enter Number of Days to Expiration],Date()) Or >Now() This lets me enter a number and the report returns records thatare due within todays date plus the number of days i type in. I am sure this worked until the year actually changed to 2008, is this possible? any help app...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

Function is killing performance
Hi - I'm using a custom function in a query and it takes forever - was hoping someone might have an idea of how to speed things up a bit. A little more info: the function is relatively simple (4 arguments that are run through an IF statement to pick a value from another table (only 12 records). The query is run against a table with 41k records. The numbers don't seem to big here, and the query takes over an hour. Any ideas?? Where & how are you using the IF's? Sounds like a coding problem to me Pieter <stephen.h.dow@gmail.com> wrote in message news:1189019241.93425...

This query is typed incorrectly or is too complex to be evaluated.
I'm trying to retrieve records with a start date and stop date criteria. The format of the Date column in my table is general date. I dont understand why it wont work FROM [Spreadcheck on this laptop] WHERE (((([Spreadcheck on this laptop].Date)>=[Start Date] AND [Spreadcheck on this laptop].Date)<[Stop Date])); OK - I guess the brackets are the problem.... it work now, but asks for the start time and stop time twice! Thanks for any help "GWB" wrote: > I'm trying to retrieve records with a start date and stop date criteria. The > format o...

Wish list for Entourage Web Access fixes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange I was fortunate enough to work with Entourage Web Services since the public beta, but now that a final version is out in the wild, we are now noticing issues that really need to be taken care of. <br><br>To get compatibility issues out of the way, all of these reports involve EWS version 13.0.3 connecting to Exchange 2007 running the latest service pack. <br><br>1. While category synching is finally supported in calendars, it is not supported if the calendar is being ac...