running total at calculated field

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I have created a table of payment. I want to produce a
form that will give me an up to date balance (running
total)

fields [category], [amount]
In the form, I like to show the running total of the
amount on each line.
Access have the running total function in report, but
donot have one in form.

Please help.
 
Thanks for the reply, Dunae.
I have created a query for the fields - [date],[Category]
and [amount]. In addition, I create a field called
[AccumTotal] in the query. I am trying to use this field
to store the running total. I can sort this field into
ascending order of the [date] field. How I can get the
running total to store in this field ? Please help.

Peter
-----Original Message-----
You can't logically create a running total without a field that determines
the order of the records. Do you have an Order By field? Do you have a table
name? Do the resulting records need to be updateable?

--
Duane Hookom
MS Access MVP


Peter said:
I have created a table of payment. I want to produce a
form that will give me an up to date balance (running
total)

fields [category], [amount]
In the form, I like to show the running total of the
amount on each line.
Access have the running total function in report, but
donot have one in form.

Please help.
Click to expand...


.
Click to expand...
 
Yes, you must have some expression that makes each record unique. You could
then use an expression like
Format(a.Date,"yyyymmdd") & a.Category <=Format(tblA.Date,"yyyymmdd") &
tblA.Category

--
Duane Hookom
MS Access MVP


Peter said:
Dear Duane Hookom :
Thank you again for your help.

There will be more than one transactions in the same day.
Therefore, the A.Date<= tblA.Date) as AccumTotal will not
done properly on the 2nd records on the same day.

This is what I expect to have :
e.g.
01/01/2001 Travelling $100.00 $100.00
01/01/2001 Telephone 25.25 125.25
01/01/2001 Hydro 123.00 248.25

Do we need to set another sorting field to identify the
transaction. e.g. ID - this will then be unique.

When sorting in the query (design mode). If I going to
sort the [date] and [type] and [id] in ascending order,
how do we identify which field to sort first ?

The reason I stored the AccumTotal is just for use in some
other area without recalculated the same again. When new
entry is entered, requery will be done again.


-----Original Message-----
Select [Date], Category, Amount, (Select Sum(Amount) FROM tblA A Where
A.Date<= tblA.Date) as AccumTotal
FROM tblA
Order By [Date];

This should create a query with a running sum of the amount. Is there a
reason you want to store this value? What would happen if you inserted an
older Date and amount into the table.

--
Duane Hookom
MS Access MVP


Peter said:
Thanks for the reply, Dunae.
I have created a query for the fields - [date], [Category]
and [amount]. In addition, I create a field called
[AccumTotal] in the query. I am trying to use this field
to store the running total. I can sort this field into
ascending order of the [date] field. How I can get the
running total to store in this field ? Please help.

Peter

-----Original Message-----
You can't logically create a running total without a
field that determines
the order of the records. Do you have an Order By field?
Do you have a table
name? Do the resulting records need to be updateable?

--
Duane Hookom
MS Access MVP


I have created a table of payment. I want to produce a
form that will give me an up to date balance (running
total)

fields [category], [amount]
In the form, I like to show the running total of the
amount on each line.
Access have the running total function in report, but
donot have one in form.

Please help.





.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top