Prev: Multiple Values in a Query Criteria
Next: Why does Access 2007 save query changes w/o prompting?
From: Smitee2006 on 28 Feb 2010 17:23
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.
From: Duane Hookom on 28 Feb 2010 18:16
Can you provide any table and field names? Can we assume the status values
are stored in an incident status history table?

--
Duane Hookom
Microsoft Access MVP


"Smitee2006" wrote:

> 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.
From: KenSheridan via AccessMonster.com on 28 Feb 2010 18:49
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

Smitee2006 wrote:
>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

 | 
Pages: 1
Prev: Multiple Values in a Query Criteria
Next: Why does Access 2007 save query changes w/o prompting?