Mildly Stumped league tables,

[stuey]

I've been asked to collate some information based on a kid league, I am trying to sort out how precisely it works

the data I have is this

finish position, name, club

I need to calculate the best kiddie based on 3 of a possible 6 races

So tbls

tblResults
fldSeries
NAME
CLUB
Pos(isition)

tblScores
fldID
pos
Points

query1
SELECT tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, Sum(fldScore.Points) AS SumOfPoints
FROM tblResults LEFT JOIN fldScore ON tblResults.Pos = fldScore.Pos
GROUP BY tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, tblResults.Pos
HAVING (((tblResults.fldSeries)="220-2005") AND ((tblResults.Pos)<51))
ORDER BY Sum(fldScore.Points) DESC;

I need this to be changed to only pick up athletes with only the best three scores?

any hints?

STuart

 

[statsman]

Try placing the following in your criteria for Pos(ition)

<4

good luck

 

[Mile-O]

SELECT TOP 3 tblResults.fldSeries, etc......

 

[stuey]

Sorry I think I confused myself its not the postion that determines the score its the number of events you completes I think I cracked, not being very clever with VBA I did it ASP.

I have create a table to store the results in which I am going to also use to display on the website, I generally like to create queries and say "select * from qryX" and display the table ( hard work in Access in other words)

So I have my results table & my Series Results table, not efficient but then i couldn't see much of a way round it.

• 
  Delete * from series results
  Select Distinct name from results where event is in the series and finish position < 51
  for every Name Select top 3 * from results where name = name from above order by points desc
  for each one of those lines insert info into series table
  select * from Series Data group by name count the number of duplicates
  if count = 1, 2 delete those records so only left with those who have 3
  perform series totals