[AccessD] Changing table values via code

Just a thought John, but I would recommend that you don't actually change a
value in a record to do this.  Instead, make it a transaction table.  For
example:

tblVacationHours
VHTransactionID (AutoNumber)
EmployeeID (foriegn key)
NumberOfHours (Long, or Currency if there could be hour 'portions')
TransactionTimeStamp (Date/Time)

Now, you could add a third field that would determine the type of
transaction, ie, a credit or a debit, or you could simply use positive or
negative values for the NumberOfHours fields.  Either way, to determine the
employees total current number of vacation hours, simply sum NumberOfHours
for their Employee ID.  I would strongly recommend this method, because if
you simply allow someone to change a value, there is no record of what was
changed.  So if a payroll clerk goofs and removes an extra day or two, there
is no record to see what it was before, and when it was changed.  With a
transaction log (which is summed for the totals), you can see what was
entered, and when, so when someone took a Monday off, and the transaction
shows 80 hours, instead of 8, you can simply fix that transaction, or add
new transactions to compensate.

Drew

-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com]
Sent: Wednesday, June 29, 2005 8:35 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Changing table values via code


Hi everybody...long time since I've asked a question here, but I find
myself needing help again.

I am working on a db for a payroll dept. One of the things I need to do
for them is to keep a running total for time off. Basically each
employee will have a record in a table with fields for vacation,
personal, sick, etc., and I need to do two things with these numbers. I
need to add to these totals at the beginning of each years, as they earn
more time. And, I need to subtract from these totals as they use their
time. So, when a payroll clerk enters a value of 7 hrs vacation for an
employee on a given day, I need to subtract seven from their total
vacation hours.

Both of these will be done via code, at the time of the change, but I
have never done this before. I would appreciate any direction you could
offer.

Thank you


John W Clark
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com