Ranking swimmers per item

[Frank]

I have posted the following question a while ago

How can one calculate the best, second best, third best ect, places in a
query using the swimmer time and then attach a value to the place to
determine the points of each swimmer during the meeting [Ex fastest time =
place 1 - place 1 +11 =12 points, second fastest time = place 2 - place 2 +
8 = 12 points, ext, applicable to all the times from fastest to slowest of a
item

I have received different answers but still have a problem because the
statements will not work. I have also search the Internet and tried a lot of
statements but each one gave met the following:

YOU HAVE WRITTEN A SUBQUERY THAT CAN RETURN MORE THAN ONE FIELD WITHOUT
USING THE EXISTS WORD IN THE MAIN QUERY'S FORM CLAUSE. REVISE THE REQUEST OF
THE SUBQYERY TO REQUEST ONLY ONE FIELD

After this I have redesign my tables but still the same answer;

The tables I am using are the following:

Table: Item Fields. Itemnumber, Swimstroke, Fastest time [and some other
fields relating to the item]

Table: Swimmer Fields; Swimmername, Swimmersurname, Swimmeritem [which is
linked to Table Item Field Itemnumber] and Swimmertime [and also some other
fields relating to the swimmer]

I want to do the following during the swim meeting::

1. Calculate the rank of the swimmers per item, fastest time first slowest
time last
2. Give each swimmer points per item referring to their rank in the specific
item [as in the first mentioned paragraph]
3. Write down the fastest time of the item next to each swimmer’s name.

I realised that this is asking a lot, but as I have stated earlier I am ‘n
“expert†novice of Access and is in a steep learning curve to master this

Thanks again for helping me

Frank

 

[KARL DEWEY]

I seem to be missing something. Where do you get the 11 you add to first
place to equal 12 points? And how do you add 8 to second place to get 12
points?

What is Swimmeritem and Itemnumber? How are they to be use in the
calculations?

What does 'Calculate the rank of the swimmers per item' mean? What is 'item'?

Post some sample data and you mathmatical formulas.

 

[Frank]

Thanks for your reply
Seem to be missing something. Where do you get the 11 you add to first
place to equal 12 points? And how do you add 8 to second place to get 12
points?

During different meetings the people want to use different ways in
calculating the best team. So for instance the first swimmer can be given 12
point and the second swimmer 10 points, the third 8 points, the fourth 6
points and the rest of the the swimmers in the race, because they swam, 1
point each
I want to apologize for the above that the above was wrong but my first
language is not English and I can see now that it will cause confusion

What is Swimmeritem and Itemnumber? How are they to be use in the
calculations?

Itemnumber is the number of the item on the program. Ex, Item 1, 100 meter
Breaststroke men and the Swimmeritem which will correspond with itemnumber is
the item in which the swimmer will swim, example: Item 1 - 100 meters
Breaststroke men - George Hussey, There are for example 10 items on the
program and swimmer may swim one item or more, items may have one or a lot of
swimmer which swam in each
What does 'Calculate the rank of the swimmers per item' mean? What is 'item'?

If 5 swimmer swam the race I want to calculate the first, second, third,
fourth, and fifth swimmer in that particular race [item] in reference to
their times. First the fastest and fifth the slowest. The item here refer to
one of the items on the list of different items of the meet.[itemnumber]

Post some sample data and you mathmatical formulas.

Thanks again

 

[Philip Herlihy]

I have posted the following question a while ago

How can one calculate the best, second best, third best ect, places in a
query using the swimmer time and then attach a value to the place to
determine the points of each swimmer during the meeting [Ex fastest time =
place 1 - place 1 +11 =12 points, second fastest time = place 2 - place 2 +
8 = 12 points, ext, applicable to all the times from fastest to slowest of a
item

I have received different answers but still have a problem because the
statements will not work. I have also search the Internet and tried a lot of
statements but each one gave met the following:

YOU HAVE WRITTEN A SUBQUERY THAT CAN RETURN MORE THAN ONE FIELD WITHOUT
USING THE EXISTS WORD IN THE MAIN QUERY'S FORM CLAUSE. REVISE THE REQUEST OF
THE SUBQYERY TO REQUEST ONLY ONE FIELD

After this I have redesign my tables but still the same answer;

The tables I am using are the following:

Table: Item Fields. Itemnumber, Swimstroke, Fastest time [and some other
fields relating to the item]

Table: Swimmer Fields; Swimmername, Swimmersurname, Swimmeritem [which is
linked to Table Item Field Itemnumber] and Swimmertime [and also some other
fields relating to the swimmer]

I want to do the following during the swim meeting::

1. Calculate the rank of the swimmers per item, fastest time first slowest
time last
2. Give each swimmer points per item referring to their rank in the specific
item [as in the first mentioned paragraph]
3. Write down the fastest time of the item next to each swimmer’s name.

I realised that this is asking a lot, but as I have stated earlier I am ‘n
“expert†novice of Access and is in a steep learning curve to master this

Thanks again for helping me

Frank

Not being an expert here (I answer the easier ones!) I was nevertheless
intrigued by the "ranking" element. In each case you have to compare
the present record with all the others to get the ranking. I found two
approaches on the web:

One approach uses a subquery:

http://www.codingforums.com/archive/index.php/t-40147.html
As far as I can figure out, this form can only be used to return the
ranking of a single record (eg behind a form somewhere).

The other joins the relevant table to itself:

http://www.1keydata.com/sql/sql-rank.html
http://support.microsoft.com/kb/186133
- this can be used as a query returning all records.

At some level these are essentially the same technique, I believe.

Makes my head spin...

Phil, London

 

[KARL DEWEY]

You did not 'Post some sample data and you mathmatical formulas.' as
requested.

 

[ken]

To rank the swimmers for each item use a query like this:

SELECT Swimmeritem, Swimmername,
Swimmersurname, Swimmertime,
(SELECT COUNT(*)
FROM Swimmer AS S2
WHERE S2.Swimmertime < S1.Swimmertime
AND S2.Swimmeritem = S1.Swimmeritem)+1 AS Rank
FROM Swimmer AS T1
ORDER BY Swimmeritem, Swimmertime;

You can of course include the Item table in this query as well,
joining it on the Itemnumber/Swimmeritem columns, to include other
columns from the item table in the results. Save the query as
qryResults say.

To assign the points I'd suggest creating another table RankPoints
say, with columns Meeting, Rank and Points like this:

Meeting1 1 12
Meeting1 2 10
Meeting1 3 8
Meeting1 4 6
Meeting1 5 1
Meeting1 6 1
All 7 1
and so on to an artificially high number, e.g.
All 100 1

For a different meeting you can add rows with different ranks to give
different points for the qualifying rank as necessary, e.g. if only
the first three positions qualify, starting with 10 points for the
first place:

Meeting2 1 10
Meeting2 2 8
Meeting2 3 6
Meeting2 4 1
Meeting2 5 1
Meeting2 6 1

You can then join the first query to this table to give the points for
the meeting in question:

SELECT Swimmeritem, Swimmername,
Swimmersurname, Swimmertime,
qryResults.Rank, Points
FROM qryResults, RankPoints
WHERE qryResults.Rank = RankPoints.Rank
AND (Meeting = "Meeting1" OR Meeting = "All");

Ideally you should also have a Meeting column in the Swimmer table.
The first query could then include this column:

SELECT Meeting, Swimmeritem, Swimmername,
Swimmersurname, Swimmertime,
(SELECT COUNT(*)
FROM Swimmer AS S2
WHERE S2.Meeting < S1.Meeting
AND S2.Swimmertime < S1.Swimmertime
AND S2.Swimmeritem = S1.Swimmeritem)+1 AS Rank
FROM Swimmer AS T1
ORDER BY Swimmeritem, Swimmertime;

And the query could then be joined to the RankPoints table on this
column as well as the rank:

SELECT qryResults.Meeting, Swimmeritem, Swimmername,
Swimmersurname, Swimmertime, qryResults.Rank, Points
FROM qryResults, RankPoints
WHERE (qryResults.Meeting = RankPoints.Meeting
OR RankPoints.Meeting = "All")
AND qryResults.Rank = RankPoints.Rank;

to give the ranked results for all meetings.

You should also have a Meetings table as well of course with columns
such as Meeting, MeetingDate, Venue etc and relate this both to the
RankPoints and to Swimmer tables on the Meeting columns, enforcing
referential integrity in each case.

Ken Sheridan
Stafford, England

Thanks for your reply
Seem to be missing something. Where do you get the 11 you add to first
place to equal 12 points? And how do you add 8 to second place to get 12
points?

During different meetings the people want to use different ways in
calculating the best team. So for instance the first swimmer can be given 12
point and the second swimmer 10 points, the third 8 points, the fourth 6
points and the rest of the the swimmers in the race, because they swam, 1
point each
I want to apologize for the above that the above was wrong but my first
language is not English and I can see now that it will cause confusion

What is Swimmeritem and Itemnumber? How are they to be use in the
calculations?

Itemnumber is the number of the item on the program. Ex, Item 1, 100 meter
Breaststroke men and the Swimmeritem which will correspond with itemnumber is
the item in which the swimmer will swim, example: Item 1 - 100 meters
Breaststroke men - George Hussey, There are for example 10 items on the
program and swimmer may swim one item or more, items may have one or a lot of
swimmer which swam in each
What does 'Calculate the rank of the swimmers per item' mean? What is 'item'?

If 5 swimmer swam the race I want to calculate the first, second, third,
fourth, and fifth swimmer in that particular race [item] in reference to
their times. First the fastest and fifth the slowest. The item here refer to
one of the items on the list of different items of the meet.[itemnumber]

Post some sample data and you mathmatical formulas.

Thanks again

 

[Frank]

Hi Ken from the UK

Thank you for the very detailed answer reffering to my problem. I have al
the tables as you have suggested. The database works very well is is doing
everything i want but it cannot place the swimmers or give them points. There
for my question I have used the first query in your answer but no matter what
i still get the following fault report: YOU HAVE WRITTEN A SUBQUERY THAT CAN
RETURN MORE THAN ONE FIELD WITHOUT USING THE EXISTS WORD IN THE MAIN QUERY'S
FORM CLAUSE. REVISE THE REQUEST OF THE SUBQYERY TO REQUEST ONLY ONE FIELD

What may be wrong ??

Kind regards

 

[ken]

That error should not arise with the subquery as used in the example I
posted as that subquery can only return one column and one row. Can
you post the exact SQL of your query here. If the field names are in
your own language don't translate them to English equivalents, leave
them exactly as you are using them so we can see the actual query
which is producing the error.

Ken Sheridan
Stafford, England

 

[Frank]

Hi Ken this is the full SQL statement vir the Query is use

SELECT Span.SpNaam, Span.SpAfkorting, Swemmer.SwNaam, Swemmer.SwVan,
Swemmer.SwGeslag, Swemmer.SwTaalvoorkeur, Swemmer.SwInskryftyd,
Swemmer.SwGeboortedatum, Swemmer.SwGalaouderdom,
DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age,
Swemmer.SwOpmerking, Swemmer.SwGalatyd, (SELECT SwItemID, SwNaam, SwVan,
SwGalatyd, (SELECT COUNT(*) FROM Swemmer AS S2 WHERE S2.SwGalatyd <
S1.SwGalatyd AND S2.SwItemID = S1.SWItemID)+1 AS RANK FROM Swemmer T1 ORDER
By SwItemID, SwGalatyd) AS Plaas, Item.ItemID, Item.ItNommer,
Item.ItSwemslag, Item.ItAfstand, Item.ItOuderdomsgroep, Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer INNER JOIN Span ON Swemmer.SwSpanID =
Span.SpanID) ON Item.ItemID = Swemmer.SwItemID
ORDER BY Swemmer.SwGalatyd;

Thanks for your time and effort

 

[John Spencer MVP]

Pardon me for jumping in.

What Ken posted was the entire query. You only needed to add the segment that
calculated the ranking (Plaas)as a calculated field.

(SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < S1.SwGalatyd
AND S2.SwItemID = S1.SWItemID)+1 AS Plaas

So your query should look like
SELECT Span.SpNaam
, Span.SpAfkorting
, Swemmer.SwNaam
, Swemmer.SwVan
, Swemmer.SwGeslag, Swemmer.SwTaalvoorkeur, Swemmer.SwInskryftyd
, Swemmer.SwGeboortedatum, Swemmer.SwGalaouderdom
, DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age
, Swemmer.SwOpmerking
, Swemmer.SwGalatyd
, (SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < S1.SwGalatyd
AND S2.SwItemID = S1.SWItemID)+1 AS Plaas
, Item.ItemID, Item.ItNommer
, Item.ItSwemslag
, Item.ItAfstand, Item.ItOuderdomsgroep, Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer INNER JOIN Span ON Swemmer.SwSpanID =
Span.SpanID) ON Item.ItemID = Swemmer.SwItemID
ORDER BY Swemmer.SwGalatyd;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[BruceM]

PMFJI too, but I don't see the S1 alias defined anywhere.

Pardon me for jumping in.

What Ken posted was the entire query. You only needed to add the segment
that calculated the ranking (Plaas)as a calculated field.

(SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < S1.SwGalatyd
AND S2.SwItemID = S1.SWItemID)+1 AS Plaas

So your query should look like
SELECT Span.SpNaam
, Span.SpAfkorting
, Swemmer.SwNaam
, Swemmer.SwVan
, Swemmer.SwGeslag, Swemmer.SwTaalvoorkeur, Swemmer.SwInskryftyd
, Swemmer.SwGeboortedatum, Swemmer.SwGalaouderdom
, DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age
, Swemmer.SwOpmerking
, Swemmer.SwGalatyd
, (SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < S1.SwGalatyd
AND S2.SwItemID = S1.SWItemID)+1 AS Plaas
, Item.ItemID, Item.ItNommer
, Item.ItSwemslag
, Item.ItAfstand, Item.ItOuderdomsgroep, Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer INNER JOIN Span ON Swemmer.SwSpanID =
Span.SpanID) ON Item.ItemID = Swemmer.SwItemID
ORDER BY Swemmer.SwGalatyd;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Ken this is the full SQL statement vir the Query is use

SELECT Span.SpNaam, Span.SpAfkorting, Swemmer.SwNaam, Swemmer.SwVan,
Swemmer.SwGeslag, Swemmer.SwTaalvoorkeur, Swemmer.SwInskryftyd,
Swemmer.SwGeboortedatum, Swemmer.SwGalaouderdom,
DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age,
Swemmer.SwOpmerking, Swemmer.SwGalatyd, (SELECT SwItemID, SwNaam, SwVan,
SwGalatyd, (SELECT COUNT(*) FROM Swemmer AS S2 WHERE S2.SwGalatyd <
S1.SwGalatyd AND S2.SwItemID = S1.SWItemID)+1 AS RANK FROM Swemmer T1
ORDER By SwItemID, SwGalatyd) AS Plaas, Item.ItemID, Item.ItNommer,
Item.ItSwemslag, Item.ItAfstand, Item.ItOuderdomsgroep,
Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer INNER JOIN Span ON Swemmer.SwSpanID =
Span.SpanID) ON Item.ItemID = Swemmer.SwItemID
ORDER BY Swemmer.SwGalatyd;

Thanks for your time and effort

 

[Frank]

Hi John
Thanks for helping with the ranking problem I appreciate this very much,
I've done the query as you have suggested and it works by ranking the times
for each item correctly but the query insert a 1 for all the swimmers and
items. It does not rank it as 1, 2, 3, 4 ext. How do I change it this the
query give the following for each time?
Item I
Swimmer 1 01:00,01 1
Swimmer 2 02:00,00 2
Swimmer 3 03:00,00 3
Item 2
Swmimer 1 00:55,89 1
Swimmer 2 00:68,34 2
Swimmer 3 00:59,46 3

and ext.

Thank you for your time and effort to help.

 

[ken]

Try this:

SELECT Span.SpNaam
, Span.SpAfkorting
, S1.SwNaam
, S1.SwVan
, S1.SwGeslag, S1.SwTaalvoorkeur, S1.SwInskryftyd
, S1.SwGeboortedatum, S1.SwGalaouderdom
, DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age
, S1.SwOpmerking
, S1.SwGalatyd
, (SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < S1.SwGalatyd
AND S2.SwItemID = S1.SWItemID)+1 AS Plaas
, Item.ItemID, Item.ItNommer
, Item.ItSwemslag
, Item.ItAfstand, Item.ItOuderdomsgroep, Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer AS S1
INNER JOIN Span ON S1.SwSpanID =
Span.SpanID) ON Item.ItemID = S1.SwItemID
ORDER BY Item.ItNommer, S1.SwNaam, S1.SwGalatyd;

BTW for more accurate ways of computing a person's age see:

http://www.mvps.org/access/datetime/date0001.htm

Ken Sheridan
Stafford, England

 

[John Spencer MVP]

I am surprised that you didn't get prompts for S1.SwGalatyd and Sq.SWItemID.

Try this revision of the SQL statement.

SELECT Span.SpNaam
, Span.SpAfkorting
, Swemmer.SwNaam
, Swemmer.SwVan
, Swemmer.SwGeslag, Swemmer.SwTaalvoorkeur, Swemmer.SwInskryftyd
, Swemmer.SwGeboortedatum, Swemmer.SwGalaouderdom
, DateDiff("yyyy",[SwGeboortedatum],[SwGalaouderdom]) AS Age
, Swemmer.SwOpmerking
, Swemmer.SwGalatyd
, (SELECT COUNT(*) FROM Swemmer AS S2
WHERE S2.SwGalatyd < Swemmer.SwGalatyd
AND S2.SwItemID = Swemmer.SWItemID)+1 AS Plaas
, Item.ItemID, Item.ItNommer
, Item.ItSwemslag
, Item.ItAfstand, Item.ItOuderdomsgroep, Item.ItVinnigsteTyd
FROM Item INNER JOIN (Swemmer INNER JOIN Span ON Swemmer.SwSpanID =
Span.SpanID) ON Item.ItemID = Swemmer.SwItemID
ORDER BY Swemmer.SwGalatyd;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Frank]

I am surprised that you didn't get prompts for S1.SwGalatyd and Sq.SWItemID.

Yes I got the prompts but change S2 to Swemmer2 and S1 into Seimmer as a
alias and it then work 100%.
Thanks for your time and effort

 

[Frank]

PMFJI too, but I don't see the S1 alias defined anywhere.

Thanks for this "tip" I defined a alias for S1 and it work 100%

Tanks for your time and effort