Count entries over a specified number of days

[mariaw]

Hi...

here's a good one...

I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.

I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).

I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.

How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???

Thanks

Maria

 

[gemma-the-husky]

you mean in any random year, different for each employee?

 

[pdx_man]

Hi...
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.

Please post this query.

 

[mariaw]

HI PDX Man

Sorry I don't know how to post the query .....but i have done it by getting it to count up the number of entries in a table?

 

[mariaw]

Hi Gemma

I need it to be able to do it over a 365 day period, which might be, for example, from May 2005 to June 2006?

Maria

 

[gemma-the-husky]

but do you mean for a given period for everybody at the same time. I sort of took it that you meant to check each person's absence individually over a random period.

If its just testing everybody for the same particular period then its straightforward.

 

[mariaw]

Hi Gemma

Yes, it is testing everybody for a 365 day period, and highlighting the ones where they have been off sick 5 times or more in that (random) 365 day period.

Thanks

Maria

 

[gemma-the-husky]

just have a query with employeeid and startsicknessdate. In the criteria for the date, put

between date1 and date2, (as you require)

change the query to a totals query, and put group by in the employee column, and count in the date column. Voila