Totals Query Help (I Think ...) (1 Viewer)

[padlocked17]

Access Gurus,

I have two tables that contain the data I need.

Tables


Results


Gotchas:
-(Order) is the sequence in which the steps (PRStepID) progress sequentially

End Goal:
Return the (PRStepID) with the Lowest (Order) grouped on the (ReportID) Where the (CompletedDate) Is null.

If the (PRStepsID) with an (Order) of 1,2 & 3 all have (CompletedDate)'s, I would like to return the (PRStepID) for that (ReportID) that has an (Order) of 4.

End Result would look like:

ReportID....... PRStepID
1..................21
2..................20
3..................20


Any help would be greatly appreciated.

 

[plog]

You are going to need a subquery to identify the lowest order of each ReportId. This is that subquery:

SELECT ReportID, MIN(Order) As LowestOrder
FROM NextStep1
WHERE IsNull(CompletedDate)
GROUP BY ReportID;

Paste that into a query and run it. It will give you the lowest order for every PRStepId. Save the query then build a new query based on it and NextStep1, link them by appropriate fields and bring down the data you want.