Running Sum 2

[darkstarjk]

I've look at many of the different posts but have come up empty handed on this one so I was hoping there is some person out there who might be able to help me solve this problem.

Current I have a Continuous form which shows bank rec information. I want it to do a running sum along side the bank rec so they can check to make sure the balances are proper. I have tried the following code:

1. =Sum([TotalReciept] - [Dispurse])
2. =Sum([TotalReciept] - Sum([Dispurse])
3. =DSum("TotalReciept","QueryName&quot

The problem with all of these is that they give me the grand total instead of a running total. This form has to be viewed inside a form otherwise I would just place it as a report.

Is there any hope for me?

Any help has my thanks in advance.

 

[DanJR]

Hi there,

I can see two possible ways to do this. You could either base the form on a query which includes the running sum. Or, use an unbound control with its control source set to a public function which uses the forms recordset to calculate the running sum.

I think the first idea is the best place to start. Could you paste the SQL statement that the form is based on, or if its based directly on a table then just list the field names.

Cheers,
Dan

 

[darkstarjk]

How would I base it on a query?

Since the form is already based off of one would I just add a sum field related to the 2 fields neccessary?

Let me refrase just in case:

If the form is already a query I just add a new field. Base the records [TotalReciept] - [Dispurse] and then sum the field then bind it to a running total box on my form. Is this correct?

P.S. I'm going to try it but watch this post for verification.

 

[DanJR]

Hi,

Yes, you'll need to edit the query the form is based on. But, the field that calculates the running sum would probably need to be based on a subquery. For example

if you had data like this:

CustID Date TotalReciept Dispurse
1 1/01/2001 2 1
1 2/01/2001 3 1
1 3/01/2001 4 2
2 1/01/2001 8 1
2 2/01/2001 8 1
2 3/01/2001 8 2


then the query:

SELECT A.CustID, A.Date, (SELECT sum([TotalReciept]-[Dispurse]) FROM tblAmounts AS B WHERE B.date<=A.date AND A.CustID=B.CustID) AS RunningSum
FROM tblAmounts AS A
ORDER BY A.CustID, A.Date

Would produce this:

CustID Date RunningSum
1 1/01/2001 1
1 2/01/2001 3
1 3/01/2001 5
2 1/01/2001 7
2 2/01/2001 14
2 3/01/2001 20

So, if you paste your form's query, then we could adapt this to your query.

Cheers,
Dan

 

[darkstarjk]

OK Cool. So far so good. Now one last thing to throw into the equation (sorry, wasn't thinking about this before). What if there are Null values. Is there some way to tell it to fill with a 0? Other than that I think the calculations are working.

 

[darkstarjk]

Sorry again...should read everything to the letter. Here's my code:

SELECT CHECKREG.Date, CHECKREG.SpecifyBank, CHECKREG.TotalReciept, CHECKREG.Reciept, CHECKREG.DepositCheckNumber, CHECKREG.[Description (Last Name)], CHECKREG.[Description (First Name)], CHECKREG.DisburseCheckNumber, CHECKREG.Disburse, CHECKREG.TotalRecieptCheck, CHECKREG.Cleared
FROM CHECKREG
WHERE (((CHECKREG.Date) Between (Forms![BANK REC]!StartDate) And (Forms![BANK REC]!EndDate)) And ((CHECKREG.SpecifyBank) Like Forms![BANK REC]!BankName))
ORDER BY CHECKREG.Date, CHECKREG.SpecifyBank, CHECKREG.TotalReciept, CHECKREG.Reciept;

 

[DanJR]

Hi there,

Here is your orginal query (but used an alais for the table name):

SELECT
A.Date,
A.SpecifyBank,
A.TotalReciept,
A.Reciept,
A.DepositCheckNumber,
A.[Description (Last Name)],
A.[Description (First Name)],
A.DisburseCheckNumber,
A.Disburse,
A.TotalRecieptCheck,
A.Cleared

FROM
CHECKREG AS A

WHERE
(((A.Date) Between (Forms![BANK REC]!StartDate)
And (Forms![BANK REC]!EndDate))
And ((A.SpecifyBank)
Like Forms![BANK REC]!BankName))

ORDER BY
A.Date,
A.SpecifyBank,
A.TotalReciept,
A.Reciept;

In my example, i used two fields (CustID and Date) to determine which fields to calculate the running

sum on. You are using 'SpecifyBank' and 'Date' (Is this correct?). So we just need to replace

'CustID' with 'SpecifyBank' and add the runningsum subquery.


SELECT
A.Date,
A.SpecifyBank,
A.TotalReciept,
A.Reciept,
A.DepositCheckNumber,
A.[Description (Last Name)],
A.[Description (First Name)],
A.DisburseCheckNumber,
A.Disburse,
A.TotalRecieptCheck,
A.Cleared
(SELECT sum(B.[TotalReciept]-B.[Dispurse]) FROM tblAmounts AS B WHERE B.date<=A.date AND

A.SpecifyBank=B.SpecifyBank) AS RunningSum
iif([RunningSum] is null,0,[RunningSum]) AS RunningSum2

FROM
CHECKREG AS A

WHERE
(((A.Date) Between (Forms![BANK REC]!StartDate)
And (Forms![BANK REC]!EndDate))
And ((A.SpecifyBank)
Like Forms![BANK REC]!BankName))

ORDER BY
A.Date,
A.SpecifyBank,
A.TotalReciept,
A.Reciept;

With respect to null values, i've added a [RunningSum2] column with converts nulls to zero's.

I hope with helps,

Cheers,
Dan

 

[darkstarjk]

Alright. It's running well now. Thank you for your time!!!

 

[ednz]

Thanks contributors. I can now see how to do running sums in a form.

I'd like to be able to do a query with a running sum, but I'm having trouble working out how to refer to the current record.

Example

I have a table &quot;monies&quot; with two fields, name and money, and want to create a table with names listed in order with the value value of the money variable, and a running total of the money variable.

Name Money Running total
a 1 1
b 2 3
c 4 7


How could one write a subquery to achieve this?

How about :
(select
a.sum(money)
from
monies as a
where
a.name <= [money])

It does not work but gives the total of the whole table for each record.

CAn anyone help?

 

[DanJR]

hi there,

try this:


SELECT [Name],
[Money]
(SELECT Sum([Money]) FROM monies as B WHERE B.Money <= A.Money) as RunningSum

FROM
monies as A

ORDER BY [Money]

Cheers,
Dan

 

[darkstarjk]

Is there maybe a way to specify a record number (Without using a auto recnumber per table) that could make this a little bit different. My totals are totaling by date so it is grouping numbers if multiple checks were written for the same day. I've tried altering the code to the following:

SELECT DISTINCTROW
A.Date,
A.SpecifyBank,
A.TotalReciept,
A.Reciept,
A.DepositCheckNumber,
A.[Description (Last Name)],
A.[Description (First Name)],
A.DisburseCheckNumber,
A.Disburse,
A.TotalRecieptCheck,
A.Cleared,

(SELECT sum(IIf(B.Reciept Is Null,0,B.Reciept) - IIf(B.Disburse Is Null,0,B.Disburse)) FROM CHECKREG AS B WHERE (B.Date Between (Forms![BANK REC]!StartDate) AND A.Date) AND B.SpecifyBank=A.SpecifyBank) AS RunningSum,

(SELECT sum(IIf(C.Reciept Is Null,0,C.Reciept) - IIf(C.Disburse Is Null,0,C.Disburse)) FROM CHECKREG AS C WHERE (C.Date Between (Forms![BANK REC]!StartDate) AND A.Date) AND C.SpecifyBank=A.SpecifyBank AND C.[Description (Last Name)] <> C.[Description (Last Name)]) AS RunningSum2,

IIf(A.Reciept Is Null,IIf(A.DisburseCheckNumber Is Not Null,[RunningSum2],[RunningSum]),0) AS RunningSum3

FROM CHECKREG AS A

WHERE (((A.Date) Between (Forms![BANK REC]!StartDate) And (Forms![BANK REC]!EndDate)) And ((A.SpecifyBank) Like Forms![BANK REC]!BankName))

ORDER BY
A.SpecifyBank,
A.Date,
A.TotalReciept,
A.Reciept,
A.DisburseCheckNumber;

The problem is that sometimes the names are the same. Also the DisburseCheckNumber field is a text value since sometimes that have to place in various characters for certain checks. Is there any better way for me to do this?

PS. Thank you for your invloved time in this, you've rocked!!!

 

[darkstarjk]

Sorry, but I was thinking...is there some way to convert the DispurseCheckNumber into a temporary value so that it can be used in the formula. If so that would be the simplest solution cause the check numbers never go out of order.

 

[ednz]

Thanks DanJR

This is what worked (I've a new example with table &quot;items&quot; containing autonum field &quot;ID&quot; and field &quot;value&quot

SELECT
items.ID,
items.value,
(select sum(b.value)
from items as b
where b.id <= items.id)
AS runningsum
FROM items;

The key seemed to be that the subquery should use an alias for the source table and the where clause should have the alias table field on the left.

 

[darkstarjk]

Thanks for the reply,

My probelm is that sometimes the records get updated so they get deleted out and if the numbering scheme changes that the id may not be the same. I really don't want to have it reassign the ID numbers again after every task, but I guess that's always an option.

 

[DanJR]

Hi there,

What we are doing here is using a &quot;correlated subquery&quot; to create the running sum. Its called a subquery because it is a query nested within the main query, and it is correlated because the subquery is dependant on one or more of the rows in the main query.

The key to generating a runnning sum, is to know which fields of the main table determine the rows the that subquery will 'look up' and what is the criteria of the 'look up' (ie. how does the subquery relate to the main query).

Since the subquery is based on the same table as the main query, aliases for table names are required so that we can differentiate which 'instance' of the table we are refering to. The main query refers to one 'instance' and the subquery refers to both 'instances'. I our examples, we have created two aliases, 'A' and 'B'. Although we really only needed one alias, I create both just to state the obvious (for some reason, I find the query easier to read).

For the running sum to work the way you want it, we need a way of uniquely identifying a row in your main query. In my first example, I assumed CustID and Date combined uniquely identified a row. However, with your data, this is not true as many checks could be written on the same date.

Which rows identify a unique ID in your dataset - is it [SpecifyBank], [Date] and [DispurseCheckNumber]? If so, then you need to adjust the subquery's WHERE clause to add [DispurseCheckNumber] as a criteria too.

e.g.
..... AND B.[DispurseCheckNumber] <= A.[DispurseCheckNumber]

If you want the enteries in your query to stay in the order that you entered them in, then I would create an autonumber field (say called 'OrderID') in the table and then use this field to relate the subquery to the main query....

e.g. consider the table (TotalReciept & Dispurse fields not shown)

[tt]
OrderID CustID CheckNum Date
1 1 1 1/1/02
2 1 2a 1/1/02
3 1 b5 1/4/02
4 1 6 1/4/02

Using OrderID:

SELECT A.CustID,
A.Date,
(SELECT sum([TotalReciept]-[Dispurse])
FROM tblAmounts AS B
WHERE B.date<=A.date AND
A.CustID=B.CustID AND
B.OrderID <= A.OrderID) AS RunningSum

FROM tblAmounts AS A

ORDER BY A.CustID,
A.Date
A.OrderID


Using CheckNum field:

SELECT A.CustID,
A.Date,
(SELECT sum([TotalReciept]-[Dispurse])
FROM tblAmounts AS B
WHERE B.date<=A.date AND
A.CustID=B.CustID AND
B.CheckNum <= A.CheckNum) AS RunningSum

FROM tblAmounts AS A

ORDER BY A.CustID,
A.Date
A.CheckSum

[/tt]

Note that which ever field you use to separate out rows within the same date (i,e. OrderID or CheckNum field in example above), you need to include them in the ORDER BY clause of the main query.

If you look in the WHERE clause of the subquery, you can see how the subquery works:

&quot;A.CustID=B.CustID&quot; --> makes sure that we don't mix up customers

&quot;B.date<=A.date&quot; --> we calculate the sum based of all previous dates, but including this current rows date aswell.

&quot;B.CheckNum <= A.CheckNum&quot; --> if check numbers occur on the same date, then this bit will separate them out, provided we also order by CheckNum (ascending) in the main part of the query.

If the main part of the query sorted CheckNum descending then the subquery WHERE clause will need to be:

&quot;B.CheckNum >= A.CheckNum&quot;


Does this help answer your problem...?

As an aside, subqueries may slow down a query, so you may want to assess the indices of your table to see which combination of indices give you optimum performance.

I hope this helps...
Cheers,
Dan

 

[DanJR]

Opps,

any where that I wrote &quot;CheckSum&quot; - it is meant to be &quot;CheckNum&quot;

 

[ednz]

Just a comment on running totals using two fields, say date and checknum.

DanJR's method is OK if checks are always numbered sequentially with date. If this is not the case, then it may be better to use:

where 
     a.date < b.date 
or 
    (a.date = b.date 
          and 
       a.checknum <= b.checknum)

This makes clear that the choice is by date, but checknum is used to distinguish between transactions on the same date.