Calculating in Forms

[undee69]

Good day everyone..

Hoping to get some advice from you.

What I am trying to track down is the number of leave days of our personnel as well as their remaining leave days but per record.

I used the following record:
TableEmp
empID- 1
empName - undee
LeavePerYr - 21
AccruedLeave - 2
TotalLeave - 23

LeaveHistory
transactionID - autnumber
empID - number


AnnualLeave - number
HalfdayLeave -number
SickLeave - number
SpecialHoliday - number
UnpaidLeave - number
RemainingLeave - number

So for example, I have a total of 23 leave days. I applied leave for 2 days on one transaction.

transactionID - 1
empID - 1
AnnualLeave - 2
HalfdayLeave -0
SickLeave - 0
SpecialHoliday - 0
UnpaidLeave - 0
RemainingLeave - 21

then on my next leave application, 3 days.

transactionID - 2
empID - 1
AnnualLeave - 3
HalfdayLeave -0
SickLeave - 0
SpecialHoliday - 0
UnpaidLeave - 0
RemainingLeave - 18

on my thrid leave application, 1 day.

transactionID - 2
empID - 1
AnnualLeave - 1
HalfdayLeave -0
SickLeave - 0
SpecialHoliday - 0
UnpaidLeave - 0
RemainingLeave - 17

I am able to count the total number of leaves as well as the remaining leaves already.

My question now is, is there a way that the form can automatically subtract my "Totalleave" to my Annualleave and save it to "RemainingLeave" per record?

[June7]

That requires code and why would you want to? Saving calculated, especially aggregate, data is usually a bad idea. Calculate when needed.

[ssanfu]

In addition to June's comment, you do not have a normalized table. You are "committing spreadsheet".
I know it seems simpler, but it is not. What happens if you need to add another type of leave?? As is, you will have to redesign your queries, forms and reports.


You might think about a structure like this:

LeaveHistory
------------
transactionID_PK - autnumber
empID_FK - number
LeaveTID_FK Long
LeaveDays - single


LeaveTypes
----------
LeaveID_PK - autonumber
LeaveDesc - Text

[undee69]

thanks for those inputs. I am still a beginner with access. will try them out ^_^