need help

  • Thread starter Thread starter tylach
  • Start date Start date
T

tylach

i'm stuck on a project that i am working on, it involves totaling the
team scores for a contest. each team may only have 4 team members,
each individual member has an individual score. however, the total
team score only uses the highest 2 of the 4 members scores to get the
final team total.
in another part of the project i had to find the best 3 of the 4 for
the total, so all that i had to do was find the minimum and subtract
from the old total.
this is the command i used in the report.
=IIf(Count(*)=4,Sum([FFA Score])-Min([FFA Score]),Sum([FFA Score]))

if anyone can help it would be greatly appreciated.
 
I write various applications for schools and commonly need to determine
first, second, third, etc.. Hence I've written a little function to do
this... Paste the code below into a module.

A typical call you'd use to find first place in a team would be something
like...
=DRank(1,"[FFA Score]","FFAScoreTableName","TeamID=" & TeamID

And for second place...
=DRank(2,"[FFA Score]","FFAScoreTableName","TeamID=" & TeamID

In your case you'd (obviously) add these together in a textbox within a
report based on just the teams and their TeamID (PK). (OR include it in the
underlying query).

And the code...

Public Function DRank(ReqdRank As Long, FieldName As String, Domain As
String, Optional criteria As String) As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & FieldName
OrderedSQL = OrderedSQL & " FROM " & Domain
If criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & FieldName & " DESC;"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

'move to reqd record
rs.MoveFirst
rs.Move ReqdRank - 1
DRank = rs.Fields(FieldName)

rs.Close
db.Close

End Function

HTH
Sam
 
Back
Top