Top 5 per year....

[GUIDO22]

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.

 

[JHB]

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

 

[GUIDO22]

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...

 

[jdraw]

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

Good luck.

 

[GUIDO22]

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]

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

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.

 

[GUIDO22]

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

Good luck.

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!

 

[CJ_London]

also posted and answered here

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