Top N values per category

Posted on 2003-02-24
Medium Priority
Last Modified: 2010-05-18
I'm using Access 97 and I'm trying to creat a query that gives me the top N values per category for a very simple table I have.  The table contains one row per day per city, and what I need is to be able to pick the top 2 Total values per city for a certain period of time (say a week, or a month).  So for example, I would have 7 Total values per city for a week, and I would like to keep the top 2 (not summed, just the 2 rows per city).  The table structure is as follows:

Date     City     Total
...     ...     ...

I have looked everywhere and the only thing I found was how to create a top values per group report (Microsoft Knowledge Base), but this gives me a report, not a query.  Any help will certainly be appreciated.
Question by:LizzyB
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 8011484
Hum, must have missed it in the MSKB:

ACC: How to Create a Top N Values per Group Query

ACC2000: How to Create a "Top N Values Per Group" Query


Author Comment

ID: 8018499
Ok, I knew I was short-sighted, but I didn't think I was BLIND!!  Jim, you get the points for finding it for me, thanks sooooo much!

Author Comment

ID: 8018510
Short and to the point.  Did I mention QUICK !?!?

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 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