Count entries over a specified number of days

mariaw

Registered User.
Local time
Today, 21:02
Joined
Jun 9, 2006
Messages
88
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
 
you mean in any random year, different for each employee?
 
mariaw said:
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.
 
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?
 
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
 
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.
 
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
 
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
 

Users who are viewing this thread

Back
Top Bottom