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
5086 Views

Similar Articles

[PageSpeed] 0

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:

Access 2003 Security Message On Open
All, I have approximately 15 databases that I am about to migrate to Access 2003 from 2000. I do not want the security message to appear every time one of my users logs on to the database. Registry changes and digital signatures are out of the question. If I put Application.AutomationSecurity = msoAutomationSecurityLow on open of my start up form will this prevent the warning message? Is there something I can put in the windows shortcut to turn this off? I have seen some suggestions were it is mentioned to call a script that calls the database to open in low security mode. Is this nec...

Match function with time
I am trying to use the MATCH function to find last train that I can catch to reach my destination before, on time or just after a given time. The arrival times are E1:E10 and also named as ArriveTime and is formatted HH:MM AM/PM The target time in H1 and is also formatted as HH:MM AM/PM. For test purposes I am using 10:00AM and ArriveTime contains 09:59,10:00 and 10:01AM Using =MATCH($H$1,ArriveTime,0) or =MATCH($H$1,ArriveTime,1) work fine and both return the row (5), but =MATCH($H$1,ArriveTime,-1) returns #N/ A instead of the row of the earliest train after the target. I am using Excel 2007 ...

Lan access password
Running a home Lan, peer to peer WinXP Pro, (no server software) 1. I use WinXP pro sp3 on all my machines on my LAN. 2. We all boot without passwords, I think it defaulted that way at install giving us the "owner.whatever" account names. 3. We are all on the same named Workgroup I am not so concerned about boot up login passwords (but will do if necessary) but I do want to find a way (if possible) to setup LAN access passwords for each node of the work group. Can I do this ? ( I have already searched my control panel and cannot find anything on this other ...

question about using the offset function
Is there a way to use the offset function (or another function) as a way to supply an expanding range to other functions like 'average'? For my purposes, there may be multiple series of numbers within say a column, with each 'series' (i.e. set of contiguous rows) separated by a few blank cells. The objective of the function would be to supply a limited set of numbers (which can expand) to the function whose result is the main interest. For example, within column D, I might have rows 5-10 representing adult male fish weights, rows 15-20 representing adult female fish weights ...

Help with functions
If I have the following formula in a worksheet to bring up a message box =IF((F4-E4)>=10,Amend()+F4-E4,F4-E4) How can I use a function to transfer some information from different cells to a new worksheet if the formula above works? You can't have a function transfer information to another cell, it will return it's info in the cell where it's located, you would need a formula(s) in the cells where you want this information. Otherwise you would need VBA Regards, Peo Sjoblom "Monty" wrote: > If I have the following formula in a worksheet to bring up a me...

function defaults change in Pivot Table..why?
I was recently working in my pivot table and had something happened that doesn't make a lot of sense to me so I'm asking for some assistance. Usually, when I drag a field on to my Pivot Table in the DATA section it defaults to the sum function with a number field. It also defaults to a count function when I drag a field that has text in it. Recently, I got a pivot table that default a number field to the count function. Can anyone explain why the default has changed on this particular Pivot Table? Any asistance would be greatly appreciated ! Thanks! Tim If there's a blank ...

OL2003: Quirky function of contacts under "Other Contacts"
I am getting a strange error to which I have found temporary work-aroun solutions but don't have any idea how to really resolve the problem. Problem: After establishing an email account with "Download Public Folde Favorites" Checked, I "Add to Favorites" a Public Folder Contact List. I click the "Contacts" shortcut and after clicking the contact lis under "Other Contacts", I receive an error message "Operation Failed immediately. Clicking on the OK button results in normal operation an the contact list is completely functional. Work-Around: ...

Not sure if this is possible
I have my database set up to process our company's cellphone bills. Everything works perfectly but there is a feature I would like to add. since all of the data is already there, I have a query set to look at the data and provide me with the minutes used for each month, date of the invoice and cost charged for each phone. In the report I have it sorted by phone number and user and I have averages set for each user simply by entering in the start and end dates of the invoices queried. Now, here's the question. I know I can do averages in a query but would that no depend on sp...

Excel 2002
....I have a large amount of amount to produce multiple charts from, so using Autofilter is a bit of a necessity. Using the Chart Wizard, I can easily select the filtered data range but it doesn't seem possible to select the (filtered) headings as the X-axis (time period). Any ideas or workrounds? Thanks in advance, Amanda when you set up your autofilter range leave the row you want to use for your axis out of the range. "Birmangirl" wrote: > ...I have a large amount of amount to produce multiple charts from, so using > Autofilter is a bit of a necessity. Using the...

ranking a query or report
I have a report that ranks plyers based on the winning percentage. I just have a running sum so it adds 1 to each name. The problem is this does not work when I use the report as a sub-report to show the teams and players. What I am looking for is this. A report that shows each team and their record as well as the players on that tteam and their record. and what those players rank in the entire league. I currently have everything but the last part. What happens is when the original report shows as the sub-report, I only get the rank on the team. so each team has players ranked 1 throug...

Descision based on a query
Hello, I'm hoping this should be easy - I just can't seem to find the answer. I'm guessing my syntax is wrong but I can not figure it out. I am making a Db for work to keep track of Tasks and Performance. The 2 main functions I am trying to keep track of is total hours signed in and how long it takes to complete each of the tasks assigned to the Employee. I'm trying to put a small check in it that an employee can not begin a task without being signed in for the day. This is being tracked on 2 different tables. (Attendance.tbl and Times.tbl) So in the form to begin t...

Query "deleteditemsagelimit"
All, What's the best way to query/configure the "deleteditemsagelimit" in an AD03/Exch03 environment? For whatever reason, I've found that some users' accounts are configured to not use the mailstore defaults for deleted items retention, and I can't find a pattern. Hence, I'd like to enumerate all accounts that have a non-default/misconfigured "deleteditemsagelimit" property. You need to get the following user attributes: 1) deletedItemsFlags - this value is set to 5 when "use mailbox store defaults" is unchecked from user properties...

Autostart query every day
Hi, is it possible to start a query every day at a specific time, automaticly? You don't say if you DB will be open - so I assume it will not (ie. a backup at night, send reports overnight, etc) You can use xmacro - something like this. msaccess.exe PathToDatabase /x NameOfMacro If I have not understand your question please answer with more details. -- Wayne Manchester, England. "Forza MIlan" wrote: > Hi, is it possible to start a query every day at a specific time, automaticly? You understand my question right (sorry for my English) 10x for the reply, but can y...

Query Confirmations still opening
all, i figure my solution is to turn off warnings, but i wondered if any knew why query confirmations are still happening when the user says the "confirm" in Editing Advanced Options are unchecked: Record changes, Document Deletions, Action Queries. the user is usually offsite and have no direct access to check whether this is actually true. if any knows why the issue is occurring or has a better solution let me know, TIA. You need to to use Set Warnings in code or a macro. Below is an example of a Make Table query. You could do something similar with a macro and...

Query broken link error
What's up MVPs?! Hope you're all doing well! If I have a query with a broken link, when I open it from the db window, and error message displays. Unable to display... blah blah... But, if I open the query this way: DoCmd.OpenQuery qdf.Name, acViewDesign The error message doesn’t fire. Any idea how to get/catch the error message to trigger from VBA code? TIA, Steve Clark FMS, Inc. Do you mean you use some object, which VBA knows as qdf, but whose ***name property*** is to be used as argument of the DoCmd.OpenQuery? If so, are you sure the object qdf.name is the same nam...

Access not enforcing referential integrity!!!
Howdy. Using Acc 2007. Have two tables related one-to-many. Ref. integrity being enforced, without any cascading updates or deletes. Data in both tables. Problem: I can add new records to the many-side table, without any value for the foreign key, and the new record is created without error or prompt from Access. I can do this both in the child table directly, and using a form/subform. On the other hand, if I create a new child record and enter a FK value that doesn't exist in the parent table, I get a message saying that a related record in the parent table is require...

Database Access
Hello, I have an existing .NET 2.0 C# Windows form application that accesses an Access database for information. The form can be utilized by 2 types of users. Type A can read & write to the database. Type B can just read from the database. The database is located on the same machine as the Windows form application. The application just accesses the database via an OleDbConnection object, and executes scalar queries against the database. I just recently received a requirement in which the customer wants the database to exist on another machine than the client application. Ever...

Business Portal External Access
Hi, can users outside of the company network i.e. working offsite or at home still login to the business portal? Is the performance affected by https:? Thanks Adam Adam, Depending on which version of Portal you're using, yes, you can access Portal externally. You will need to be on Business Portal 3.0 or later. I haven't noticed any significant slowdowns. Steve "Adam" wrote: > Hi, > > can users outside of the company network i.e. working offsite or at home > still login to the business portal? Is the performance affected by https:? > > Th...

Read-only mailbox access, how to?
Exchange 5.5 on NT4 I want to set up a Support mailbox which all can read but only some can send-as. When I created it I associated it with the Domain Admin. I then added the Search role to Domain Users. But if I connect via a POP3 or IMAP client and authenticate as a domain user I am denied access. Ditto if I use an Outlook 2000 client connected to another mailbox, and try File->Open-> Other User's Folder. What permissions do I need for read-only access? The Help on the dialog in Exchange Admin doesn't even list the Search right! jim -- Jim Hatfield On Wed, 24 Aug 2005 14:...

Access 2007 DLOOKUP
Can someone please help me with this expression? I am trying to use an ID to bring up a Name in a form. The form is named ISSUE. The table that has the ID's and Name is DEALER. The form and table fields are named exactly the same. Is this where I am making my mistake? Do I put the Dlookup in the ID or Name control source? I am new to this, so if someone could tell me what fields and table names to put in the expression and where that would be wonderful. Thank you in advance for your assistance in this matter! This is the expression I am trying to use:=DLookUp("[Name]&quo...

No GP 9 access when our Internet connection fails
When our Internet connection fails no one can access GP 9 SP3 on Server 2003/SQL 2000. This is a small local network of 25 computers, with a single Internet router. We first noticed this problem about 2 months ago. Why would a loss of our Internet connection not allow anyone access to our GP Server? Thanks, Steve Hi Steve My guess is that it is not your interent going down but you router shutting down. If all computers are connected to the same router for internet and for network access, and it went down then you would no tbe able to access internal resources or the internet. ...

VPN access 08-13-04
Hello All, For remote usrs coming in through a VPN connections, does only port 80 need to opened to the CRM server (CRM and SQL on same server)? Do they need access to the Exchange router server or does all traffic transverse the CRM server for mail. I just want to give them what is required for functional operations. Thanks Nick If you only give users port 80 access they will be able to use Microsoft CRM at least from the web client. CRM email is sent from the CRM Server directly to the Microsoft CRM Exchange connector running on the exchange server. -- John O'Donnell Microsoft C...

accessing a javascript variable using IHTMLDocument
Hi, I have the IHTMLDocument2 interface set up for a web page in an MFC VC++ prog. eg IHTMLDocument2* pHTMLDocument2; hr = pMC->QueryInterface(IID_IHTMLDocument2, (void**)&pHTMLDocument2 ); The page contains: var variable_name = "variable data"; within a <SCRIPT> tag. Is there a straightforward way to return the value of the variable_name using the interface? Of course I know I can do a hard-coded search using, say, 'c' char functions, but I just wondered... TIA ...

outlook web access doesn't work properly on Windows XP Profesional
Hi all, I already tried to open my e-mail using Exchange 2000 outlook web access on XP Profesional but it's having trouble. The internet explorer was only perform OWA's toolbar and appearance just kept loading on it. Please give the suggestions. Regards, Nita If have running WinXP pro also, but it works fine. Probably this will be a security issue if your PC is behind a firewall or is taking part of a network. "Yunita Yanto" <nita@muliagroup.co.id> wrote in message news:03f001c349bf$e3e6f0d0$a501280a@phx.gbl... > Hi all, > > I already tried to open ...

How to put an Empty Cell in an IF function...
Let me try this a different way... I am using Excel 2003 and I've got a chart that is linked to data located on another sheet within the same workbook. It is a weekly production report and it always records a 0 for the weekends since we do not produce anything on Saturday or Sunday. The data is extracted from another source - not manually entered - and will always have the weekends included. The chart is structured such that the 'Y' axis is comprised of the values on the Data worksheet in column X, rows 14 through 49 ($X$14:$X$49) and represent the production yields. Th...