How to limit query results

[patriboodood]

Hi Guys,

I have a query which is designed to return the top 10 results based on an "amount" field in my database.

The query I created in MsAccess returns all of the results in the table, so how can I limit this to just the top 10?

Here is the code:

SELECT ChurnRecordTable.[Account Number], ChurnRecordTable.[Account Name], ChurnRecordTable.[Currency Type], ChurnRecordTable.MRR, ChurnRecordTable.Likely, ChurnRecordTable.Reason, ChurnRecordTable!Likely/ChurnRecordTable!MRR AS MRRPercentage, ChurnRecordTable.Month, ChurnRecordTable.Year
FROM ChurnRecordTable
GROUP BY ChurnRecordTable.[Account Number], ChurnRecordTable.[Account Name], ChurnRecordTable.[Currency Type], ChurnRecordTable.MRR, ChurnRecordTable.Likely, ChurnRecordTable.Reason, ChurnRecordTable.Month, ChurnRecordTable.Year
HAVING ((([ChurnRecordTable]![Month])=[Forms]![ChurnDBForm]![MonthSelect]) AND (([ChurnRecordTable]![Year])=[Forms]![ChurnDBForm]![YearSelect]))
ORDER BY ChurnRecordTable.Likely DESC;


Thanks

 

[dhookom]

Is there a good reason why your query is a totals query? There are no aggregates in the SELECT clause.

If you want to limit your query to the top X number of records you can set the TOP property of the query. Also, it doesn't look like you have an "amount" field in your query which is what you would need to sort by.

Duane
Hook'D on Access
MS Access MVP