Top 5 per year....

GUIDO22

Registered User.

Local time

Today, 09:55

Joined

Nov 2, 2003

Messages

515

• Nov 4, 2015

• #1

I have the following SQL query...

SELECT TOP 5 SumOfTotal, Year, Customer
FROM Reporting_MonthlyTotalSales
GROUP BY Reporting_MonthlyTotalSales.SumOfTotal, Reporting_MonthlyTotalSales.Year, Reporting_MonthlyTotalSales.Customer
ORDER BY Reporting_MonthlyTotalSales.SumOfTotal DESC

This returns to me the TOP 5 highest annual SALES totals, by CUSTOMER ie.
1999 - 200,000 Cust 1
1999 - 180,000 Cust 25
2002 - 175,000 Cust 3
2010 - 130,000 Cust 49
2004 - 120,000 Cust 1

But what I actually want is : the TOP 5 sales totals, for EACH YEAR.

Help gratefully received, thank you.

 

J

JHB

Have been here a while

Local time

Today, 10:55

Joined

Jun 17, 2012

Messages

7,732

• Nov 4, 2015

• #2

The link gives a description how to do it, (1. example).
https://support.microsoft.com/en-us/kb/153747

 

GUIDO22

Registered User.

Local time

Today, 09:55

Joined

Nov 2, 2003

Messages

515

• Nov 4, 2015

• #3

Yes, I had found this myself previously but although I got it to work with NWIND.MDB, could not get it to work with my DB....
See pic attached..

When I run it.... it 'timesout'.. appears to be in a permanent loop... I have to Ctrl+Break to quit out of processing...

 

Attachments

• 
  query_pic.jpg

  105.7 KB · Views: 152

jdraw

Super Moderator

Staff member

Local time

Today, 04:55

Joined

Jan 23, 2006

Messages

15,571

• Nov 4, 2015

• #4

You may find this post, and the original question identified within, useful.

Good luck.

 

GUIDO22

Registered User.

Local time

Today, 09:55

Joined

Nov 2, 2003

Messages

515

• Nov 4, 2015

• #5

Thanks but having tried various things most of which either wont run or crash Access, am having no luck at all. I dont usually get despondent but this is bugging me. (The database isnt massive either.....so its isnt the sheer number of records causing the crash)

Any Access gurus able to work out a solution for this seemingly 'simple' (at least thats what the boss thinks it is...), problem would be really appreciated...

Thank you.

 

jdraw

Super Moderator

Staff member

Local time

Today, 04:55

Joined

Jan 23, 2006

Messages

15,571

• Nov 4, 2015

• #6

I mocked up your data, changed a few names, created 200 records with random data and then ran the query below:

Code:

SELECT *
FROM (SELECT   a1.customer, a1.Year_, a1.SumOfTotal, COUNT(*) AS CategoryRank 
FROM [Reporting_monthlyTotalSales] AS a1 INNER JOIN [Reporting_monthlyTotalSales] AS a2 
ON (a1.year_ = a2.Year_) AND (a1.SumOfTotal<= a2.SumOfTotal) 
GROUP BY a1.SumOfTotal, a1.Year_,a1.customer
)  AS RankingQuery
WHERE (((RankingQuery.CategoryRank)<=5))
ORDER BY RankingQuery.year_, RankingQuery.CategoryRank, RankingQuery.SumOfTotal

I am including my sample database and a jpg of sample output.

Good luck.

 

Attachments

• Top5_ReportingDemo.accdb

  492 KB · Views: 152
• 
  Top5_ReportingSampleOutput.jpg

  62.4 KB · Views: 187

Last edited: Nov 4, 2015

GUIDO22

Registered User.

Local time

Today, 09:55

Joined

Nov 2, 2003

Messages

515

• Nov 5, 2015

• #7

I mocked up your data, changed a few names, created 200 records with random data and then ran the query below:
.....

Good luck.

Click to expand...

Sir, you are a genius, thank you for taking the time.
It appears I wasnt 'miles off' with my efforts : having tried the RANKING query from the suggested thread myself, looking at yours ... it seems I was missing the crucial ....

WHERE (((RankingQuery.CategoryRank)<=5))

Works a treat, thanks again!

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 09:55

Joined

Feb 19, 2013

Messages

17,752

• Nov 5, 2015

• #8

also posted and answered here

http://www.accessforums.net/queries/top-5-per-year-56217.html