0

My Code

I have an Access query:

SELECT DISTINCT [Request-Priority, ALL_Q].RequestID, "Awaiting Approval" AS RequestStatus
FROM [Request-Priority, ALL_Q] LEFT JOIN LIST_RequestBurndown_Q ON [Request-Priority, ALL_Q].RequestID = LIST_RequestBurndown_Q.RequestID
WHERE (((LIST_RequestBurndown_Q.MilestoneStatus)="Awaiting Approval") AND (([Request-Priority, ALL_Q].[Completed on]) Is Null))

UNION

SELECT DISTINCT [Request-Priority, ALL_Q].RequestID, "Blocked (see notes)" AS RequestStatus
FROM [Request-Priority, ALL_Q] LEFT JOIN LIST_RequestBurndown_Q ON [Request-Priority, ALL_Q].RequestID = LIST_RequestBurndown_Q.RequestID
WHERE (((LIST_RequestBurndown_Q.MilestoneStatus)="Blocked (see notes)") AND (([Request-Priority, ALL_Q].[Completed on]) Is Null))

UNION

SELECT DISTINCT [Request-Priority, ALL_Q].RequestID, "Completed - Awaiting Rollout" AS RequestStatus
FROM [Request-Priority, ALL_Q] LEFT JOIN LIST_RequestBurndown_Q ON [Request-Priority, ALL_Q].RequestID = LIST_RequestBurndown_Q.RequestID
WHERE (((LIST_RequestBurndown_Q.MilestoneStatus)="Completed") AND (([Request-Priority, ALL_Q].[Completed on]) Is Null));

The basic concept of the query... based on the status of individual milestones (LIST_RequestBurndown_Q.MilestoneStatus), determine an overall status of a RequestID ([Request-Priority, ALL_Q].RequestID).

My Issue

For some RequestID's, there will be milestones in more than one status. For example, I may get results like:

RequestID   RequestStatus 
123         Awaiting Approval 
243         Blocked 
243         Awaiting Approval 
243         Completed - Awaiting Rollout 
542         Awaiting Approval

What I would like to do is "prioritize" which RequestStatus displays so there is only one record per RequestID.

I've been trying to think of how to do this using ORDER BY, WHERE, DISTINCT ON, or possibly even just creating a table of the possible status options with an associated priority and using MAX - but at this point I'm just overthinking this. Any help would be greatly appreciated!

TMY
  • 471
  • 1
  • 7
  • 20

1 Answers1

1

Suggestion: a table to model the prioritization:

CREATE TABLE RequestStatusPrioritization
( RequestStatus VARCHAR(20) NOT NULL
     REFERENCES RequestStatuses ( RequestStatus ),
  RequestStatusRank INT NOT NULL );

...then join to this table and return the lowest (MIN) rank.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • Yeah, that's basically what I was thinking with respect to my 'associated priority' table that I could then use `MAX` in my query. I was hoping to avoid a table like that if there was a way to do it all with a single line of code in the query though. – TMY Oct 07 '16 at 12:25