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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Torrwin
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.
@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.
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'm sorry to hear that, I hope everything works out!
ASKER
I am going to close out this question as the solution is no longer needed.
ASKER
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.
No problem, completely understandable... glad to hear he is ok