Limiting Records Retrieved To 10... With A Catch.

Hello all,

I am a buyer and I'm attempting to create a query in Access 2003 that will list all of my vendors (20 in total) and their top 10 selling sku's. Here are the fields I am using:

Buyer_Code
Supplier_Number
name
Sku_Global
OEM_Number
Part_Description_English
Status
C_Last12_SLS_QTY

I have created one Query including all of the above fields as well as the following field, "XP1: GetTen([Supplier_Number])"
In this query I have set "Supplier_Number" to sort ascending and "C_Last12_SLS_QTY" to sort descending.

I then created a module, written as follows:

Option Compare Database
Option Explicit
Public wSUPPLIER_NUMBER As String
Public wNum As Integer

Function GetTen(SUPPLIER_NUMBER) As Long
If wSUPPLIER_NUMBER = SUPPLIER_NUMBER Then
wNum = wNum + 1
Else
wNum = 1
End If
If wNum > 10 Then
GetTen = 0
Else
GetTen = wNum
End If
wSUPPLIER_NUMBER = SUPPLIER_NUMBER
End Function

I then created a second query with all of the above fields, and the criteria for the XP1 field as ">0"

As far as I can tell, this should bring me back a list with the first 10 rows for each of my Supplier numbers... unfortunately it is for some and for others it's bringing back more than 10 rows. The other problem is that it seems to be picking random rows to bring back, and not just the first 10 (which because in query1 I set the C_LAST12_SLS_QTY field to sort descending, should be my top 10 selling sku's).

Does anyone have any idea as to what I've done wrong? I'm pretty new with Access so I may be missing something pretty simple.

Any help would be greatly appreciated.

Antonio