I am attempting to track employee sick time using a Microsoft Access Database. The purpose of the tracking is to provide reports indicating the number of sick days and the number of sick turn-ins for each employee. (A turn-in is an unbroken sequence of days out sick. The weekend, or a holiday is not considered a break in the sequence. For example, if a person is out sick from Wednesday through Tuesday, their sick days are listed as Wednesday, Thursday, Friday, Monday and Tuesday. This is reported as five sick days and one turn-in. Whereas, if someone is sick on Monday and Tuesday of this week and Thursday and Friday two weeks from now, it would be reported as four sick days and two turn-ins. Coming to work on the days in between caused a break in the sequence and makes the Thursday sick day the start of a new turn-in).

I have a table with the fields "Name" and "Sick Day Date" (the other fields are departmental information) Each sick day is listed as a separate record.

I can easily calculate the number of sick days per employee, but I am at loss on how to calculate the number of Turn-ins.

Can anyone help me?

I have an idea you can try. I assume that periodically (annually?) You will want to pull all of the sick day data for a person and print it to a report.

When you create the report you can query the table for each person, create a recordset of all of their sick day records. step thru the recordset and stick the date (or a Julian date value), and the day of the week. Go to the next record and compare the date then day of the week value (if necessary) with the last record. If it is the next day or the next Monday after a Friday then it is consecutive. Use acouple of incrementing values to keep track of total days, days per turn-in, number of turn-ins, and whatever else you need.

You'll need to throw in some logic to determine if the sick time off spans a holiday or not. I would try something like that and see if I could get it to do what I need.

Thanks!!

I'll give it a try.

Jocat