Date grouping question!!

[edioguardi]

I'm working on a database to start tracking my Company's employees Sick Leaves & FMLA's.

I have a "Leaves" table with Employee Numbers, Start Date End Date, Type & FMLA Period. No primary key, and have not entered the FMLA Period for each entry- only the very first for each employee.

After the Employee submits their 1st FMLA a 12-month clock starts, in which the employee may take a total of 60 working days off. After the 12 months has expired, the next FMLA submitted will start the next 12-month period.

I have a query that will determine the number of working days off for each FMLA, but I need to be able to:
1. sum the days used within the current 12 month period (not current Date - 12 months)

2. automatically increase the FMLA period by 1, when the first FMLA is submitted 12 or more months after the start date of the first FMLA of the previous period.

Any help is appreciated

 

[kathryn]

If I understand you, your FMLA period contains an integer such as 1 which indicates which FMLA period the employee is in. Instead, how about changing that to the FMLA Period Start Date? You could then count how many distinct start dates any employee had if you needed the number of FMLA periods they have had.

Using this method, when you enter new data for an employee, you can have code check the latest value for the employee in the FMLA Period Start Date. If this date is less than 12 months previous, copy it into the current record. If it is greater, copy the Start date from the current record into the FMLA Period Start Date.

Does that make sense? Kathryn

 

[edioguardi]

Well, I kinda followed your advice. I changed the periods number to a Period start date. I based all the time used/available calculations for all records whose period Start date = MaxofFMLA Period Start. Works great, but I don't know how to write the code for checking the period start date vs. the current FMLA start date.

Here's how the forms work
Main Form - Provides Employee Name, number, job desription... with a subdatasheet displaying all leaves for that employee. the datasheet will be locked against editing. If the user wants to add a new leave they click a command button to open another form which carries over the general employee information and the Max of FMLA Period Start Date. This is editable, so the user may have some licence on starting the next period if necessary.

I'm not very good with code just yet. How would I write the code to check if the start date is within 1 year of the period start date?

Also- I have a query based form which will calculate the number of days used/available, but the new or edited record must be saved for Query to include that record in the calculation. What I would be looking for ideally is a warning, before saving, that will tell the user an entry will exceed the 60 working days, and if so, will produce the last possible date that the FMLA can be extended to.

Also #2- the user can chose to edit a leave for an employee by clicking another command button and opening a different form. I have the form working to filter out only the leaves associated with that employee - where would I have to go to specify only the max record (most recent)?

Thank You so much for your help.

Elizabeth

 

[kathryn]

How would I write the code to check if the start date is within 1 year of the period start date?

In the BeforeUpdate event of the field where the start date is entered, you could enter code to check the difference between the two dates. If the difference is greater than one year, you would plug the entered start date into the FMLA Period Start Date.

For the days available query, again, use the Before Update event. Maybe you would use the ending date and pass that value to a query?

For the "I have the form working to filter out only the leaves associated with that employee - where would I have to go to specify only the max record (most recent)?" can you post the wording of the filter? Kathryn