I'm creating a database that will be used to track who is taking vacation days from work. The goal is to be able to limit the number of employees absent from the workplace to less than 10% on any given day.
I have one table that lists all employees [tblemployees]and their basic info. A form allows a supervisor to input an individual and their first day and last day of vacation (stored in another table, [tblLeaveData]).
I want to use a query to return all of the records for those that are on vacation on a given day, then use COUNT, to tell me how many records that is - in order for me to derive a percentage of employees on vacation on any given day.
I can successfully filter how many records are on leave in a given month (month designated in a form on switchboard) by using ((DatePart("m",[StartLeave]))=[Forms]![Switchboard]![MonthAbbrev])) in one field and ((DatePart("m",[EndLeave]))=[Forms]![Switchboard]![MonthAbbrev]))
I simply cannot account for the days between StartLeave and EndLeave. If the 5th day of a month falls inbetween the StartLeave and EndLeave of 20 employees - I want it to tell me that - and I want to do that for every day in a given month.
Any help would be so much appreciated me...I've spent far too much time on this!!