If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with creating condition to produce follow-up list in Acc
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. |
#2
|
|||
|
|||
Need help with creating condition to produce follow-up list in Acc
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. |
#3
|
|||
|
|||
Need help with creating condition to produce follow-up list in Acc
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/For...eries/201002/1 |
Thread Tools | |
Display Modes | |
|
|