Hi
Your help needed please
It could have been done easily in Update Query on which I tried a lot but update query does not work as it gives the error message “ Operations must use updatable query” So I decided to use VBA on which I have little knowledge frankly speaking.
I am designing an Employee Salary module for a New School. The school remains closed during Winter i.e. from 15th Dec. to end of Feb next Year (28/29 Feb). The school on the last day of each month normally disburses the salary to its employees even during the winter season (they call it Winter Salary from 15th Dec to 28/29Feb next year) Subject to the condition that the employee has completed minimum service of 184 days up to 15th Dec.(i.e. if he was appointed upto 15th June). And if his service is below 184 days he is entitled neither for Winter Salary nor for the next increment.
The School releases the yearly increment as on 31st January every year irrespective of hiredate.
I have created Employee table with following fields
1. EmployeeID ; DataType Autonumber (pk)
2. FirstName ; DataType (txt)
3. BasicSalary ; Datatype (Number)
4. NewBasicSalary;– Datatype (number)
5. HireDate ; DataType (date)
6. LastIncrementDate; DataType (Date) It should be give some value at the beginning
7. NextIncrementDate ; Datatype (Date)
IncrementAmount is calculated as (BasicSalary + 10% of BasicSalary)
On 31st January each year NewBasicSalary =BasicSalary+incrementAmount
(Next increment will fall exactly one year after Last Increment Date I have used the formula as NextIncrementDate: (DateAdd("m",12,[lastincrementDate]))
SalaryMonthsPaid: (DateDiff("m",[lastIncrement],Date()) [ Number of months for which Salary paid)
Formula should been :– If SalaryMonthsPaid>12
then update NewBasicSalary with (BasicSalary+incrementAmount)
Update LastIncrementDate with NextIncrementDate
Next Year the IncrementAmout will increase as per rate already fixed.
Simultaneously the system should update NewBasicSalary, LastIncrementDate with Next IncrementDate and also the IncrementAmount.
Thanks
Jalal