CODEWITHSUNDEEP
vbams-accessdistinctdistinct-on
TMY
TMY

Reputation: 469

Access - Select Distinct based on Field Value

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!

Upvotes: 0

Views: 133

Answers (1)

onedaywhen
onedaywhen

Reputation: 57093

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.

Upvotes: 1

Related Questions