(SELECT COUNT(*) + 1
       FROM History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE H.ScoreTotal > History.ScoreTotal AND 
            L.Gender = Lifter.Gender) AS Rank

SELECT (SELECT COUNT(*) + 1
       FROM History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE H.ScoreTotal > History.ScoreTotal AND 
            L.Gender = Lifter.Gender) AS Rank, 
    Lifter.LifterNumber AS LifterNumber, 
    Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, 
    Lifter.BodyWeight, Class.Description AS CDescription, 
    Activity.Description AS GDescription, A.Name AS NameA, 
    History.ScoreA, History.SubTotalA, B.Name AS NameB, 
    History.ScoreB, History.SubTotalB, C.Name AS NameC, 
    History.ScoreC, History.SubTotalC, 
    History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity, Events A, 
    Events B, Events C
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 
    Meet.uidEventsA = A.uidEvents AND 
    Meet.uidEventsB = B.uidEvents AND 
    Meet.uidEventsC = C.uidEvents AND 
    Team.TeamStatus = 0 AND Lifter.LifterStatus = 0 AND 
    (MeetDate = DATE ())
GROUP BY History.ScoreTotal, uidClassRef, Lifter.LifterNumber, 
    Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight, 
    Class.Description, History.WeightScoreC, Lifter.Gender, 
    Activity.Description, Activity.Activity, Lifter.LifterStatus, 
    A.Name, B.Name, C.Name, History.ScoreA, History.ScoreB, 
    History.ScoreC, History.SubTotalA, History.SubTotalB, 
    History.SubTotalC
HAVING (Activity.Activity = 'GenderStatus') AND 
    (Lifter.LifterStatus = 0)
ORDER BY Lifter.Gender ASC, History.ScoreTotal DESC, 
    VAL(Class.Description) DESC, Lifter.BodyWeight ASC

(SELECT COUNT(*) + 1
       FROM History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE (H.ScoreTotal > History.ScoreTotal AND 
            L.Gender = Lifter.Gender)

OR

(H.ScoreTotal = History.ScoreTotal AND 
            L.Gender = Lifter.Gender AND L.LifterNumber < Lifter.LifterNumber)

) AS Rank,

(SELECT COUNT(*) + 1
       FROM 
History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE H.ScoreTotal > History.ScoreTotal AND 
            L.Gender = Lifter.Gender
and

L.uidLifter = (select min(uidLifter) from Lifter LL inner join History HH on
      LL.uidLifter = HH.uidLifterRef where LL.Gender = L.Gender and 
              HH.scoretotal = H.scoretotal)

) AS Rank,

SELECT (SELECT COUNT(*) + 1
       FROM History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE L.uidClassRef = Lifter.uidClassRef AND 
            H.WeightScoreA > History.WeightScoreA AND 
            (L.StatusInTeam < 2) AND 
            L.firstname & L.lastname =
                (SELECT MIN(firstname & lastname)
              FROM Lifter LL INNER JOIN
                   History HH ON 
                   LL.uidLifter = HH.uidLifterRef
              WHERE HH.WeightScoreA = H.WeightScoreA)) 
    AS Rank, Lifter.LifterNumber AS LifterNumber, 
    Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, 
    Lifter.BodyWeight, Class.Description AS CDescription, 
    Activity.Description AS GDescription, 
    Events.Name AS EventName, 
    History.WeightScoreA AS ScoreA, History.SubTotalA
FROM Class, Lifter, Team, History, Meet, Activity, Events
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND 
    History.WeightScoreA > 0 AND (Lifter.StatusInTeam < 2) AND 
    Class.uidClass = Lifter.uidClassRef AND 
    Lifter.uidTeamRef = Team.uidTeam AND 
    Lifter.uidLifter = History.uidLifterRef AND 
    Team.uidMeetRef = Meet.uidMeet AND 
    Lifter.uidTeamRef = Team.uidTeam AND 
    Lifter.Gender = Activity.Code AND 
    Meet.uidEventsA = Events.uidEvents
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, 
    Lifter.Firstname, Lifter.BodyWeight, Class.Description, 
    History.WeightScoreA, Lifter.Gender, Activity.Description, 
    Activity.Activity, Events.Name, History.SubTotalA, 
    Lifter.LifterStatus, Lifter.StatusInTeam
HAVING (Activity.Activity = 'GenderStatus') AND 
    (Lifter.LifterStatus = 0)
ORDER BY Val(Class.Description) ASC, History.WeightScoreA DESC, 
    Lifter.Gender DESC, Lifter.BodyWeight ASC

SELECT (SELECT COUNT(*) + 1
       FROM History H INNER JOIN
            Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE L.uidClassRef = Lifter.uidClassRef AND 
            H.WeightScoreA > History.WeightScoreA AND 
            L.StatusInTeam < 2

AND
L.uidLifter = (select min(LL.uidLifter)
    from Lifter LL inner join History HH on
      LL.uidLifter = HH.uidLifterRef where
LL.uidClassRef = Lifter.uidClassRef AND HH.WeightScoreA = H.WeightScoreA)

) AS Rank, 


Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname,
Lifter.Firstname AS Firstname, History.WeightScoreA AS ScoreA,
History.SubTotalA
FROM Lifter, History
WHERE History.WeightScoreA > 0 AND Lifter.StatusInTeam < 2 AND 
    Lifter.uidLifter = History.uidLifterRef

          AND Lifter.Gender = 0
;

SELECT (SELECT COUNT(*) + 1
       FROM History H
            INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef
       WHERE L.uidClassRef = Lifter.uidClassRef AND 
            H.ScoreTotal > History.ScoreTotal AND
           L.uidLifter = (Select Min(LL.uidLifter) from Lifter LL INNER JOIN History HH on
                                    LL.uidLifter = HH.uidLifterRef WHERE
                                    LL.uidClassRef = Lifter.uidClassRef AND
                                    HH.ScoreTotal = H.ScoreTotal)) AS Rank,
Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS CDescription, Activity.Description AS GDescription, A.Name AS NameA, History.ScoreA, History.SubTotalA, B.Name AS NameB, History.ScoreB, History.SubTotalB, C.Name AS NameC, History.ScoreC, History.SubTotalC, History.ScoreTotal AS ScoreTotal
FROM Class, Activity, Events AS A, Events AS B, Events AS C,
((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef)
 INNER JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef)
 INNER JOIN History ON Lifter.uidLifter = History.uidLifterRef
WHERE (((History.ScoreTotal)>0) AND
 ((Lifter.Gender)=[Activity].[Code]) AND
 ((Class.uidClass)=[Lifter].[uidClassRef]) AND
 ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND
 ((Lifter.uidLifter)=[History].[uidLifterRef]) AND
 ((Team.uidMeetRef)=[Meet].[uidMeet]) AND
 ((Meet.uidEventsA)=[A].[uidEvents]) AND
 ((Meet.uidEventsB)=[B].[uidEvents]) AND
 ((Meet.uidEventsC)=[C].[uidEvents]) AND
 ((Team.TeamStatus)=0) AND
 ((Lifter.LifterStatus)=0) AND
 ((Lifter.StatusInTeam)<2) AND
 ((Meet.MeetDate)=Date()) AND
 ((Activity.Activity)='GenderStatus'))
ORDER BY Val(Class.Description), Lifter.Gender, History.ScoreTotal DESC;

quote:

---

Originally posted by jsmith8858

This is really easy to do in a report in Access. What are you doing with the results of this query? Further calculations? storing the values? or just outputing the results? if you use a report to output the results, calculating the rank of a row is quite simple using a running total.

- Jeff

---

SELECT Lifter.uidLifter, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS CDescription, Activity.Description AS GDescription, A.Name AS NameA, History.ScoreA, History.SubTotalA, B.Name AS NameB, History.ScoreB, History.SubTotalB, C.Name AS NameC, History.ScoreC, History.SubTotalC, History.ScoreTotal AS ScoreTotal
FROM Class, Activity, Events AS A, Events AS B, Events AS C, ((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef) INNER JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef) INNER JOIN History ON Lifter.uidLifter = History.uidLifterRef
WHERE (((History.ScoreTotal)>0) AND
 ((Lifter.Gender)=[Activity].[Code]) AND
 ((Class.uidClass)=[Lifter].[uidClassRef]) AND
 ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND
 ((Lifter.uidLifter)=[History].[uidLifterRef]) AND
 ((Team.uidMeetRef)=[Meet].[uidMeet]) AND
 ((Meet.uidEventsA)=[A].[uidEvents]) AND
 ((Meet.uidEventsB)=[B].[uidEvents]) AND
 ((Meet.uidEventsC)=[C].[uidEvents]) AND
 ((Team.TeamStatus)=0) AND
 ((Lifter.LifterStatus)=0) AND
 ((Lifter.StatusInTeam)<2) AND
 ((Meet.MeetDate)=Date()) AND
 ((Activity.Activity)='GenderStatus'));

SELECT (SELECT COUNT(*) + 1
from q t where t.CDescription = q.CDescription and t.GDescription = q.Gdescription
and t.ScoreTotal > q.ScoreTotal and
t.uidLifter = (select min(uidLifter) from q tt where tt.CDescription = t.CDescription 
and tt.GDescription = t.Gdescription and tt.ScoreTotal = t.ScoreTotal)
) AS Rank, q.*
FROM q
ORDER BY Val(CDescription), GDescription, ScoreTotal DESC;

quote:

---

Originally posted by jsmith8858

In crystal just add a running total formula to your report. set the running total to accumulate the value "1" and have it reset where you need to. Takes about 4 clicks of the mouse! much more efficient than trying to use T-SQL. Access reports has something similiar, add a text box, set it equal to "1", and have it set to "RunningSum".

Try reading the help files and/or books on the product you are using.

- Jeff

---

quote:

---

Originally posted by TenTwenty

Hi Stoad,

Nope it's not working. The query currently resembles something that in short look like this:

1 AAAA 100
2 BBBB 90
3 CCCC 85
3 DDDD 85
5 EEEE 80

I would like to see:

1 AAAA 100
2 BBBB 90
3 CCCC 85
3 DDDD 85
4 EEEE 80

I'm desperate to have someone helping me on the solution given by MS

Regards

---

quote:

---

Originally posted by jsmith8858

quote:

---

Originally posted by TenTwenty

Hi Stoad,

Nope it's not working. The query currently resembles something that in short look like this:

1 AAAA 100
2 BBBB 90
3 CCCC 85
3 DDDD 85
5 EEEE 80

I would like to see:

1 AAAA 100
2 BBBB 90
3 CCCC 85
3 DDDD 85
4 EEEE 80

I'm desperate to have someone helping me on the solution given by MS

Regards

---

By the way -- you know that what you are looking for is to return incorrect results, right? with a tie, the value EEEE is in 5th place, not 4th place. There are 4 items ahead of it, not 3.

You are asking for 2+2 to equal something other than 4. That's why you are having so much trouble.

- Jeff

---

SELECT Lifter.uidLifter, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS
Lastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS 
CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotal, 
History.LiftTotal, Team.Name AS TeamName, uidAwardNamesRef
FROM Class, Activity, ((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef) INNER 
JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef) INNER JOIN History ON Lifter.uidLifter 
= History.uidLifterRef
WHERE (((History.ScoreTotal)>0) AND
 ((Lifter.Gender)=[Activity].[Code]) AND
 ((Class.uidClass)=[Lifter].[uidClassRef]) AND
 ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND
 ((Lifter.uidLifter)=[History].[uidLifterRef]) AND
 ((Team.uidMeetRef)=[Meet].[uidMeet]) AND

 ((Team.TeamStatus)=0) AND
 ((Lifter.LifterStatus)=0) AND
 ((Lifter.StatusInTeam)<1) AND
 ((Meet.MeetDate)=Date()) AND
 ((Activity.Activity)='GenderStatus'));

SELECT (SELECT COUNT(*) + 1
FROM qryDistinctComptTeam t

WHERE t.CDescription = qryDistinctComptTeam.CDescription AND
 t.GDescription = qryDistinctComptTeam.Gdescription AND 
((t.ScoreTotal > qryDistinctComptTeam.ScoreTotal) OR 
(t.ScoreTotal = qryDistinctComptTeam.ScoreTotal AND
t.Bodyweight < qryDistinctComptTeam.Bodyweight)) AND
t.uidLifter = (SELECT Min(uidLifter) FROM qryDistinctComptTeam tt 

WHERE tt.CDescription = t.CDescription AND
 tt.GDescription = t.Gdescription AND
 tt.ScoreTotal = t.ScoreTotal AND
 tt.Bodyweight = t.Bodyweight)

) AS Rank, qryDistinctComptTeam.*
FROM qryDistinctComptTeam;

SELECT qryIntermComptTeam.*, AwardPoints.Name AS AName, AwardPoints.Points
FROM qryIntermComptTeam, AwardPoints
WHERE ((qryIntermComptTeam.Rank) > 0) AND
 ((AwardPoints.uidAwardNamesRef)=[AwardPoints].[uidAwardNamesRef])  AND
((AwardPoints.Rank) = qryIntermComptTeam.Rank)
ORDER BY Val(CDescription), GDescription, ScoreTotal DESC , Bodyweight;