Creating Cash Flow - consecutive running sum

[Lightwave]

Dear All,

I've got involved in the organisation of the European Duathlon Championships and as treasurer I've put together a basic database to track the costs and produce monthly budget and forecast cash flow.

This is essentially a fairly basic finance tracking system and each record has costs associated to it that I store in two fields (income and expenditure) I understand I could have done this with one field and a DR / CR field.

What I'm finding tricky at the moment is getting an automatic calculation of a runnig total for cash flow. Its not a great effort but at the moment I'm taking the information out of the database and putting it into a excel sheet to allow me to just use a very simple formula to calculate a running total.

I've got a suspicion that this could be very simply be done in report format. It's just I'm having difficulty getting my head round creating a sum actually on each line that will understand that the field is referenced to the field above.

Any advice would be much appreciated.

Thanks in advance

M

PS - Age group Duathlon champs is on 16th of June in Edinburgh - with qualifier on 25th March 07 at Fife. There will also be a Citizens Tri on the 16th which will be open to all.. Great chance to visit the city and take part in a great event held in and around Holyrood Park(Brass kneck sales pitch over- I'm an honest forumite cross my heart)

 

[RCurtin]

Basically you would put an unbound text box in the page footer or the group footer section of your report.
Example:
I will assume the field you want to add is in the detail section of the report and is the name of the text box is txtExpenditure.
-You just go to the page footer section and add a textbox.
- Select the textbox you just added and in the Properties window set the Control Source to =Sum(txtExpenditure)
- This will add the values in your expenditure field and the total will appear at the end of each page.

Hope that helps,
RC

 

[Lightwave]

Thanks R Curtin

That certainly will tell me what the cash flow is for that particular page or for the whole report.

However if there has been a particularly large cash inflow followed by a particularly large outflow I will not necessarily see the max and minum bank balance which may occur at any point on the page

For instance
Date Description Amount Bank Balance
1 Jan 2007 Sponsor Payment 8000 8000
2 Jan 2007 Traffic Management -4000 4000
3 Jan 2007 Entry Fees 10000 14000
4 Jan 2007 Closing ceremony -20000 -6000
5 Jan 2007 Council sponsorship 10000 4000

For these payment just looking at the last payment summing all payments and receipts on the page it would indicate that the balance was a health 4000 when I need to know that I have to take care of a potential 6000 overdraft. Similarly that overdraft depends on where in the order of payments that would occur. Thus a running balance related to the previous record is required to give an indication of the min / max balances required..

You have given me a thought though - I could total after ever single detail - could make that work although It would involve grouping on individual ID - sounds clunky but would probably work.

 

[Rich]

Use the RunningSum property of a calculated control on your Report

 

[Lightwave]

That's brilliant Rich - I thought it might be simple

To tie up the thread

What I've done is place two further unbound text fields within the detailed section of the report.

The first simply takes the expenditure from the income (would not be required if I'd had one column with DR and CR). (This field will be hidden on the final report). Call this field [Text80].

The second unbound text field has as its source [Text80] and within the running sum property it is set to "Over All"

Looks like its totally what I wanted - Thanks

 

[Lightwave]

Follow up question...?????

Is there a way of doing the same thing in a form - just looked at an unbound text field on a form Within the detail section and cannot find a running sum property

 

[Rich]

It really needs code to perform on a form

 

[Lightwave]

Thanks Rich will investigate further..