i just want to number each line in the results of the query and restrict output to where that number is less than 5. why's it gotta be so round-about to arrive at a top N query when you have more than one Grouping?
INSERT INTO Results ( State, MARKET, FCID_MARKET, Violation, [Loss Type], SumOfAmount, [Policy Type], [NPV Viol Sum], [PV Viol Sum], [PV % Total], [NPV % Total], [Count] )
SELECT TOP 5 [Base Info].State, [Base Info].MARKET, [Base Info].[FCID Market], IIf(Forms!Menu.Combo0 Is Null,Null,(IIf(Forms!Menu.Combo3 Like "Yes",Null,[Violation Type]))) AS Violation, IIf(Forms!Menu.Combo3 Like "Yes",[LOSS_TYPE_DESC],(IIf(Forms!Menu.Combo3=No,""))) AS [Loss Type], Sum([Base Info].Amount) AS SumOfAmount, IIf(Forms!Menu.Combo0 Is Null,Null,[Policy]) AS [Policy Type], [Base Info].[NPV Viol Sum], [Base Info].[PV Viol Sum], [Amount]/[PV Viol Sum] AS Expr1, [Amount]/[NPV Viol Sum] AS Expr2, Count(*) AS Expr3
FROM [Base Info]
GROUP BY [Base Info].State, [Base Info].MARKET, [Base Info].[FCID Market], IIf(Forms!Menu.Combo0 Is Null,Null,(IIf(Forms!Menu.Combo3 Like "Yes",Null,[Violation Type]))), IIf(Forms!Menu.Combo3 Like "Yes",[LOSS_TYPE_DESC],(IIf(Forms!Menu.Combo3=No,""))), IIf(Forms!Menu.Combo0 Is Null,Null,[Policy]), [Base Info].[NPV Viol Sum], [Base Info].[PV Viol Sum], [Amount]/[PV Viol Sum], [Amount]/[NPV Viol Sum]
HAVING ((([Base Info].State)="GA") AND (([Base Info].MARKET)="GA BUS") AND (([Base Info].[FCID Market])="GA CTR") AND ((IIf([Forms]![Menu].[Combo0] Is Null,Null,[Policy])) Like [Forms]![Menu].[Combo0])) OR ((([Base Info].State)="GA") AND (([Base Info].MARKET)="GA BUS") AND (([Base Info].[FCID Market])="GA CTR") AND (([Forms]![Menu].[Combo0]) Is Null))
ORDER BY Sum([Base Info].Amount) DESC;