Ms access query for an employee management system

i am seeking assistance in designing a query/macro/script for an employee management system that allows for the followin functions: -

my system is supposed to be capable of deducting the an instance of every leave taken from the total alloted days granted per year. i need a query or script that works similar to an inventory system where every time a leave is registered against an employee it deducts from the maximum allotted days.

i hav the tables employee leave and leave. the leave table specifies the max allotted days - DSL/MOH has a maximum of 14 days and CL has a maximum of 5.

attributes in the employee leave table are :- Leave ID, employee No*, Start Date, End Date, Duration, Days Paid For, Date Entered, Approval Date, Leave Type ID

Leave table:- [Leave Type ID, Leave Name]


your assistance is greatly appreciated

You have only listed the tables that track the leave an employee has taken (Employee leave) and the table that specifies the type of leave.

You say that the leave table specifies the max alloted days, but you haven't provided the column names that do that.

Based on the design you have provided my guess is that the values for the leave table are similar to:

1 : Holiday
2 : Sick
3 : Vacation
etc.

Is this correct?

You haven't provided anything that tracks the count or number of days that an employee is entitled based on their classification (DSL/MOH or CL).

Given this lack of information the best I can do is to provide a base count of days taken by an employee.

SELECT Sum(Duration) as Leave_Taken, [employee no*], Year([Start Date])
FROM Employee leave
GROUP BY [employee no*], Year([Start Date])

This just uses calendar date for summing the leave taken and doesn't account for someone taking leave that crosses the new year. You would need to provide additional information to make this more accurate.

tanx for the info but you sorta lost me.
as it is now, i can do a select query to list all the DSL/MOH and CL that any given employee has taken for the current year. what i need is something that would perform calculations to count the leave balance of DSL/MOH and CL..... once that has been accomplished, i would then need to figure out how to reset the maximum alloted days each year. i hope you understand what i am tryin to do

i eagerly await your response