How do I query the top X of something?

=========
QUESTION
———
I have TABLE-A with 19000 records in it.
Each record contains REGION, DISTRICT, STORE, NAME, and that person’s SALES
How do I find the top sales per Region, or per District, or per Store, etc…

 

=========
ANSWER
———
OPTION 1

All it takes is three simple MAKE TABLE* queries:
In this example, we will find the top SALES per STORE

*NOTE: Query 1 and 2 MUST be MAKE TABLE queries for this to work!!


QUERY1: (MUST be a MAKE TABLE query)

Query TABLE-A to make table* TABLE-A1
Sort STORE ascending
Sort SALES descending

This gets us the table sorted by top Sales


QUERY2: (MUST be a MAKE TABLE query)

Query TABLE-A1 to make table* Table-A2
Sort STORE ascending
Sort SALES descending

Turn on Grouping
GROUP BY every field except SALES
Set SALES to FIRST

This gets us the top SALES amount per NAME in each STORE


QUERY3:

Query Table-A2 to make table Table-A3
Sort STORE ascending
Sort SALES descending

Turn on Grouping
GROUP BY: REGION, DISTRICT, STORE
FIRST: all other fields – !! SET ALL OTHER FIELDS TO FIRST !!

This then simply strips off the top SALES associate per STORE and writes it out to Table-A3

 

———
OPTION 2 – !! BEST OPTION !!

This query MUST be against TABLES — NOT other QUERIES!!


Find the Store with the Top Sales per District:

Create a new Query
Add the Districts table, and the Sales Table
Link (1 to 1 relationship) the two tables on the District field

Add the following fields to the grid:

Districts.District
Sort: Ascending

Sales.Store
Sort: (not sorted)

Sales.TtlSales
Sort: Descending
Criteria:
In (Select Top 5

[TtlSales] From [Sales] Where [Sales].[DISTRICT]=[Districts].[DISTRICT] Order By [TtlSales] Desc)

Run the query.

 

=========
REF
———
Option 1

Many thanks to our friend and colleague George Schick on this one!

———
Option 2
http://support.microsoft.com/kb/210039

———
Some other options we have found as well:
http://support.microsoft.com/kb/153747/en-us

——— 
http://www.anysitesupport.com/vba-rnd-function-pick-a-random-number/

 

=========
APPLIES TO / KEYWORDS
———
Microsoft Access
Queries
Query the Top N of something


http://www.anysitesupport.com/how-do-i-query-the-top-x-of-something/

By | 2017-12-01T23:47:51+00:00 April 7th, 2011|Access, Documentation, Microsoft|0 Comments