?
Solved

Sum only positive values in ACCESS 2003 table

Posted on 2009-12-17
11
Medium Priority
?
344 Views
Last Modified: 2013-11-25
I have a table with fifteen columns that need to be summed for a report.  There are about 150 rows BUT I only want to sum the positive values.  That is, say TotalApp column has 145 rows with a positive value, but five with a negative.  Is there an easy way to tell the query to only sum the 145 posuitive values and ignore the negative?  I know I can do an If statemnt in the format of Iif(TotalApp <0, 0, TotalApp) but I really do not want to have to do this for all fifteen columns as this is eventually going to be run from a code module and not the query pane.
0
Comment
Question by:ssmith94015
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 13

Accepted Solution

by:
Torrwin earned 700 total points
ID: 26075750
I'm not sure if this is any better logistic-wise but you can do this with a sub-query as well.

SELECT SUM(TotalApp)
FROM (SELECT TotalApp FROM Table1 WHERE TotalApp > 0)
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 700 total points
ID: 26076464
Make the recordsource for the report something like

Select * from tblData where TotalApp >0

That will only get the rows with a positive value in the TotalApp column.
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26077644
That won't work for 15 columns though, the WHERE statements would start conflicting with each other if you didn't put them into subqueries.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 18

Expert Comment

by:Simon
ID: 26078653
@torrwin: You're right, it depends on exactly what ssmith wants to do.

If they want a sum on all 15 columns only where TotalApp>0, my suggestion will work, but if each individual column should only sum positive values they need a series of subqueries as you've illustrated.
0
 

Author Comment

by:ssmith94015
ID: 26081508
I think I finally got it to work, messy but seems to give me the results.  I need each of the 15 columns to sum themseles, but only the positive values.  So, I wrote a lot of Iif statements - SUM(Iif(TotalApps <0,0, TotalApps)), SUM(Iif(MailedApps <0,0, MailedlApps))  and so on for each of the columns, changing, of couse, the name of the column.  Actually, I realized there were closer to 30 columns.  But it seems to do the trick.  I was hoping not to have to go this route as the query in code is really really messy.
0
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 600 total points
ID: 26114642
Another option

you could create a query that shows you only records where the column(s) are > 0

Then just report on that query??
0
 

Author Comment

by:ssmith94015
ID: 26133994
Sorry I have not returned to this question.  My husband and a rather large truck decided to meet in an intersection - the truck won.  He is fine but was a little preoccupied.  
0
 
LVL 13

Expert Comment

by:Torrwin
ID: 26134050
I'm sorry to hear that, I hope everything works out!
0
 

Author Comment

by:ssmith94015
ID: 26306660
I am going to close out this question as the solution is no longer needed.
0
 

Author Comment

by:ssmith94015
ID: 26306680
Rather, you each gave me an idea which contributed to the final solution.  I have been a bit preoccupied with taking care of the husband, sorry it took so long to get back to this.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 26306687
No problem, completely understandable... glad to hear he is ok
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question