How do I calculate a value in a new record from the previous recor

  • Thread starter Thread starter RickSF
  • Start date Start date
R

RickSF

How do I calculate a value in a new record from the value based on the value
of previous record?

the table has 3 fields for calculation:
Field1.TransactionDate
field2.ChangeAmount
field3.NewBalance

I need to do an Update Query that uses the [NewBalance] from the previous
record plus the [ChangeAamount] to update the [NewBalance] of the following
record.


for example:
TransactionDate ChangeAmount NewBalance
10/13/08 100
100
10/14/08 50
150
10/15/08 75
225

Thanks for any help.

Rick
 
Rick, the right way to handle this in a database woudl be to store only the
transaction value, and not the balance.

You can then get Access to calculate the balance for you in a report (using
a running sum text box - it's dead easy), or with a subquery. The subquery
is a bit more effort: this year-to-date query illustrates the idea:
http://allenbrowne.com/subquery-01.html#YTD

Why it is so absolutely crucial not to store the balance is that one day
someone will enter a transaction out of order. If you are storing the
balances, your existing records will be compromised (wrong stored balance.)
That's the reason why one of the basic rules of data normalization is that
you must never store dependent values.
 
Allen,

Thank you for the response. I do know how to calculate the total in a
report. Although I did not explain the purpose of my request, I used the
simple example below because I wanted to know if it was possible to refer to
data in a hierarchial relationship. Sometimes, a static report is not
desired.

Rick


Allen Browne said:
Rick, the right way to handle this in a database woudl be to store only the
transaction value, and not the balance.

You can then get Access to calculate the balance for you in a report (using
a running sum text box - it's dead easy), or with a subquery. The subquery
is a bit more effort: this year-to-date query illustrates the idea:
http://allenbrowne.com/subquery-01.html#YTD

Why it is so absolutely crucial not to store the balance is that one day
someone will enter a transaction out of order. If you are storing the
balances, your existing records will be compromised (wrong stored balance.)
That's the reason why one of the basic rules of data normalization is that
you must never store dependent values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

RickSF said:
How do I calculate a value in a new record from the value based on the
value
of previous record?

the table has 3 fields for calculation:
Field1.TransactionDate
field2.ChangeAmount
field3.NewBalance

I need to do an Update Query that uses the [NewBalance] from the previous
record plus the [ChangeAamount] to update the [NewBalance] of the
following
record.


for example:
TransactionDate ChangeAmount NewBalance
10/13/08 100
100
10/14/08 50
150
10/15/08 75
225

Thanks for any help.

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