Automatically Changing Data From One Field To Another

[Pat]

I am creating an employee timesheet database which keeps track of
benefit time. I subtract the hours used from the current vacation
balance to get a new current vacation balance. I can't figure out how
to make the new vacation balance replace the current vacation
balance. For example: Jan. 1, 2007 I had 20 vacation hours. On Jan.
5th, I used 8 hours. This makes my current vacation balance 12. On
Feb. 5, I used 8 hours which now makes my balance 4. This number, 4,
should automatically become the new vacation balance.

I tried the update query but it kept changing all records to the new
balance. I want the current balance to automatically be updated each
time hours have been subtracted from the balance.

 

[Jeff Boyce]

Pat

Instead of storing a calculated value, consider calculating it when needed.
In your situation, it sounds like you could:
* add up all accrued vacation hours
* add up all taken vacation hours
* subtract to get "current vacation balance"

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat]

Pat

Instead of storing a calculated value, consider calculating it when needed.
In your situation, it sounds like you could:
* add up all accrued vacation hours
* add up all taken vacation hours
* subtract to get "current vacation balance"

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Thanks for helping me figure out what I should do which is doing the
calculations when needed.

I have to make the database update each record with the new curvacbal
instead of adding all hours at the same time and then doing the
calculation. This is how I'm trying to make it work

Record#1: DateVacTaken 1/1/07 AccVac 30 minus HrsUsed 8 =
CurVacBal 22
Record#2: DateVacTaken 1/15/07 AccVac 22 minus HrsUsed 8 = CurVacBal
14
Record#3: DateVacTaken 2/5/07 AccVac 14 minus HrsUsed 8 =
CurVacBal 6

After each record is entered, I want the curvacbal to automatically
become the accvac.

 

[Jeff Boyce]

I can't tell from your description if you are planning to store calculated
values or are calculating values "as needed" in a query. The latter gives
you a way to show the results of the calculation in both forms and reports.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP