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

Posted on 2009-04-23
Last Modified: 2013-11-28
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?
Question by:pgerman
    LVL 92

    Accepted Solution


    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
    LVL 40

    Expert Comment

    Can you proivde some sample set and expected result?

    Author Comment

    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
    LVL 40

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now