Query criteria help

[BLGIRL311]

I am trying to track absences at work. They are written up if they meet the
following criteria.

3 in 30 days
4 in 90 days
7 in 6 months

Im having trouble figureing out the criteria to pull the correct data.

Thanks

 

[KARL DEWEY]

Post sample data with table and field names indicating datatype.

 

[BLGIRL311]

I manually type in a form I created the persons name and date they were absent.
I want to be able to run a query, based by the date field (If possible),
meeting the criteria below.

Has anyone had 3 absences with the last 30 days, 4 in the last 90, or 7 in
the past 6 months.

 

[John Spencer MVP]

One solution is an SQL statement for such a query that might look like:

SELECT PersonName
, Count(AbsentDate) as HalfYearCount
, Count(IIF(AbsentDate>=DateAdd("d",-90,Date()),1,Null) as 90DayCount
, Count(IIF(AbsentDate>=DateAdd("d",-30,Date()),1,Null) as 30DayCount
FROM [YourAbsenceTable}
WHERE AbsentDate Between DateAdd("M",-6,Date()) and Date()
GROUP BY PersonName
HAVING Count(AbsentDate)<=7
, Count(IIF(AbsentDate>=DateAdd("d",-90,Date()),1,Null) >= 4
, Count(IIF(AbsentDate>=DateAdd("d",-30,Date()),1,Null) >= 3

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

I manually type in a form I created the persons name and date they were
absent.
We are talking about Access here and not Excel aren't we?
You can not run a query on a form, it must pull the data from a table.
I sked you to 'Post sample data with table and field names indicating
datatype.'

Do I need to rephrase the statement?

 

[BLGIRL311]

I was under the impression that this was a forum for people to help each
other.If your not a person capable of that then move on, I will get help else
where. And
no you do not need to rephrase the question, Im not a moron, don't talk to
me like Im one.

Field Name: Employee_ID, Data Type: Text
Field Name: Hours, Data Type: Number
Field Name: Date_Occurrence, Data Type: Date/Time

Sample: 006394, 8, 4/11/09

 

[KARL DEWEY]

Try this --
SELECT BLGIRL311.Employee_ID, Count(BLGIRL311.Employee_ID) AS [Number of
Absences]
FROM BLGIRL311
WHERE (((BLGIRL311.Date_Occurrence) Between Date() And Date()-29)) OR
(((BLGIRL311.Date_Occurrence) Between Date() And Date()-89)) OR
(((BLGIRL311.Date_Occurrence) Between Date() And DateAdd("m",-6,Date())))
GROUP BY BLGIRL311.Employee_ID
HAVING (((Count(BLGIRL311.Employee_ID))>=3)) OR
(((Count(BLGIRL311.Employee_ID))>=4)) OR
(((Count(BLGIRL311.Employee_ID))>=7));

 

[BLGIRL311]

Thank you for your help. I will try to work with this.

One solution is an SQL statement for such a query that might look like:

SELECT PersonName
, Count(AbsentDate) as HalfYearCount
, Count(IIF(AbsentDate>=DateAdd("d",-90,Date()),1,Null) as 90DayCount
, Count(IIF(AbsentDate>=DateAdd("d",-30,Date()),1,Null) as 30DayCount
FROM [YourAbsenceTable}
WHERE AbsentDate Between DateAdd("M",-6,Date()) and Date()
GROUP BY PersonName
HAVING Count(AbsentDate)<=7
, Count(IIF(AbsentDate>=DateAdd("d",-90,Date()),1,Null) >= 4
, Count(IIF(AbsentDate>=DateAdd("d",-30,Date()),1,Null) >= 3

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I manually type in a form I created the persons name and date they were absent.
I want to be able to run a query, based by the date field (If possible),
meeting the criteria below.

Has anyone had 3 absences with the last 30 days, 4 in the last 90, or 7 in
the past 6 months.