running total at calculated field

[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.

 

[Peter]

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.

.

 

[Duane Hookom]

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

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

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.





.

.