Limit # of Records by one field

[Dana]

I have a query that I created and am now trying to create a report. In the
query, there are hundreds of records by different dealer codes. I'd like to
be able to limit the number of records for each dealer code to 10 based on
their total plan count. Is there a formula or something I can use to limit
the records? I'm not very familiar with SQL but have attached my SQL query
below:

SELECT IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))) AS BC, [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, [qry:All Dealers w/o "C"
Prefix].DealerName, [qry:SC Plan Mix].TERM, [qry:SC Plan Mix].PlanCode,
[qry:SC Plan Mix].TYPE, [qry:SC Plan Mix].[TYPE 1], [qry:SC Plan Mix].[OTHER
TYPE], Sum([qry:SC Plan Mix].CountOfPlanCode) AS SumOfCountOfPlanCode
FROM [qry:All Dealers w/o "C" Prefix] INNER JOIN [qry:SC Plan Mix] ON
[qry:All Dealers w/o "C" Prefix].DLRCODE = [qry:SC Plan Mix].DealerCode
GROUP BY IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))), [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, [qry:All Dealers w/o "C"
Prefix].DealerName, [qry:SC Plan Mix].TERM, [qry:SC Plan Mix].PlanCode,
[qry:SC Plan Mix].TYPE, [qry:SC Plan Mix].[TYPE 1], [qry:SC Plan Mix].[OTHER
TYPE]
ORDER BY IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))), [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, Sum([qry:SC Plan
Mix].CountOfPlanCode) DESC;


So, I'd like to be able to display the top 10 records (Sum of
CountOfPlanCode) for each dealer code

Thank You!

 

[[MVP] S.Clark]

Read this:

http://www.mvps.org/access/queries/qry0020.htm

Feel free to post furhter questions.

 

[Dana]

Thanks Steve. It didn't work for me. I actually had to get my database
restored because something went wrong and I kept getting a "Compile" error
when I tried to open the query/report.

Read this:

http://www.mvps.org/access/queries/qry0020.htm

Feel free to post furhter questions.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I have a query that I created and am now trying to create a report. In the
query, there are hundreds of records by different dealer codes. I'd like
to
be able to limit the number of records for each dealer code to 10 based on
their total plan count. Is there a formula or something I can use to
limit
the records? I'm not very familiar with SQL but have attached my SQL
query
below:

SELECT IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))) AS BC, [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, [qry:All Dealers w/o "C"
Prefix].DealerName, [qry:SC Plan Mix].TERM, [qry:SC Plan Mix].PlanCode,
[qry:SC Plan Mix].TYPE, [qry:SC Plan Mix].[TYPE 1], [qry:SC Plan
Mix].[OTHER
TYPE], Sum([qry:SC Plan Mix].CountOfPlanCode) AS SumOfCountOfPlanCode
FROM [qry:All Dealers w/o "C" Prefix] INNER JOIN [qry:SC Plan Mix] ON
[qry:All Dealers w/o "C" Prefix].DLRCODE = [qry:SC Plan Mix].DealerCode
GROUP BY IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))), [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, [qry:All Dealers w/o "C"
Prefix].DealerName, [qry:SC Plan Mix].TERM, [qry:SC Plan Mix].PlanCode,
[qry:SC Plan Mix].TYPE, [qry:SC Plan Mix].[TYPE 1], [qry:SC Plan
Mix].[OTHER
TYPE]
ORDER BY IIf([C_ZONE]="21","21 - E.B.C.",IIf([C_ZONE]="22","22 -
Q.B.C.",IIf([C_ZONE]="25","25 - W.B.C."))), [qry:All Dealers w/o "C"
Prefix].C_DIST, [qry:SC Plan Mix].DealerCode, Sum([qry:SC Plan
Mix].CountOfPlanCode) DESC;


So, I'd like to be able to display the top 10 records (Sum of
CountOfPlanCode) for each dealer code

Thank You!