Total positive and negative values in a query

[Fabdav]

I have a table with all the sales
I would like to have on two fileds the total of the sales and the total of the returns.


At the moment I have to create two separte query one with values > 0 and one with values < 0.
Can I do it on a sigle query?

[pbaldy]

Probably:

SELECT Sum(IIf(Amount > 0, Amount, 0)) AS TotalPositive, Sum(IIf(Amount < 0, Amount, 0)) AS TotalNegative
FROM TableName

[swat]

Hi guys, I am having a similar issue as this one. I would like to have 1 query do the work of 3 if possible. Having 3 columns with 3 sums involving the same table but different criteria. I tried to incorporate Pbaldy's code and recieved this error, same error if I do it in design view. Here is the error.

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

Here is the SQL:

SELECT Sum([Weight_Before]-[Weight_After]) AS Gas_Used, Sum(IIf(([SF6_Usage_Log].[Initial_Fill]=Yes),([Weight_Before]-[Weight_After]),0)) AS Initial_Gas, Sum(IIf(([SF6_Usage_Log].[Initial_Fill]="No"),([Weight_Before]-[Weight_After]),0)) AS Transient_Gas
FROM SF6_Usage_Log
HAVING (((SF6_Usage_Log.Date_Used) Like "*" & [What year? (yyyy)]));

I have also tried design view in the "Field" box:

Initial_Gas: [Weight_Before]-[Weight_After] AND [Initial_Fill]="Yes"
and the "Total" box: Sum

And recieved the same error.

Any help would be greatly appreciated.

Shawn

[pbaldy]

What is the data type of Initial_Fill? You're treating it 2 different ways. Also, if Date_Used is a date field, I don't know how reliable using Like will be. Try:

HAVING Year(SF6_Usage_Log.Date_Used) = [What year? (yyyy)]

[swat]

Thank you for the quick reply pbaldy. The Initial_Fill column is a "Yes/No" box. Also, if the Date_Used field is a mm/dd/yyyy date, will the Having Year() function still work?

[pbaldy]

Then "No" should be No (or 0).

If the data type is date/time, the year function will work regardless of format.

[swat]

Awesome, I understand now what you were saying. I changed the date code to match yours as well. It works as intended now. Thank you so much.

[pbaldy]

Happy to help!