G'day, I'm currently working on an annual leave database for the company, so far so good, till they want the database to be able to identify public holidays and prevent reducing the employee's balance if the application overlaps with the holiday. I'm using access 2000. Here's what I have so far.
tblMainProfile - contains employee's profile
EmployeeID <== Pkey
Name
Date of Birth ..
tblLeaveApply - link to tblMainProfile by EmployeeID
LeaveID <=== Pkey
EmployeeID
DateFrom
DateTo
Half day (AM/PM)
Days <=== number of days between DateFrom and DateTo for that record
LeaveTypes
Year ..
tblLeaveEntitlement - link to tblLeaveApply by EmployeeID
Index <=== Pkey
EmployeeID
Year
LeaveType
LeaveEntitled
And then I have a query to calculate TotalDays, by
- TotalDays: Sum(Nz([Days],0))
and DatesLeft, which is the leave balance,
- DaysLeft: [LeaveEntitled]-[TotalDays].
This query is then used for sub-form1, linking EmployeeID with the Mainform.
The mainform shows that employee's profile (ID,name,department,nationality). Sub-form2 bounded directly to tblLeaveApply, is for employees to input in the dates and leave types to apply for leave. The order is:
1) employee chooses DateFrom and DateTo, AM/PM if halfday.
2) employee then clicks on a button to calculate the number of days between DateFrom and Dateto, and if AM/PM = Is Not Null, the Days-0.5. (calculations for the button is done in VB, on-click event).
If possible, can you recommend a design such that when the employee picks DateFrom, DateTo and AM/PM if applicable, and then clicks on the button to calculate number of days, the database checks through tblPH, and for every PH_Date that is within DateFrom and DateTo, then Days-1.
Well, I roughly know the logic, but this is more or less my first time actually doing a database, so I can't figure it out in terms of a database application. I tried using SELECT count function in query but it showed me 26, when I only have 2 PH_Dates in the table. So I'm a little puzzled. Plus, let's say that count function works, I can't figure out how to implement it into the form too.
Thanks,
Johnny
