TMY TMY - 1 month ago 7
SQL Question

Access SQL Query / Expression to Compare Two Records in Same Table for SCRUM

I've been banging my head against the wall on this one for days. I feel like this should be simple, but I keep finding loop holes in all the queries I create.

Example Data



Long story short, through a series of queries, I have generated a data set similar to the example below:

RequestID Status
45 Completed
87 Completed
87 Acknowledged
87 In Progress
87 Blocked
99 New


Example Results



The next step for me is running a query on this data set so only ONE SCRUM
Status
remains for each
RequestID
based on the following parameters. Thus for the example above, I would want my result to show:

RequestID Status
45 Completed
87 Blocked
99 New


Logic Tree



Here is my UML logic tree that I can't seem to figure out how to translate into Access (please note: I'm just showing the entire logic tree for future readers. For my troubleshooting, Case 1, 2, 4 are the only unique logic expressions that I need help translating into an Access SQL query)...

All cases below are based on a per
RequestID
basis.



  1. IF any record of a specific
    RequestID
    has a
    Status
    ="Blocked"; resulting
    Status
    is ="Blocked".

  2. IF any record of a specific
    RequestID
    has a
    Status
    ="Awaiting Approval" AND no records for that specific
    RequestID
    has a
    Status
    is ="Blocked"; resulting
    Status
    is ="Awaiting Approval".

  3. IF any record of a specific
    RequestID
    has a
    Status
    ="In Progress" AND no records for that specific
    RequestID
    has a
    Status
    is ="Blocked" AND no records for that specific
    RequestID
    has a
    Status
    is ="Awaiting Approval"; resulting
    Status
    is ="In Progress".

  4. IF any record of a specific
    RequestID
    has a
    Status
    ="Completed" AND there ARE records for that specific
    RequestID
    has a
    Status
    is ="Acknowledged"... AND no records for that specific
    RequestID
    has a
    Status
    is ="Blocked" AND no records for that specific
    RequestID
    has a
    Status
    is ="Awaiting Approval"; resulting
    Status
    is ="In Progress".

  5. IF any record of a specific
    RequestID
    has a
    Status
    ="Acknowledged" AND no records for that specific
    RequestID
    has a
    Status
    is ="Blocked" AND no records for that specific
    RequestID
    has a
    Status
    is ="Awaiting Approval" AND no records for that specific
    RequestID
    has a
    Status
    is ="Completed" AND no records for that specific
    RequestID
    has a
    Status
    is ="In Progress"; resulting
    Status
    is ="Acknowledged".

  6. IF any record of a specific
    RequestID
    has a
    Status
    ="New" AND no records for that specific
    RequestID
    has a
    Status
    is ="Blocked" AND no records for that specific
    RequestID
    has a
    Status
    is ="Awaiting Approval" AND no records for that specific
    RequestID
    has a
    Status
    is ="Completed" AND no records for that specific
    RequestID
    has a
    Status
    is ="In Progress" AND no records for that specific
    RequestID
    has a
    Status
    is ="Acknowledged"; resulting
    Status
    is ="New".



Troubleshooting



Since I'm comparing multiple rows in the table maybe it requires a
self-join
? Or maybe it can be done with an IIF statement for the resulting
Status
field? I don't know. Every attempt I make seems to fail, so at this point I'd really love someone's insight regarding how I can do this in Access!

Any help is greatly appreciated.

Answer
SELECT RequestID, 
Min(IIf([status]="Blocked",Yes)) AS B, 
Min(IIf([status]="Awaiting approval",Yes)) AS W, 
Min(IIf([status]="In Progress",Yes)) AS IP, 
Min(IIf([status]="Completed",Yes)) AS C, 
Min(IIf([status]="acknowledged",Yes)) AS Ack, 
Min(IIf([status]="New",Yes)) AS N, 
Switch([B],"Blocked",[W],"Awaiting Aproval",[IP],"In Progress",[C] And [Ack],"In Progress",[Ack],"Acknowledged",[N],"New") AS Result
FROM Status
GROUP BY RequestID;

This doesn't deal with the question above - it leaves the result blank for Completed but not Acknowledged as blank - but you should be able to modify the logic once you get the rules defined better.

Comments