I am creating a query listing top 6 part numbers grouped by task number, ascending by task no and descending by quantity. I came across a prior thread and was trying to format to suit my needs with no luck. Was hoping someone could give me some insight. Thanks in advance
SELECT a.Task, a.Part_No, a.SumOfQuantity, a.CountOfPart_no
FROM Qry_Part_Qty_By_Task AS a
WHERE (((a.CountOfPart_No)>0)
AND (((SELECT count (*) FROM Qry_Part_Qty_By_Task
WHERE [CountOfPART_NO] >0
AND [Task]=a.[Task]
AND [SumOfQuantity] =a.[SumOfQuantity]
AND [Part_No] =a.[Part_No]
AND [CountOfPART_NO]>a.[CountOfPart_No] ))<6))
ORDER BY a.Task, a.SumOfQuantity DESC;
SELECT a.Task, a.Part_No, a.SumOfQuantity, a.CountOfPart_no
FROM Qry_Part_Qty_By_Task AS a
WHERE (((a.CountOfPart_No)>0)
AND (((SELECT count (*) FROM Qry_Part_Qty_By_Task
WHERE [CountOfPART_NO] >0
AND [Task]=a.[Task]
AND [SumOfQuantity] =a.[SumOfQuantity]
AND [Part_No] =a.[Part_No]
AND [CountOfPART_NO]>a.[CountOfPart_No] ))<6))
ORDER BY a.Task, a.SumOfQuantity DESC;
