Running total issue...

[sassenach]

Hi,

I made a query where I i have my total credit, my total debits, (Expr1)my total credits minus debits, and my running total of Expr1. Problem is, in the last record, the calculation is off by a few numbers.

Here is my SQL View of the query MyQuery:
SELECT DISTINCTROW tblCD.MonthId AS MonthIdAlias, (Format(Sum([CurrIncomeNum]),"Currency")) AS [Sum Of CurrIncomeNum], (Format(Sum([CurrExpenseNum]),"Currency")) AS [Sum Of CurrExpenseNum], Format([Sum Of CurrIncomeNum]-[Sum Of CurrExpenseNum],"Currency") AS Expr1, tblMonth.MonthName, tblMonth.YearNum, Format(DSum("CurrIncomeNum-CurrExpenseNum","tblCD","[MonthId]<=" & [MonthIdAlias] & ""),"Currency") AS RunTot
FROM tblMonth INNER JOIN tblCD ON tblMonth.MonthId = tblCD.MonthId
GROUP BY tblCD.MonthId, tblMonth.MonthName, tblMonth.YearNum
ORDER BY tblMonth.YearNum, tblCD.MonthId;

For example:
Lets say:
Month -- Credit -- Debit -- Expr1 -- Running Total
October 2004 -- 43,513.98 -- 0.00 -- 43,513.98 -- 43,513.98
November 2004 -- 6,250.00 -- 200.00 -- 6,050.00 -- 49,563.98
December 2004 -- 6,250.00 -- 567.00 -- 5,683.00 -- 55,246.98
Janurary 2005 -- 6,250.00 -- 200.00 -- 6,050.00 -- 61,316.98

Make sense? Janurary 2005, the running total calculation is not correct.
This is the calculation i made in the query MyQuery above:
Format(DSum("CurrIncomeNum-CurrExpenseNum","tblCD","[MonthId]<=" & [MonthIdAlias] & ""),"Currency") AS RunTot

thanks in advance

 

[Rich]

Please stick to your original post, and why are you using the Format Function?

 

[sassenach]

why not?

why not? does it cause a problem?

 

[Pat Hartman]

Duplicate postings waste our time and clutter the board. Please stick to a single post on a topic. Add additional questions until the issue is resolved.