Processing all records in table

[allanjf]

Greetings friends,
Not sure if this is a table, query or form issue.

I'm working on a system for recording employee information, leave details, etc. I need to run a monthly update to increment each employees leave balance.

The two tables now in question are;

Employees - relevant fields are EmployeeID (key), Active (Boolean) and Leave (Numeric) [annual leave entitlement - eg 15 days]

The second table is LeaveTrans;
Emprec, LDate, LType, PrevBal, ThisLeave, NewBal, Comments.

What I need to achieve;
For each "Active" record in the employee table, detirmine the monthly leave entitlement ("Leave" /12 - no problem here).
Append a new record to the LeaveTrans table with;
Emprec, LDate (Date of the update), LType (="Monthly allocation"), PrevBal (The NewBal from the last record in the table for this employee), ThisLeave (monthly allocation) and NewBal (PrevBal + ThisLeave)
I will need to access the last record for this employee to get the previous NewBal, before appending the new record.

The problem I'm having is appending to the LeaveTrans table, and then moving on to the next record in the Employee table, to repeat the process

I've tried the FAQ's but can't seem to find a suitable answer. If anyone can help or point me at a previous thread, I'd be grateful. The finished product (if ever I get there!) will be for an old age home which is a non-profit organisation.

Thanks
Allan

 

[plog]

You shouldn't store calculated values. PrevBal and NewBal shouldn't be fields in a table, but a fields in a query.

Do you also use LeaveTrans to debit leave from a person? If so, what you need is search for 'Running Sum Query' in this forum and use what you find to build a query that generates a running sum for balance on the LeaveTrans table.

Removing the balance fields from your table eliminates the issue you are facing and allows you to do a straight append.

 

[The_Doc_Man]

If I am reading this correctly, you are trying to update a record in a table to be the sum of its previous value plus some transactions. This is probably not the right way to do that. While "NEVER" is a harsh word, normally one would NEVER update a master record with a running total.

What you want is a LeaveTrans table-based query that is the sum of all entries for a given employee (summation query with GROUP BY for employee number.) Then you have transactions in this table to add leave or remove it, with removals representing a negative number and accruals representing a positive number. You can define a leave type called "initial balance" or "adjustment" or whatever else you need. You don't care how many of these you have as long as the signs of the leave amounts are correct to add or subtract hours/days/whatever.

Then, to build the leave-balance report, what you do is build a query that joins your Employees table to the summation query (which you can do very easily). The top of the query design grid allows you to express a relationship between two fields even if one is a table and one is a query. Make this an outer join so that you can have an empty sum with an NZ(sum, 0) in the column of the query that defines the leave balance.

To get started, everyone has a leave balance of 0 (no leave at all). Accruals get positive leave numbers. People taking leave get negative numbers. Adjustments go in any direction as required. Then all you have to do for someone who wants vacation is do a lookup of their leave balance in the summation query. If they have enough, fine. If not, disallow the leave (or issue a leave overdraw notice, depending on company policy.)

If you have to archive the transaction table, what you do is have a transaction called "Interim Balance" with a positive or negative balance as of a given date. Copy older transactions to your archiving table and append the "Interim Balance" transaction for the employee. Ideally, the balance in the "Interim Balance" transaction is just the sum of all transactions earlier than that transaction date. And yes, it is OK if the second time you do an archiving step, one of the archived transactions is the prior interim balance transaction. Just remember, if you do this, be sure to leave no transactions behind that were earlier than your interim balance. Otherwise, you are going to double-dip on some transaction.

In essence, you are treating leave balance in the same way you would treat a bank balance or an inventory system, with the constraint that you never allow the total to go negative.

Or did I totally miss what you intended?

 

[allanjf]

Thanks for your advice. I'll give that a try.
I have another small problem I need advice on:
I have a field "AnnualLeave", long, fixed 2 dec places.
When I do a division on the value, it give the wrong answer
eg. AnnualLeave = 15
Dividing by 12 (AnnualLeave / 12) give the answer 1.00, not 1.25

Any ideas?
Thanks

 

[Pat Hartman]

You have the field defined as an integer. You need to change the data type to something that allows decimals. I suggest Currency since you don't need more than 4 decimal places. Currency is a data type as well as a format. The currency datatype is a scaled integer rather than floating point as are single and double so you won't end up with strange rounding errors.