Top 10 people

[Beerman3436]

I have a report that lists names down the left hand column and the months across the top. This report so me how many calls each person got for each month.

This report is based on a Crosstab Query.

What I need to do is add a new column on the report that will put a 1 thru 10 beside the top 10 people (the top 10 perple with the most calls for the year)!

Can anyone help please.

 

[Travis]

Add a field to your report for the 1 to 10

Make its control source: = TopTen([EmployeeID])


Then on the Reports Class module add this function

Private Function TopTen(byVal EmpID as Long) as Varient
dim rst as Recordset '(Dao.Recordset for 2000)
dim stSQL as String

stSQL = "Select Top 10 [EmpID], Count([Calls]) From [tblCalls] Order by Count([Calls]) asc Group by [EmpID]"

set rst = currentdb.OpenRecordset(stsql)
rst.Movefirst
rst.findfirst "[EmpID]=" & EmpID
if Not rst.NoMatch then
TopTen = rst.AbsolutePosition +1
Else
TopTen = Empty
end if
end function

 

[Beerman3436]

I tryed it with this but it gives me a ORDER BY error.

Private Function TopTen(ByVal IncidentID As Long) As Variant

Dim rst As Recordset
Dim strSQL As String

strSQL = "Select Top 10 [IncidentID], Count([IncidentID]) from [QryMonth_Crosstab] order by Count ([IncidentID])asc Group by [IncidentID]"

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
rst.FindFirst "[IncidentID]=" & [IncidentID]
If Not rst.NoMatch Then
TopTen = rst.AbsolutePosition + 1
Else
TopTen = Empty
End If
End Function

Help Please

 

[Travis]

sorry the Order By comes after the Group By. My mistake