Top N records per group (1 Viewer)

[TimW]

Hi All.
I have a query that I have created dynamically using VBA
The VBA being:

strSQL = "TRANSFORM nz(Sum([Duration]),0) AS SumOfDuration " & _
            "SELECT " & strGroup & " AS F " & _
            "FROM (SELECT tblDowntime.Reference, tblDowntime.Stopped, tblDowntime.DowntimeStartDate, tblDowntime.DowntimeEndDate, tblDowntime.Cause, Round(([DowntimeEndDate]-[DowntimeStartDate])*1440,0) AS Duration, tblShiftLog.Line, tblShiftLog.reference AS ShiftLogRef " & _
            "FROM tblShiftLog LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference " & _
            "WHERE (((tblDowntime.Stopped)=True) AND ((tblDowntime.DowntimeStartDate) Is Not Null And (tblDowntime.DowntimeStartDate) " & strDateS & ") AND ((tblDowntime.Cause) In ( " & _
            "SELECT " & strReasons & " tblDowntime.Cause " & _
            "FROM tblShiftLog LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference " & _
            "WHERE (((tblDowntime.DowntimeStartDate) Is Not Null And (tblDowntime.DowntimeStartDate) " & strDateS & strShift & ")) " & _
            "GROUP BY tblDowntime.Cause, tblDowntime.Stopped, tblShiftLog.Line " & _
            "HAVING (((tblDowntime.Stopped)=True) " & strCellA & ") " & _
            "ORDER BY Sum(tblDowntime.Downtime) DESC " & _
            "))" & strCellA & _
            "))  AS x " & _
            "WHERE (((X.Stopped) = True)) " & _
            "GROUP BY " & strGroup & _
            " ORDER BY " & strGroup & _
            " PIVOT x.Cause;"

The code works in that it brings the top 5 downtime reasons, for a period of time (grouped, by day or week or month) for the selected lines(cells).
However, if I want to group by lines(or manufacturing cell), I would want to bring back the 5 top downtime reasons per line. Below is the SQL generated by the above VBA

[FONT=Calibri][SIZE=3]TRANSFORM nz(Sum([Duration]),0) AS SumOfDuration [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]SELECT Line  AS F [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]FROM (SELECT tblDowntime.Reference, tblDowntime.Stopped, tblDowntime.DowntimeStartDate, tblDowntime.DowntimeEndDate, tblDowntime.Cause, Round(([DowntimeEndDate]-[DowntimeStartDate])*1440,0) AS Duration, tblShiftLog.Line, tblShiftLog.reference AS ShiftLogRef FROM tblShiftLog [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]WHERE (((tblDowntime.Stopped)=True) AND ((tblDowntime.DowntimeStartDate) Is Not Null And (tblDowntime.DowntimeStartDate) [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]BETWEEN #01/01/2012# AND #09/22/2012#) AND [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]((tblDowntime.Cause) In [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]( SELECT [COLOR=red]TOP 5[/COLOR] tblDowntime.Cause FROM tblShiftLog [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]WHERE (((tblDowntime.DowntimeStartDate) Is Not Null [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]And (tblDowntime.DowntimeStartDate) BETWEEN #01/01/2012# AND #09/22/2012#)) [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]GROUP BY tblDowntime.Cause, tblDowntime.Stopped, tblShiftLog.Line[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]HAVING (((tblDowntime.Stopped)=True) ) ORDER BY Sum(tblDowntime.Downtime) DESC ))))  AS x WHERE (((X.Stopped) = True)) [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]GROUP BY Line  ORDER BY Line  PIVOT x.Cause;[/SIZE][/FONT]

The question is should i try to do a correlated subquery or use vba to bring back one line(or cell) at a time and then union them in a temporary table. Actually I think I need to show the SQL when lines(Cells) are selected, the above returns all the lines(cells).

TRANSFORM nz(Sum([Duration]),0) AS SumOfDuration 
SELECT Line  AS F FROM (SELECT tblDowntime.Reference, tblDowntime.Stopped, tblDowntime.DowntimeStartDate, tblDowntime.DowntimeEndDate, tblDowntime.Cause, Round(([DowntimeEndDate]-[DowntimeStartDate])*1440,0) AS Duration, tblShiftLog.Line, tblShiftLog.reference AS ShiftLogRef 
FROM tblShiftLog LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference 
WHERE (((tblDowntime.Stopped)=True) AND ((tblDowntime.DowntimeStartDate) Is Not Null 
And (tblDowntime.DowntimeStartDate) BETWEEN #05/01/2012# AND #09/22/2012#) 
AND ((tblDowntime.Cause) In ( 
SELECT TOP 5 tblDowntime.Cause FROM tblShiftLog LEFT JOIN (tblProductionTimes LEFT JOIN tblDowntime ON tblProductionTimes.ID = tblDowntime.ProductionTimesReference) ON tblShiftLog.reference = tblProductionTimes.ShiftLogReference 
WHERE (((tblDowntime.DowntimeStartDate) Is Not Null And (tblDowntime.DowntimeStartDate) BETWEEN #05/01/2012# AND #09/22/2012# AND ((tblShiftLog.shift) IN( 'Late', 'Nights')))) 
GROUP BY tblDowntime.Cause, tblDowntime.Stopped, tblShiftLog.Line HAVING (((tblDowntime.Stopped)=True) AND 
((tblShiftLog.Line) IN( '2', '4', '7', '9', '11', '12'))) 
ORDER BY Sum(tblDowntime.Downtime) DESC ))AND ((tblShiftLog.Line) IN( '2', '4', '7', '9', '11', '12'))))  AS x WHERE (((X.Stopped) = True)) GROUP BY Line  ORDER BY Line  PIVOT x.Cause;

Suggestions please :banghead:

Thanks in advance

[BTW this is on a Manfucaturing database we use production Lines or Cells which is why I have used both terms together! I am not refering to a record when I use line above ]

TTFN

TimW