Running sum in query

[Bob]

I am using a query that sellects transactions by vendor... there are two
fields [Credits] and [Debits] These are sorted by date... I would like to
have and expression that makes a running total of these two fields.. I would
then like to us this expression in a report..

Thanks in Advance for your help

Bob

 

[Amy Blankenship]

http://tinyurl.com/2r8z4w

 

[Bob]

Sorry to be dense but I really do not undestand.. I believe what I want to do
is much simpler than all of that programming..

If I were doing this in excell this is what I would do..

Row Number D E F Formula
1 date Description Credit Debit Balance
2 2-Jan John Smith 50 -50 D3 -E3
3 3-Jan John Smith 50 0 (D4 -E4) + F3
4 4-Jan John Smith 100 -100 (D5 -E5) + F4
5 5-Jan John Smith 50 -50 Etc….------>
6 6-Jan John Smith 25 -75
7 7-Jan John Smith 75 0
8 8-Jan John Smith 25 -25
9 9-Jan John Smith 25 0

http://tinyurl.com/2r8z4w

I am using a query that sellects transactions by vendor... there are two
fields [Credits] and [Debits] These are sorted by date... I would like to
have and expression that makes a running total of these two fields.. I
would
then like to us this expression in a report..

Thanks in Advance for your help

Bob

 

[John Spencer]

If you are doing this in a report, then don't try to do it in the query at
all.

Set up your report to do the work using three controls and a grouping by
Vendor

Control Name: txtCredit
Control Source: [Credits]
Running Sum: Over Group

Control Name: txtDebit
Control Source: [Debits]
Running Sum: Over Group

Control Name: txtBalance
Control Source: = [txtCredit] - [txtDebit]
Running Sum: No

If you feel that you have to do this in the query, then post back with the
query you currently have. Using the query will probably be a quite a bit
slower.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Bob]

John thatnks, works like a charm.. A lot better than needing a PHD..

Thanks Again

Bob

If you are doing this in a report, then don't try to do it in the query at
all.

Set up your report to do the work using three controls and a grouping by
Vendor

Control Name: txtCredit
Control Source: [Credits]
Running Sum: Over Group

Control Name: txtDebit
Control Source: [Debits]
Running Sum: Over Group

Control Name: txtBalance
Control Source: = [txtCredit] - [txtDebit]
Running Sum: No

If you feel that you have to do this in the query, then post back with the
query you currently have. Using the query will probably be a quite a bit
slower.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am using a query that sellects transactions by vendor... there are two
fields [Credits] and [Debits] These are sorted by date... I would like to
have and expression that makes a running total of these two fields.. I
would
then like to us this expression in a report..

Thanks in Advance for your help

Bob

 

[John Spencer]

Well to be fair, you did ask the question in the Queries group. If I hadn't
noticed that you wanted to use the results of the query in a report, I would
have given you one of the PHD answers.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John thatnks, works like a charm.. A lot better than needing a PHD..

Thanks Again

Bob

If you are doing this in a report, then don't try to do it in the query
at
all.

Set up your report to do the work using three controls and a grouping by
Vendor

Control Name: txtCredit
Control Source: [Credits]
Running Sum: Over Group

Control Name: txtDebit
Control Source: [Debits]
Running Sum: Over Group

Control Name: txtBalance
Control Source: = [txtCredit] - [txtDebit]
Running Sum: No

If you feel that you have to do this in the query, then post back with
the
query you currently have. Using the query will probably be a quite a bit
slower.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

That is not much more complicated, in a query:

Make a first query, to collocate by date (of transaction)


--------------------
SELECT date, SUM(amount) AS totalByDate
FROM yourTable
GROUP BY date
--------------------


call it sumByDate. Then, use:

----------------------
SELECT a.date, LAST(a.totalByDate) as totalThatDay,
SUM(b.totalByDate) AS runningSum
FROM sumByDate AS a INNER JOIN sumByDate AS b
ON a.date >= b.date
GROUP BY a.date
------------------------



to supply your running sum.



Vanderghast, Access MVP