pgerman
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sharath S
Can you proivde some sample set and expected result?
ASKER
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
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
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.
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.