check for repeat events and email alert

[Brigitte P]

I need to check in one table whether the same event has occurred for the same
person 3 times or more within any consecetuve 3 day date period. Once this is
determined, an alert needs to be sent to certain staff. We have Office 2003.
To clarify with an example:
It should show if person with ID 007 had 4 events X between Jan. 1 and Jan 3
(or any other given 3 day period), so ID, Event needs to be the same within 3
days. What stumps me most is the consecetuve 3 days without any data input
from a user. I think the rest could be done with a table duplication and
comparison, but I'm not sure about this either.
My thinking is that this could be run in a query, and once the conditions
are met, a pop up box could be placed on the switchboard of the data entry
person. Once the pop up box shows, an outomatic email alert is sent.
Can anyone tell me how to do this? Thanks.

 

[[MVP] S.Clark]

To count occurances you can use the Group By

Select ID, Count(ID) From Tablename
Group By ID

You may need to break it up into several queries. For example, one query to
extract all of the data for the 3 day period, and then use a GROUP BY query
to total that data.

 

[Brigitte P]

I don't think I made myself clear. I think I need to compare line by line.
For instance, see the following example
1. ID 007 1/1/06 Accident
2. ID 007 1/1/06 Fall
3. ID 008 1/1/06 Fall
4. ID 007 1/1/06 Accident
5. ID 007 1/2/06 Fall
6. ID 007 1/2/06 Accident
7. ID 007 1/3/06 Accident
8. ID 007 1/4/06 Accident
9. ID 007 1/5/06 Fall

With record # 6 ID 007 has 3 of the same events - Accident - and the alert
needs to come up. It then needs to restart counting so it has by record #8
the 3 events counting from 1/2 - 1/4 but ignoring 1/1. It also ignors the
Falls and ID 008 because they don't meet criteria. It stumps me how to keep
on starting the count over again. A total query with a count field of the
dates and events gives me what I need, but I don't know how to start over and
over to meet the criteria of 3 events in any given 3 days for the same ID. Of
course, with user input in a form to get a Between Start Date and End Date,
all would be well, but they want to avoid the user input.
Any help is appreciated.

 

[John Spencer]

UNTESTED AIRCODE that might work. Of course, you need to replace table and
field names with yours.


SELECT ID, IncidentDate, IncidentType
FROM Incident as I
WHERE I.IncidentDate IN

(SELECT Max(I2.IncidentDate)
FROM Incident AS I2
WHERE I2.Incident Date Between I.IncidentDate-2 and I.IncidentDate
AND I2.ID = I.ID
AND I2.IncidentType = I.IncidentType
GROUP BY ID, IncidentType
HAVING COUNT(IncidentType) =3)

If that works the way I think it will it will give give you two records
returned for the same incident appearing 4 consecutive days in a row. That
is it doesn't start over on day 4. I believe that could be worked out, but
I can't do that in my head without a test database.