Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Found the solution but do'nt know how to implement

Author  Topic 

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-07 : 04:04:29
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


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 : [url]http://support.microsoft.com/kb/207626/EN-US/[/url]
The database can be downloaded from [url]http://www.reitzgh.co.za/add/Database.zip[/url]

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-07 : 07:38:25
Try this "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)

OR

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


) AS Rank,


I chose LifterNumber as "a corrector", but it can be e.g. BodyWeight or even DateOfBirth.
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-07 : 08:05:03
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
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-07 : 09:20:51
.. means I misunderstood your trouble; try this:

(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,
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-10 : 04:09:01
Hi Stoad,

The code work fine initially until I start to add some more test data. In 2 or more 3 cases the rank is out of sync. Below is the code that I ran to get the results I'm talking about.

I've also uploaded a new database with the data I tested on to [url]http://www.reitzgh.co.za/add/Database.zip[/url]

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


Regards
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-10 : 04:32:28
TenT,

you missed my "Edited by - Stoad on 01/07/2005 10:22:28".

PS Ok, I will check your updated mdb.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-10 : 08:35:06
1.
Why the GROUP BY without any aggregation? I think you need no it.
And you can safely move HAVING part (its content) into WHERE clause.

2.
Check this castrated query. It works fine. Why it goes mad after adding
tables Class, Team, Meet, Activity, Events I can't say for sure.

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
;

Edit: this query returns 51 rows (I count both genders); yours (original) - 31 rows.
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-10 : 09:57:40
Hi Stoad,
I've changed the code. Thanks.

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;


I'll also try this side to look for a fix. Let you know if I find something.

Regards
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-10 : 11:06:29
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
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-11 : 02:35:40
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



The result will be used in a report. The report is created in VB 6 report writer. Not Crystal. You can create the report in ACCESS though, I believe that I can rebuild it. If VB is not a problem for you I can send you the reports.

Seem like you got quite a different angle here. Would you mind to enlight me?
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-11 : 04:34:18
TenT,
I understood the trouble: we can't calculate Rank on the fly. So, I split
the whole thing into two queries: the 1st named "q" and the 2nd - "main".

Query "q":

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

Query "main" (you can name/ rename both of these queries as you like):

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;

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-11 : 07:29:52
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
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-11 : 07:37:52
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



Hi,
This method only works in crystal and unfortunately not in the VB reportwriter.

Thanks
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-11 : 08:04:37
Hi Stoad,

Can you help, two lifters with the same score does not constitute a tie but only the same score and the same bodyweight. Currently the program constitute a tie when the scores are equal.

Other than that, what can say, this will help me solve 7 statements and fixing 11 reports.

BTW,
I was going nuts man, believe me, after a week and a half on the same problem one tend to, I guess you will know from experience.

Regards

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-11 : 08:51:30
I'm sorry, I misread what you wrote. The VB Report Writer, to speak bluntly, is horrible by the way.



- Jeff
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-11 : 09:00:24
TenT,
try this updated query "main" (staging query "q" stays unchanged):

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

btw, don't mind to keep asking.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-11 : 09:40:13
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
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-11 : 09:48:35
THAT's IT!


Stoad, I only need to display the top 3 lifters in each division seperated by gender, thus, same query but only the top 3to be able to do an award ex.

1 = Gold
2 = Silver
2 = Silver
3 = Bronze

Or

1 = Gold
2 = Silver
3 = Bronze

I might I ask you later to help me with the team positions as this is now all that is left but I need to quickly take a look at my analysis to see how it is done.

Thanks!
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-11 : 09:57:32
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



Jeff,

Yes, True, that's unfortunately the reality I'm sitting with.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-11 : 12:54:08
TenTw,
it's the easiest part! Just add one more query (I'd call it "SuperMain":):

select *
from Main
where Rank < 4
ORDER BY Val(CDescription), GDescription, ScoreTotal DESC, Bodyweight;

and remove ORDER BY part from query "Main" - now it's needless there.
Go to Top of Page

TenTwenty
Starting Member

22 Posts

Posted - 2005-01-12 : 10:55:30
Hi Stoad,

Thanks for the help again and I'll say a few more times. I spend the whole day on implementing your solution into some of my statements. All VB code is now finished.

Look at this weirdo rule, "Point scoring for all World, Continental and Regional Championships shall be : 12, 9, 8, 7, 6, 5, 4, 3, 2, for the first 9
placing in any bodyweight category. Thereafter, each lifter who makes a total in the competition shall be awarded one point" The current code is now rendered useless by my dam ignorance. Do you think this can be solved in the statement or should I be looking at something else?

Here is the Full code I used, I'll add some more test data tonight.

qryDistinctComptTeam

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


qryIntermComptTeam

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;


qryComptTeam

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;


Regards
Go to Top of Page
    Next Page

- Advertisement -