Simple Question on Addition

[GwenH]

I searched the discussion groups for the answer to what seems to be a simple
question, but found nothing. I have a group by query with three columns:

itemNo
Debits
Credits

The query is grouped on the first column, itemNo.

I want to add the Debit column to the credit column and show the result in a
third field, Total. For each record, the Debit column contains currency
amounts. The Credit column contains either negative currency amounts or is
null.

Here is my nonfunctioning SQL. When I run the query, the Total column is
blank for every record.

SELECT eBayFees_lastMonth.[Item number], Sum(eBayFees_lastMonth.Debits) AS
SumOfDebits, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits,
Sum([eBayFees_lastMonth.Debits] + [ebayFees_lastMonth.Credits]) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

Any ideas?

Thanks,
GwenH

 

[Jeff Boyce]

Gwen

What happens when you run that query?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Use the NZ function (or an IIF statement) to force a value if Credits is Null

SELECT eBayFees_lastMonth.[Item number]
, Sum(eBayFees_lastMonth.Debits) AS SumOfDebits
, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits
, Sum([eBayFees_lastMonth.Debits] + Nz([ebayFees_lastMonth.Credits],0)) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

Using an IIF statement:
SELECT eBayFees_lastMonth.[Item number]
, Sum(eBayFees_lastMonth.Debits) AS SumOfDebits
, Sum(eBayFees_lastMonth.Credits) AS SumOfCredits
, Sum(Debits + IIF(Credits is Null,0,Credits)) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

SELECT eBayFees_lastMonth.[Item number],
Sum(eBayFees_lastMonth.Debits) AS SumOfDebits,
Sum(eBayFees_lastMonth.Credits) AS SumOfCredits,
Sum([eBayFees_lastMonth.Debits]) +
IsNull(Sum([ebayFees_lastMonth.Credits]),0) AS Total
FROM eBayFees_lastMonth
GROUP BY eBayFees_lastMonth.[Item number]
ORDER BY eBayFees_lastMonth.[Item number];