rank in Access

Rank in Access Query (Sales Ranking)

A common request I get is for ranking of sales in Access by region, state and item combination for analysis and space management.  In my business, we have some overlap, where a state can be part of two separate areas or regions, for example Southern CA vs. Northern CA.  It’s helpful for us to show separate rankings for each region and state combo, as the results often tell a different story for each.

The Pass-Through query below enables me to determine the ranking at each of these levels.  By entering as a Pass-Through query, you can utilize the Rank and Partition SQL commands.  After running this query, I bring the data into Excel for further analysis, but this query is a good starting point for any similar analyses.

Here is the full Access pass-through query, color coded for the explanation below:

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Explanation

Query 1

Diving right in, since we are measuring at multiple levels, we have some nested queries.  Working from the inside out, my first nested query is this one:

(Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Franchise.Store_monthly_sales m,Franchise.FR_Site_Master s
WHERE m.Site_num = s.Site_num AND accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)

Query 2

We then nest that within this query (the WHERE clause is optional – I am dealing with sales in the thousands, and want to exclude items with a sales quantity less than 5.  Note the Rank() over and Partition By Commands here:

(Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank

FROM

WHERE SumSaleQ >5)

Query 3

The final nesting and main query is selecting the rank from the second query, along with the other pertinent attributes.  The WHERE clause is again optional, I am looking to limit my results to the top 200 results only for each region and state combination. I use 205 as the figure to allow for ties.  The Order by is also optional.  I like to order the results by state, region and then rank ascending.

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank

FROM …

WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Full Query (no color coding)

Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc

Conclusion

I hope this post will help you in your efforts to rank in Access.  It can be a bit of a challenge, but hopefully the breakdown of the steps here will help you achieve your ranking goals.  Please comment any questions, feedback, or what you would like to see next!  Share this post if you feel it would help someone else! 🙂

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Convert Date in Access – Serial Number, Text String, First of Month, etc.

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month.  MS Access will not automatically convert dates into different types, so this post will assist you with doing so manually. You will create a new field in your query to accomplish this. 

Hint: Substitute [yourdate] with the name of the field that you are converting.

To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:

Cdbl ([yourdate])

———————————–

To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:

Cdate ([yourdate])

———————————–

To convert a text or string date to the date/time format, try:

DateValue ([yourdate])

———————————–

To convert a date to the first day of the month:

DateSerial (year([yourdate]),month([yourdate]),1)

———————————-

To convert a date to the last day of the month:

DateSerial (year([yourdate]),month([yourdate])+1,1)-1

———————————-

You can also convert a date/timeformat to a string – here is one example:

Format([yourdate],”mmmmyyyy”)

This would render 7/1/19 in your date field as July2019.

More information on the Format function and syntax can be found on the Microsoft Office Support site.

If I helped you, please consider buying me a coffee via PayPal! Thanks!!

Subscribe for more great content!