How do I have a running total subtracted from a starting budget?

[Ed]

I want to build a report for the finance dept. that shows several accounts,
each with a starting budget with a running balance. One account should look
like this:
Purch# Desc Cost balance
Opening balance 1000
1 Pizza 10 990
2 soda 5 985
3 widgets 200 785

The account table fields:
- AccID#
- startingBudget

The Purch fields:
- Purch#
- Desc
- Cost
- AccID#

 

[Michel Walsh]

Hi,


SELECT a.purchNumber, LAST(a.desc), LAST(a.cost), -SUM(b.cost) As Balance
FROM myTable As a INNER JOIN myTable As b
ON a.purchNumber >= b.purchNumber
GROUP BY a.purchNumber



should do, as long as the first record is

PurchNumber Desc Cost
0 Opening balance -1000


(ie, - because it is a negative cost, it is an income)

Note also I give a PurchNumber rather than leaving it blank.



Hoping it may help,
Vanderghast, Access MVP

 

[Ed]

Thanks. The opening balance is in another table.

Ed

 

[Michel Walsh]

Hi,


Could that record be ALSO append to the purchases table? If not, either you
have to use manually, a constant:


SELECT a.purchNumber, LAST(a.desc), LAST(a.cost),
StartingConstant-SUM(b.cost) As Balance
FROM myTable As a INNER JOIN myTable As b
ON a.purchNumber >= b.purchNumber
GROUP BY a.purchNumber



either to somehow incorporate that table in the FROM clause which is already
sufficiently complex as it is, or something similar, or to use a non-trivial
DLookup, to read the appropriate starting constant.


Hoping it may help,
Vanderghast, Access MVP