Query Help

[Guest]

I have inherited an Access Database that manages staff absence. Field
headers include Staff Number (the unique identifier), Forename, Surname, 1st
Day of Absence, Last Day of Absence and a field that automatically calculates
the difference between the 1st and Last Day of Absence (DateDiff).

The database was built to work out how many times individual staff memebers
were absent over a rolling 6 month period, what caused their absence and
whether it was self-certified or medically certified.

The database works well enough in that it identifies and give a total of
days absent on an individual basis but what it really needs to do is give a
total period of absence for individuals who have been:

a. Absent more than once in the 6 month period.

b. Have had two episodes of absence totalling >=14 days.

c. Absent for >=3 episodes in the rolling 6 month period.

d. Have a total of all absences that is >=28 days

Can anyone help with suggestions for how I put this into a query?

Thanks

 

[John Spencer]

Do you need all this in one query? Your criteria are somewhat
contradictory. For instance if a person is absent more than once in 6 month
period, then all the other criteria are moot. If

How do you define "rolling" 6 month period? Do you mean that you specify a
start date or end date for the 6 month period? Or do you want to use the
system date and get the report for the period between 6 months ago and
today?

Also, if a person is absent from 11/29 to 12/15, how do you count there
absence. Are they absent in November or absent in December or absent in
both? If in both, do you count them absent in November for 2 days and
absent in December for 15 days?

Assumptions:
-- a field named CalculatedField for the number of days absent in each
episode
-- absences are counted as of the end date
-- you are specifying the date range as the prior six months

SELECT [Staff Number], ForeName, SurName, Sum(CalculatedField) as TotalDays
, Count([Last Day of Absence]) as NumberAbsences,
, Abs(Sum(CalculatedField>=14)) As AbsencesOver14Days
, Abs(Sum(CalculatedField>=14)) > 1 as 14DaysTwiceOrMore
, Count([Last Day of Absence]) >2 as MoreThan2Absences
, Sum(CalculatedField) >27 as MoreThan27Days
FROM YourTable
WHERE [Last Day of Absence] Between DateSerial(Year(Date()),
Month(Date())-6,1)
AND DateSerial(Year(Date()), Month(Date()),0)
GROUP BY [Staff Number], ForeName, SurName
Having Count([Last Day of Absence]) > 1

 

[Guest]

Thanks very much for your help. I am most grateful as new to Access Database
work.

Rolling 6 month date is set using "Bewtween Date() And DateAdd(M,-6,Date()).
Absence is not month bound, just how many days did an individual take in any
period of absence; ie. between [Date1] (First Day of Absence) 30/04/06 and
[Date2] (Day Returned to Work) 06/05/06 = 6 days away.
--
R J Etheridge

Do you need all this in one query? Your criteria are somewhat
contradictory. For instance if a person is absent more than once in 6 month
period, then all the other criteria are moot. If

How do you define "rolling" 6 month period? Do you mean that you specify a
start date or end date for the 6 month period? Or do you want to use the
system date and get the report for the period between 6 months ago and
today?

Also, if a person is absent from 11/29 to 12/15, how do you count there
absence. Are they absent in November or absent in December or absent in
both? If in both, do you count them absent in November for 2 days and
absent in December for 15 days?

Assumptions:
-- a field named CalculatedField for the number of days absent in each
episode
-- absences are counted as of the end date
-- you are specifying the date range as the prior six months

SELECT [Staff Number], ForeName, SurName, Sum(CalculatedField) as TotalDays
, Count([Last Day of Absence]) as NumberAbsences,
, Abs(Sum(CalculatedField>=14)) As AbsencesOver14Days
, Abs(Sum(CalculatedField>=14)) > 1 as 14DaysTwiceOrMore
, Count([Last Day of Absence]) >2 as MoreThan2Absences
, Sum(CalculatedField) >27 as MoreThan27Days
FROM YourTable
WHERE [Last Day of Absence] Between DateSerial(Year(Date()),
Month(Date())-6,1)
AND DateSerial(Year(Date()), Month(Date()),0)
GROUP BY [Staff Number], ForeName, SurName
Having Count([Last Day of Absence]) > 1

I have inherited an Access Database that manages staff absence. Field
headers include Staff Number (the unique identifier), Forename, Surname,
1st
Day of Absence, Last Day of Absence and a field that automatically
calculates
the difference between the 1st and Last Day of Absence (DateDiff).

The database was built to work out how many times individual staff
memebers
were absent over a rolling 6 month period, what caused their absence and
whether it was self-certified or medically certified.

The database works well enough in that it identifies and give a total of
days absent on an individual basis but what it really needs to do is give
a
total period of absence for individuals who have been:

a. Absent more than once in the 6 month period.

b. Have had two episodes of absence totalling >=14 days.

c. Absent for >=3 episodes in the rolling 6 month period.

d. Have a total of all absences that is >=28 days

Can anyone help with suggestions for how I put this into a query?

Thanks

 

[John Spencer]

SELECT [Staff Number], ForeName, SurName
, Sum(CalculatedField) as TotalDays
, Count([Last Day of Absence]) as NumberAbsences,
, Abs(Sum(CalculatedField>=14)) As AbsencesOver14Days
, Abs(Sum(CalculatedField>=14)) > 1 as 14DaysTwiceOrMore
, Count([Last Day of Absence]) >2 as MoreThan2Absences
, Sum(CalculatedField) >27 as MoreThan27Days
FROM YourTable
WHERE [Last Day of Absence] Between DateAdd("M",-6,Date()) And Date()
GROUP BY [Staff Number], ForeName, SurName
Having Count([Last Day of Absence]) > 1

You still haven't answered my questions.
If today were July 1 and someone was absent from January 29 to Feb 3, should
that absence be included in the query results?
If so, do you count Jan 29, 30, and 31 in the total number of days absent or
just Feb 1,2, and 3?

In other words, when calculating figures, are these the questions
How many absences ended in the Last 6 months?
--Of those, how many total days were involved
--Of those, how many absences wer there
--Of those, how many were over 14 days in length
--Of those, were there two or more over 14 days in length
--Of those, how many were over 28 days or more in length
--Of those, were there 3 or more absences

IF so, then the above query should answer your question. You just need to
replace the fieldnames and tablename with YOUR fieldnames and table name.
Calculated field will have to be replaced with the name of the field that
has the number of days for each absence or you will need to substitute a
formula (such as [Last day of Absence] - [First Day of Absence] + 1 ) for
the reference.

Thanks very much for your help. I am most grateful as new to Access
Database
work.

Rolling 6 month date is set using "Bewtween Date() And
DateAdd(M,-6,Date()).
Absence is not month bound, just how many days did an individual take in
any
period of absence; ie. between [Date1] (First Day of Absence) 30/04/06
and
[Date2] (Day Returned to Work) 06/05/06 = 6 days away.
--
R J Etheridge

Do you need all this in one query? Your criteria are somewhat
contradictory. For instance if a person is absent more than once in 6
month
period, then all the other criteria are moot. If

How do you define "rolling" 6 month period? Do you mean that you specify
a
start date or end date for the 6 month period? Or do you want to use the
system date and get the report for the period between 6 months ago and
today?

Also, if a person is absent from 11/29 to 12/15, how do you count there
absence. Are they absent in November or absent in December or absent in
both? If in both, do you count them absent in November for 2 days and
absent in December for 15 days?

Assumptions:
-- a field named CalculatedField for the number of days absent in each
episode
-- absences are counted as of the end date
-- you are specifying the date range as the prior six months

SELECT [Staff Number], ForeName, SurName, Sum(CalculatedField) as
TotalDays
, Count([Last Day of Absence]) as NumberAbsences,
, Abs(Sum(CalculatedField>=14)) As AbsencesOver14Days
, Abs(Sum(CalculatedField>=14)) > 1 as 14DaysTwiceOrMore
, Count([Last Day of Absence]) >2 as MoreThan2Absences
, Sum(CalculatedField) >27 as MoreThan27Days
FROM YourTable
WHERE [Last Day of Absence] Between DateSerial(Year(Date()),
Month(Date())-6,1)
AND DateSerial(Year(Date()), Month(Date()),0)
GROUP BY [Staff Number], ForeName, SurName
Having Count([Last Day of Absence]) > 1

I have inherited an Access Database that manages staff absence. Field
headers include Staff Number (the unique identifier), Forename,
Surname,
1st
Day of Absence, Last Day of Absence and a field that automatically
calculates
the difference between the 1st and Last Day of Absence (DateDiff).

The database was built to work out how many times individual staff
memebers
were absent over a rolling 6 month period, what caused their absence
and
whether it was self-certified or medically certified.

The database works well enough in that it identifies and give a total
of
days absent on an individual basis but what it really needs to do is
give
a
total period of absence for individuals who have been:

a. Absent more than once in the 6 month period.

b. Have had two episodes of absence totalling >=14 days.

c. Absent for >=3 episodes in the rolling 6 month period.

d. Have a total of all absences that is >=28 days

Can anyone help with suggestions for how I put this into a query?

Thanks