• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Is there a way to query for the top 25 in MS Access?

I want to create a query that will display the top 25 clients by revenue each month.  Is there a way to do this without having to manually run the query, see what the threshold for 25 is and then re run with a criteria set for all greater than that threshold?
0
pgerman
Asked:
pgerman
  • 2
1 Solution
 
Patrick MatthewsCommented:
Yes:

SELECT TOP 25 Client, Sum(SaleAmt) AS TotSales
FROM tblSales
WHERE Year(SaleDate) = 2009 And Month(SaleDate) = 3
GROUP BY Client
ORDER BY Sum(SaleAmt) DESC
0
 
SharathData EngineerCommented:
Can you proivde some sample set and expected result?
0
 
pgermanAuthor Commented:
the table I am querying has a bunch of fields, many of them labeled as time codes for example 0209 for February 2009.  I want to label a query as 'Top 25 for this time period'  the user enters the time code in a text box on the form.  If the user enters 0209, I would want the query to sum the revenues in 0209 column, so I am trying this:

T25SQL = "SELECT TOP 25 masterdata.[UltP Name], Sum(masterdata.[" & Time_Code_txt & "]) AS [Expr1000] " & _
"FROM masterdata " & _
"GROUP BY masterdata.[UltP Name] " & _
"ORDER BY Sum(masterdata.[" & Time_Code_txt & "]) DESC;"
DoCmd.OpenQuery "Top 25 Clients for this Time Code"
DoCmd.Close acQuery, "Top 25 Clients for this Time Code", acSaveYes

But it is asking to enter a parameter value, as if it is not recognizing what is in the text box
0
 
SharathData EngineerCommented:
i think matthewspatrick has answered your question? Why i asked for sample set is in that query, year and month values are hardcoded. if you want the details for other month or year you need to modify those values and run the query again.
I thought that you want top 25 records like that for each month/year combination in one query. if yes, and you are still looking for solution, then provide some sample set.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now