Running total issue

sassenach

Registered User.
Local time
Today, 22:49
Joined
Mar 10, 2003
Messages
42
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
 
In order for a running sum to work correctly in a query, the recordset must have a uniqueID and the transactions must have been entered in relative order. I'm guessing that you have a data anomoly in your table. Create a select query and use the DSum()'s criteria and see what you get that you didn't expect.
 
please explain in more details...

I dont understand what you mean about making a query using Dsum. Where? in my ASP page or in my Query in Access?
I already made an Acess query as I displayed earlier in the forum.
And what about the uniqueID? I already use one according to the MonthId. Is that what u mean?
 
Did you look at your data? Use the criteria from the DSum() for a separate query so you can see the records that it selects:

Select * from tblCD
Where Monthid < ??????;

Replace the question marks with the "key" of the january 2005 record. Then sum the selected records to see the answer. Identify the record(s) that don't belong in the set.
 
Hi Pat,

Sorry about posting in other topics.

I think I did as you mentioned:

I created a query Query1 with the following infor:
SELECT tblCD.CurrIncomeNum, tblCD.CurrExpenseNum
FROM tblCD
WHERE (((tblCD.MonthId)<25));


I then recieved two colums with my income & expenses.

When I try and add a SUM (another column) to this, I get an error. This is where I don't understand.

And why use my January 2005 ID[currently MonthId=25]? What happens when I add more months after? I will have to keep on changing that number?

I am confussed:)

I look forward for your reply. Thanks again
 
I found the problem...

I did do my query calculations correctly.
Turns out there was a hidden month that did not show in the query. I only seen it when I open the month table. It was a test month I did and forgot to delete it, so it stayed hidden, yet the amount that was in there was added in my calculations.
In other words...sorry to have troubled u, but thank you for your help. you did give me the idea of going back and checking the table outputs.
thanks again
 

Users who are viewing this thread

Back
Top Bottom