Ranking Sales and pulling only top 100

[nah225]

Is there a way to pull only the top 100 (or whatever #) items by sales $ within my query? I have the sales $ listed in descending order, but there are over 10000 items and rather than manually pull out the top 100, I'd like to have it automatically.

I don't want it to necessarily rank them to where I get 1,2,2,4, but rather 1,2,3,4....100.

TI hope this is clear -thanks!

 

[SydneyGeek]

In your query where the sales are ranked, make sure they are sorted Descending -- this gives biggest values first.
Now, still in Design view, there is a Top Values box just under the toolbars. Enter 100 in the box, press Enter, adn you're done.

Denis

 

[boprat]

What if you wanted to take the query a step further and report the top 100 within each and every state????

 

[SydneyGeek]

Good question!

I don't think it's possible in a query but it can be achieved in a report. You'll need to set the report to group by State and sort descending by Value. This code comes from the Microsoft Knowledge Base --

Option Compare Database 'Use database order for string comparisons.
Option Explicit
Global TotCount As Integer

' Call the SetCount() function from the group header section's
' OnPrint property using the syntax: =SetCount(Report)

Function SetCount (R As Report)
   TotCount = 0
   R![ProductName].Visible = True
End Function

' Call the PrintLines() function from the detail section's OnPrint
' property using the syntax: =PrintLines(Report,[TotGrp]).

Function PrintLines (R As Report, TotGrp)
   TotCount = TotCount + 1
   If TotCount = TotGrp Then
        R.NextRecord = False
   ElseIf TotCount > TotGrp And TotCount < 15 Then
        R.NextRecord = False
        R![ProductName].Visible = False
   End If
End Function

You'll notice this row:ElseIf TotCount > TotGrp And TotCount < 15 Then which limits the # of rows pre group to 15. Try changing the number. Here's the link to the full article: http://support.microsoft.com/default.aspx?scid=kb;en-us;210350

Denis