Limit # of Records by one field

D

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!
 
D

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.

[MVP] S.Clark said:
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

Dana said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top