Found the solution but do'nt know how to implement

[TenTwenty]

Hi,
I wrote the query below and everything works fine except that the rank does not display correctly in the case of a tie. The calculated rank after the ties would not be correct due to this code in the query:

(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

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

[red]I would like to see:[/red]

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

I found a solution to the problem, in the link below, but I must admit that I have no clue on how to implement that solution in my query to solve the problem. I know that I have to write two small queries that will be used in this larger one.

Here is the link to the solution : http://support.microsoft.com/kb/207626/EN-US/
The database can be downloaded from http://www.reitzgh.co.za/add/Database.zip

Make sure to update the MeetDate in the Meet table to reflect the current date before you try to run the query.

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

 

[TenTwenty]

Hi Pat,

I ran the code but found that the statements in the where clause is ignored like, (Meet.MeetDate) = DATE ()) , ((Lifter.StatusInTeam) < 1) If you change the Lifter.StatusInTeam you will see that the calculated rank is out of sync.

I've uploaded a new database with more data to http://www.reitzgh.co.za/add/Database.zip

Where can I change the code?

Regards

 

[TenTwenty]

The solution is to use nested queries. Create a query that applies the selection criteria and then join to that query rather than the table. Remove the criteria from the final query.

Hi Pat,
If you look at the thread starter and at the solution that I linked to, it was what MS proposed. The whole thing is that after nearly a week and a half of staring at variants of the same code I'm so confused that I don't know how to go about of implementing the solution. What should go in which statement?

They talk about the same thing that you just mentioned and they have examples statements of it.

If possible, can you perhaps extend a helping hand. Where ever possible I'll try to help to my abillity.

Regards

 

[TenTwenty]

Solution implemented

I don't really have time to play with this. The selection criteria is on fields that don't appear in the final recordset. Create a separate query for each table that has selection criteria. Select the columns you want in the final solution and apply the selection criteria. Then join the queries rather than the tables. A quick count says you'll need 5 queries with selection criteria.

Hi,

Here is the best solution that we found. All my thanks to those who helped.

Query #1 with the name: qryDistinctComptBestLifts

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'));

A second query with the name: qryComptBestLifts

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

WHERE t.CDescription = qryDistinctComptBestLifts.CDescription AND
 t.GDescription = qryDistinctComptBestLifts.Gdescription AND 
((t.ScoreTotal > qryDistinctComptBestLifts.ScoreTotal) OR 
(t.ScoreTotal = qryDistinctComptBestLifts.ScoreTotal AND t.Bodyweight < qryDistinctComptBestLifts.Bodyweight)) AND
t.uidLifter = (SELECT min(uidLifter) from qryDistinctComptBestLifts tt WHERE tt.CDescription = t.CDescription AND
 tt.GDescription = t.Gdescription AND
 tt.ScoreTotal = t.ScoreTotal AND
 tt.Bodyweight = t.Bodyweight)

) AS Rank, qryDistinctComptBestLifts.*
FROM qryDistinctComptBestLifts
ORDER BY Val(CDescription), GDescription, ScoreTotal DESC , Bodyweight;