Sum only positive values in ACCESS 2003 table

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.
Sandra SmithRetiredAsked:
Who is Participating?
 
TorrwinCommented:
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
 
SimonCommented:
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
 
TorrwinCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SimonCommented:
@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
 
Sandra SmithRetiredAuthor Commented:
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
 
Auric1983Commented:
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
 
Sandra SmithRetiredAuthor Commented:
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
 
TorrwinCommented:
I'm sorry to hear that, I hope everything works out!
0
 
Sandra SmithRetiredAuthor Commented:
I am going to close out this question as the solution is no longer needed.
0
 
Sandra SmithRetiredAuthor Commented:
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
 
Auric1983Commented:
No problem, completely understandable... glad to hear he is ok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.