Need help with creating condition to produce follow-up list in Acc

[Smitee2006]

I have an incident management database with a separate table for tracking
status of file. Users managing the incidents can select a status type such as
followup, pending investigation, and finally, signed off. I want to see a
listing of all matters which don't have a signed off status selected. There
could be multiple status until the signed off choice is made and I want the
followup report or list to only show the most current status like a checklist
for the person managing the incidents.

 

[Duane Hookom]

Can you provide any table and field names? Can we assume the status values
are stored in an incident status history table?

 

[KenSheridan via AccessMonster.com]

Something like this, possibly:

SELECT Incident, Status, StatusDate
FROM Incidents INNER JOIN IncidentStatus AS IS1
ON IncidentID = IS1.IncidentID
WHERE NOT EXISTS
(SELECT *
FROM IncidentStatus AS IS2
WHERE IS2.IncidentID = IS1.IncidentID
AND Status = "Signed Off")
AND StatusDate =
(SELECT MAX(StatusDate)
FROM IncidentStatus AS IS3
WHERE IS3.IncidentID = IS1.IncidentID);

The first subquery restricts the outer query to only those incidents where
there is no 'signed off' row in IncidentStatus. The second subquery further
restricts the outer query to those where the date of the status row is the
latest for that incident.

Ken Sheridan
Stafford, England