How to Get a TOP 3 Query 2

[spongie1]

I have had a lot of trouble with this particular query.

Here is what I want...

The 3 largest downtime reasons for each date, shift, machine combination.

I have a table that has these fields:
tbl1ID
date
shift
machine

I have a 2nd table that has these fields:
tbl2ID
downtimeMinutes
downtimeReason
tbl1IDreference


There could be many downtime reasons associated with each date, shift, machine combination.

I want to only show the top 3 for each date, shift, machine combination.

I am a novice. The only way I could think of accomplishing this is by using a union on all combinations of date, shift, and machine. Each union would show the top 3 downtime reasons for that combination.

Each day/shift/machine combination could have 3 different downtime reasons listed.

Could someone show me how to construct a query to do this?

 

[spongie1]

What I mean to say is... is there a way to use nested select statements instead of creating a giant Union table.

I need the process to be automated because there will be way too many entities to put into a union table.

 

[amorous]

Look at this thread to get some idea...

thread701-889001

-VJ

 

[JonFer]

Create a query called qry1:

Select tbl1IDreference,
downtimeReason,
Sum(downtimeMinutes) as downtimeMinutes,
Count(*) as Events
From tbl2
Group By tbl1IDreference,
downtimeReason

Create a new query based on qry1 that uses a subquery in the Where clause:

Select tbl1IDreference,
downtimeReason,
downtimeMinutes,
Events
From qry1
Where (Select Count(*) from qry1 as a
Where a.tbl1IDReference=qry1.tbl1IDReference And
a.Events<qry1.Events)<=2

If there are ties in the number of Events, you'll get an additional record. You could also change the subquery to find the reasons with the highest downtimeMinutes instead of the highest number of Events.

 

[PHV]

I want to only show the top 3
What is the sort criteria (NumberOf or TotalTime)?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[spongie1]

TotalTime is the sort criteria...

I appreciate all your help so far, I may post the query I eventually use along with any follow-up questions... just to make sure that I am on the right track.