creating cash forecast with ms access query 2

[NeilBelgium]

dear all,

I'm trying to create a system, based on a simple table, that tracks finances - invoices in and out, and what the cash position will be iun the future.

To my mind, we are talking a very simple table indeed-
ID
DATE
AMOUNT

The amount can be a +/- figure to denote either money coming, or money going, like a bank account.

HOW, though, can we get running totals for a particular period in time. For exalple, suppose the table has five records (sorry, using european date format!)

1 26/2/2002 +5000
2 27/02/2002 -2000
3 3/3/2002 +10000
4 4/4/2002 +20000
5 6/5/2002 -10000

how on earth can we calculate what is the balance in march, or april or may?????

really hope you can help, neil

 

[MichaelRed]

A 'simple?' aggregate query w/ parameter(s) should do it easily. The parameter(s) would be the beginning and ending dates for the calculation (or just the ending date - if it is always cummuilative from the beginning). The aggregate query is just the SUM. since you have an ID, I would assume that is an account ID, and so you would need to GROUP BY this for all accounts, or also include a parameter for the specific account.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[NeilBelgium]

thanks. the id is yes, to identify the particular invoice, I will probably add names etc later, but the db will ask for some unique key, so for now it's simply an autonumber, ID.

still not sure I understand how to go abou this.

going back to my original table,
1 26/2/2002 +5000
2 27/02/2002 -2000
3 3/3/2002 +10000
4 4/4/2002 +20000
5 6/5/2002 -10000

i should be able to create an ongoing balance:

26/2/2002 +5000
27/2/2002 +3000 (record 1+record2)
3/3/2002 +13000 (sum record 1,2,3)
4/4/2002 +33000 (sum 1-4)
6/5/2002 +23000 (1-5)

can you elaborate?

many thanks! neil

 

[tlbroadbent]

You need a running sum which Access doesn't handle with a straight forward aggregate. This should give you an idea.

Select
MyDAte,
(Select Sum(Amount) From MyTable
Where ID <= t.ID) As AmtSum
From MyTable Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[NeilBelgium]

thanks! i'm really surprised that it's not so simple!
neil