Calculating subsequent field values in table

[JLM]

I have a table that has fieldA, fieldB, fieldC. I want
fieldC=fieldA-fieldB. simple enough. the next record I want to be able to
do the same on the new value of fieldC.

I can do this with SAP ABAP/4, but have never done this using Access.

I'm sure this can be done, but not sure how to go about it.

thanks in advance,
jlm

[Bas Cost Budde]

JLM wrote:

I have a table that has fieldA, fieldB, fieldC. I want
fieldC=fieldA-fieldB. simple enough. the next record I want to be able to
do the same on the new value of fieldC.

I can do this with SAP ABAP/4, but have never done this using Access.

I'm sure this can be done, but not sure how to go about it.

Storing a running sum here? As a general rule, don't store calculated
values. Produce them as you need them (in a query, on a report)
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[JLM]

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0***********@news2.solcon.nl...

JLM wrote:

I have a table that has fieldA, fieldB, fieldC. I want
fieldC=fieldA-fieldB. simple enough. the next record I want to be able to do the same on the new value of fieldC.

I can do this with SAP ABAP/4, but have never done this using Access.

I'm sure this can be done, but not sure how to go about it.

Storing a running sum here? As a general rule, don't store calculated
values. Produce them as you need them (in a query, on a report)
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

I'll be needing the values later (as in months from now), so that's why I
want to keep them.

[Rick Brandt]

"JLM" <je**********@colostate.edu> wrote in message
news:40******@news.ColoState.EDU...

I'll be needing the values later (as in months from now), so that's why I
want to keep them.

If you will still have the values (months from now) needed to perform the
calculation you can still do so. So there is still no need to store the result
now (and many reasons not to).
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bas Cost Budde]

Rick Brandt wrote:

"JLM" <je**********@colostate.edu> wrote in message
news:40******@news.ColoState.EDU...

I'll be needing the values later (as in months from now), so that's why I
want to keep them.

If you will still have the values (months from now) needed to perform the
calculation you can still do so. So there is still no need to store the result
now (and many reasons not to).

I do store 'calculated' values occasionally. Especially in an invoice, I
usually include product prices in the invoice line records. I change
prices often, but I don't like the idea that my history changes under my
fingers. (Well, if prices go up, it *would* be nice to receive the bonus
;-) ). And product descriptions for fairly the same reason.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Rick Brandt]

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0**********@news2.solcon.nl...

Rick Brandt wrote:

"JLM" <je**********@colostate.edu> wrote in message
news:40******@news.ColoState.EDU...

I'll be needing the values later (as in months from now), so that's why Iwant to keep them.

If you will still have the values (months from now) needed to perform the calculation you can still do so. So there is still no need to store the result now (and many reasons not to).

I do store 'calculated' values occasionally. Especially in an invoice, I
usually include product prices in the invoice line records. I change
prices often, but I don't like the idea that my history changes under my
fingers. (Well, if prices go up, it *would* be nice to receive the bonus
;-) ). And product descriptions for fairly the same reason.

I would not consider either of those a "calculation" though.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[JLM]

Let me try to be a little more specific about what I'm trying to do.

I have a form which feeds a table (TblEmpLeave) of Employee Leave Time (time
taken off for Administrative, Annual, Sick, Compensation, leave). I have
LeaveDate, LeaveType, LeaveHours fields on this form. Any employee can have
multiple entries in the table (key fields are EmpID and LeaveID) for
multiple dates (John Doe can take 3 days annual leave, then take 3 days sick
leave in any given month).

I have a BeginningBalance of hours they've accumulated throughout the year,
and an accumulation factor for Annual and Sick leave (which is another
question entirely).

I want to subtract from the BeginningBalance of LeaveType(s) for each record
in TblEmpLeave. As such, I can calculate the first occurance of leave, but
if the employee has another record in TblEmpLeave, I'm having problems
subtracting from the new value of BeginningBalance.

Sick Leave example:
for first occurance of SickLeave taken by an Employee that is appended to
TblEmpLeave;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] -
[TblEmpLeave].[SickLeaveHoursUsed]

for second and subsequent occurances of same employee in TblEmpLeave;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] -
[TblEmpLeave].[SickLeaveHoursUsed].

finally, at the end of the month, for each record in TblEmp, after
calculating all leave used;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] +
[TblEmp].[SickLeaveAccumulationFactor].

I'm pretty sure I know what I want to do (which is a good start), but not
sure how to go about it.

thanks again in advance.
jlm
"JLM" <je**********@colostate.edu> wrote in message
news:40******@news.ColoState.EDU...

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0***********@news2.solcon.nl...

JLM wrote:

I have a table that has fieldA, fieldB, fieldC. I want
fieldC=fieldA-fieldB. simple enough. the next record I want to be

able

to do the same on the new value of fieldC.

I can do this with SAP ABAP/4, but have never done this using Access.

I'm sure this can be done, but not sure how to go about it.

Storing a running sum here? As a general rule, don't store calculated
values. Produce them as you need them (in a query, on a report)
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

I'll be needing the values later (as in months from now), so that's why I
want to keep them.

[Bernard Peek]

In message <40******@news.ColoState.EDU>, JLM
<je**********@colostate.edu> writes

I'll be needing the values later (as in months from now), so that's why I
want to keep them.

It sounds as if you are trying to record something line line-item values
in an invoice. Once the invoice has been raised the numbers must remain
fixed for all time. It's quite normal to store those in addition to the
data they are calculated from. When you analyse your sales figures you
don't want to have to recalculate the figures.

You can use an update query to calculate the values and store them in
the database. In the case of an invoice you may want to keep a separate
table with total values in. That saves you having to do a lot of
calculations every time you look at your sales figures.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.