Increment a Counter and reset it

[Ten Twenty via AccessMonster.com]

Increment a Counter and reset it at some stage when the group change:

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. A database with sample data can be found at http://www.reitzgh.co.za/add/database.mdb

My first problem comes from the statement below. It brings back data and pack a report BUT I just can not get it right to add a field that would contain the rank or position of the lifter: It should reset after the break in CDescription. Should I be able to solve this I guess that I will then be able to also complete the reports on the team positions.
My other problem with this statement, though I guess I will be able to live with it, is the sorting order of the CDescription field. A problem when one mix numeric and alfa. One would have liked to have the 100Kg right at the top. Currently it look like this:

LifterNumber Lastname Firstname CDescription GDescription EventName SubTotalA ScoreA
6 Man Spider 90Kg Male Benchpress 190 126.37
5 Doe John 82.5Kg Male Benchpress 172.3 115.68
3 Pompies Piet 82.5Kg Male Benchpress 142 95.48
1 Beneke Andre 70Kg Male Benchpress 87.3 72.29
4 Thomas Sue 56Kg Female Benchpress 137.5 168.91
8 Roetz Ilse 52Kg Female Benchpress 150 186.99
2 Archel Joan 52Kg Female Benchpress 135 168.29
7 Hulk Incredible 100Kg Male Benchpress 190 116.95

but would like to see it like this,

Rank LifterNumber Lastname Firstname CDescription GDescription EventName SubTotalA ScoreA
1 7 Hulk Incredible 100Kg Male Benchpress 190 116.95
1 6 Man Spider 90Kg Male Benchpress 190 126.37
1 5 Doe John 82.5Kg Male Benchpress 172.3 115.68
2 3 Pompies Piet 82.5Kg Male Benchpress 142 95.48
1 1 Beneke Andre 70Kg Male Benchpress 87.3 72.29
1 4 Thomas Sue 56Kg Female Benchpress 137.5 168.91
1 8 Roetz Ilse 52Kg Female Benchpress 150 186.99
2 2 Archel Joan 52Kg Female Benchpress 135 168.29

SELECT Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Class.Description AS CDescription, Activity.Description AS GDescription, Events.Name AS EventName, History.SubTotalA, History.WeightScoreA AS ScoreA
FROM Class, Lifter, Team, History, Meet, Activity, Events
WHERE (((Meet.MeetDate)=Date()) AND ((Class.uidClass)=[Lifter].[uidClassRef]) AND ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND ((Lifter.uidLifter)=[History].[uidLifterRef]) AND ((Team.uidMeetRef)=[Meet].[uidMeet]) AND ((Lifter.Gender)=[Activity].

) AND ((Meet.uidEventsA)=[Events].[uidEvents]))
GROUP BY Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreA, Activity.Description, Events.Name, History.SubTotalA, Lifter.Gender, Activity.Activity, Lifter.LifterStatus
HAVING (((Activity.Activity)='GenderStatus') AND ((Lifter.LifterStatus)=0))
ORDER BY Class.Description DESC , Lifter.Gender DESC , History.WeightScoreA DESC;

 

[Tom Ellison]

Dear Ten Twenty:

A column to rank the lifters within each Class.Description according to the
ScoreA would use a subquery. As best I can tell about the table structure
you use, Class.Description may be unique, so this could operate on
Lifter.uidClassRef. This might now work if you have two values of uidClass
with the same Description, but I'm guessing that's not the case (in which
case you should have a unique index on Description in the Class table BTW).

But this subquery will need a JOIN in order to work between the uidClassRef
column in the Lifter table and the WeightScoreA in the History table.
However, all this makes me concerned about how, or whether, you intend to
filter the history according to MeetDate = Date(). I didn't see any way you
are filtering that. Are you wanting to rank lifters within one meet, or all
the meets on a given date, or what?

Well, here's some stab at a solution, but it may be quite inadequate because
of the problems I'm seeing:

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

Maybe this will give you what you want, but I'm doubting it. Put this at
the end of your SELECT list to try it (Just before the FROM). I'm guessing
you need to filter the History to just the date, or the one meet you want,
for this to really work, but I don't even know if you have such columns in
the History table. I didn't see a reference to these in the query you
posted, althought I would think you need to filter/join on these as well.
The need to do this may not show up until you have several meets on
different days in the data.

Tom Ellison
Microsoft Access MVP

Increment a Counter and reset it at some stage when the group change:

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. A database with sample
data can be found at http://www.reitzgh.co.za/add/database.mdb

My first problem comes from the statement below. It brings back data and
pack a report BUT I just can not get it right to add a field that would
contain the rank or position of the lifter: It should reset after the
break in CDescription. Should I be able to solve this I guess that I will
then be able to also complete the reports on the team positions.
My other problem with this statement, though I guess I will be able to
live with it, is the sorting order of the CDescription field. A problem
when one mix numeric and alfa. One would have liked to have the 100Kg
right at the top. Currently it look like this:

LifterNumber Lastname Firstname CDescription GDescription EventName
SubTotalA ScoreA
6 Man Spider 90Kg Male Benchpress 190 126.37
5 Doe John 82.5Kg Male Benchpress 172.3 115.68
3 Pompies Piet 82.5Kg Male Benchpress 142 95.48
1 Beneke Andre 70Kg Male Benchpress 87.3 72.29
4 Thomas Sue 56Kg Female Benchpress 137.5 168.91
8 Roetz Ilse 52Kg Female Benchpress 150 186.99
2 Archel Joan 52Kg Female Benchpress 135 168.29
7 Hulk Incredible 100Kg Male Benchpress 190 116.95

but would like to see it like this,

Rank LifterNumber Lastname Firstname CDescription GDescription EventName
SubTotalA ScoreA
1 7 Hulk Incredible 100Kg Male Benchpress 190 116.95
1 6 Man Spider 90Kg Male Benchpress 190 126.37
1 5 Doe John 82.5Kg Male Benchpress 172.3 115.68
2 3 Pompies Piet 82.5Kg Male Benchpress 142 95.48
1 1 Beneke Andre 70Kg Male Benchpress 87.3 72.29
1 4 Thomas Sue 56Kg Female Benchpress 137.5 168.91
1 8 Roetz Ilse 52Kg Female Benchpress 150 186.99
2 2 Archel Joan 52Kg Female Benchpress 135 168.29

SELECT Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname,
Lifter.Firstname AS Firstname, Class.Description AS CDescription,
Activity.Description AS GDescription, Events.Name AS EventName,
History.SubTotalA, History.WeightScoreA AS ScoreA
FROM Class, Lifter, Team, History, Meet, Activity, Events
WHERE (((Meet.MeetDate)=Date()) AND
((Class.uidClass)=[Lifter].[uidClassRef]) AND
((Lifter.uidTeamRef)=[Team].[uidTeam]) AND
((Lifter.uidLifter)=[History].[uidLifterRef]) AND
((Team.uidMeetRef)=[Meet].[uidMeet]) AND
((Lifter.Gender)=[Activity].

) AND
((Meet.uidEventsA)=[Events].[uidEvents]))
GROUP BY Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname,
Class.Description, History.WeightScoreA, Activity.Description,
Events.Name, History.SubTotalA, Lifter.Gender, Activity.Activity,
Lifter.LifterStatus
HAVING (((Activity.Activity)='GenderStatus') AND
((Lifter.LifterStatus)=0))
ORDER BY Class.Description DESC , Lifter.Gender DESC ,
History.WeightScoreA DESC;
[/QUOTE]

 

[Ten Twenty via AccessMonster.com]

Hi Tom,

Firstly: Yes, There is a unique index.

Secondly: There can only be one meeting at any given day but at some stage I guess that the user might want to checkout some history of previous meetings.

Thirly: By linking the Lifter to the Team and the Team to a Meet I can get the Meeting Date to Filter on.

The pasted code rightfully only display data of a single meet as pointed out by you. This is the least that I want.

I'm now going to try your solution to see how it works.

BTW. You can download a sample of the database from the page I listed in the posting. That should give you a fair idea of what I,m trying to do.

 

[Ten Twenty via AccessMonster.com]

Hi to you all. Here is the sulution to the questions I had.

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) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Class.Description AS CDescription, History.WeightScoreA AS ScoreA, Activity.Description AS GDescription, Events.Name AS EventName, History.SubTotalA
FROM Class, Lifter, Team, History, Meet, Activity, Events WHERE (Meet.MeetDate = Date()) 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 = Events.uidEvents GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Class.Description, History.WeightScoreA, Lifter.Gender, Activity.Description, Activity.Activity, Events.Name, History.SubTotalA, Lifter.LifterStatus HAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0) ORDER BY Val(Class.Description) ASC, History.WeightScoreA DESC, Lifter.Gender DESC