Join of a file on a calculated counter

[Ten Twenty via AccessMonster.com]

I wrote a powerlifting system but have run into some problems and it all boils down to reporting and queries. Although the system is in VB6 with an access database and the reporting done with the datareporter that ship with VB, the SQL statements should all be the same.

I have come to a point where I can now calculate the position of a record by doing a Select within a Select and it works like a charm. I have two tables, TeamPoints that holds the points a lifter is awarded for his/her team depending on the rank they hold. Only the top 8 rankings are rewarded irrespective of anything else. Al I want is to have the teams grouped as I would like to group on each team and create an agregate to sum the points for each team. Currently it look like this:

Rank LifterNumber LastName Firstname ScoreTotal TeamName Points Pos
3 5 Man Super 655.09 Team A 1 Tenth
3 5 Man Super 655.09 Team A 2 Nineth
3 5 Man Super 655.09 Team A 3 Eighth
3 5 Man Super 655.09 Team A 4 Sewenth
3 5 Man Super 655.09 Team A 5 Sixth
3 5 Man Super 655.09 Team A 6 Fifth
3 5 Man Super 655.09 Team A 7 Fourth
3 5 Man Super 655.09 Team A 8 Third
3 5 Man Super 655.09 Team A 9 Second
3 5 Man Super 655.09 Team A 12 First


but would like to see it like this,

Rank LifterNumber LastName Firstname ScoreTotal TeamName Points Pos
3 5 Man Super 655.09 Team A 8 Third
4 4 Hulk Incredible 645.8 Team A 7 Fourth
6 3 Man Spider 537.63 Team A 5 Sixth
8 6 America Mr 406.42 Team A 2 Nineth
1 7 DeDee Jenna 702.27 Team B 12 First
2 8 Bird Twitty 684.69 Team B 9 Second
5 10 Diva Meisie 550.46 Team B 6 Fifth
7 9 Doe John 480.3 Team B 4 Sewenth


Here is the current SQL statement I use:

SELECT Top 8
(SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
History.ScoreTotal AS ScoreTotal,
Team.Name AS TeamName, AwardPoints.Points, AwardPoints.Name
FROM Class, Lifter, Team, History, Meet, Activity, AwardNames,
AwardPoints
WHERE History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code AND Lifter.LifterStatus = 0 AND
Team.TeamStatus = 0 AND
Meet.uidAwardNamesRef = AwardNames.uidAwardNames AND
AwardPoints.uidAwardNamesRef = AwardNames.uidAwardNames
AND AwardPoints.Rank = Rank AND (MeetDate = DATE ())
GROUP BY Team.Name, uidClassRef, Lifter.LifterNumber,
Lifter.Lastname, Lifter.Firstname, History.ScoreTotal,
Activity.Description, Activity.Activity, Lifter.LifterStatus,
AwardPoints.Points, AwardPoints.Name
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY Team.Name ASC, History.ScoreTotal DESC

Here is the layout of the AwardPoints Table:

uidAwardPoints uidAwardNamesRef Name Award Points
1 1 First 1 12
2 1 Second 2 9
3 1 Third 3 8
4 1 Fourth 4 7
5 1 Fifth 5 6
6 1 Sixth 6 5
7 1 Sewenth 7 4
8 1 Eighth 8 3
9 1 Nineth 9 2
10 1 Tenth 10 1

 

[[MVP] S.Clark]

I would create a second(or more) to do anymore operations to the data. When
worse comes to worst, I create a destination table, write everything to
it(from mulitple append queries), then base the report on it.

 

[Ten Twenty via AccessMonster.com]

S.Clark, Can you PLSE elaborate....