Ranking swimmers on their time

[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

And received the following answer

I have Trace as my race table, and t_points as the rank to points lookup
table. tested and works with my kludged data under Access 2007

q_rank:
SELECT Trace.swimmer, 1-Sum([trace].[timesecs]>[trace_1].[timesecs]) AS
rank, Trace.timesecs
FROM Trace, Trace AS Trace_1
GROUP BY Trace.swimmer, Trace.timesecs;


SELECT q_rank.rank, q_rank.swimmer, q_rank.timesecs, t_points.points
FROM q_rank INNER JOIN t_points ON q_rank.rank = t_points.rank
ORDER BY q_rank.rank, q_rank.swimmer;

BUT
The q-rank return 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

ANOTHER QUESTION

in the statement what is the difference between [trace] and [trace_1]

I would really appreciate your help as I am VERY new to Access and need this
to complete the Swimmeet database

Thank a lot

Frank

 

[MGFoster]

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

And received the following answer

I have Trace as my race table, and t_points as the rank to points lookup
table. tested and works with my kludged data under Access 2007

q_rank:
SELECT Trace.swimmer, 1-Sum([trace].[timesecs]>[trace_1].[timesecs]) AS
rank, Trace.timesecs
FROM Trace, Trace AS Trace_1
GROUP BY Trace.swimmer, Trace.timesecs;


SELECT q_rank.rank, q_rank.swimmer, q_rank.timesecs, t_points.points
FROM q_rank INNER JOIN t_points ON q_rank.rank = t_points.rank
ORDER BY q_rank.rank, q_rank.swimmer;

BUT
The q-rank return 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

ANOTHER QUESTION

in the statement what is the difference between [trace] and [trace_1]

I would really appreciate your help as I am VERY new to Access and need this
to complete the Swimmeet database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"As trace_1" is an Alias for the table Trace when it is used in a self
referencing JOIN. The person who gave you that query used SQL89 syntax
and neglected to put in the joining columns in a WHERE clause, which
makes the result set a Cartesian product - all the rows in one table
joined to all the rows in the other table.

Try this:

SELECT swimmer, timesecs, (SELECT COUNT(*) FROM Trace WHERE timesecs<
T1.timesecs)+1 As Rank
FROM Trace As T1
ORDER BY timesecs

T1 is the alias for the Trace table in the main query. It is referenced
in the sub-query so the main query's timesecs can be compared to the sub
query's timesecs.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSehkQoechKqOuFEgEQLJ7wCfehlnONWHvb+gSdAJKFcAisa3J40AoJzC
+a3aeeXQLXza+R2iyaYhQvdZ
=614v
-----END PGP SIGNATURE-----

 

[Dale Fye]

It would help if we knew your table structure. I don't see any reference to
a meet, or race in the query so I seriously doubt what you have would work.

The purpose of Trace_1 is to give you a second copy of the recordset, so you
can compare each swimmers time against all of the times. This type of join
(no join at all) is called a Cartesian Join, and it matches every record in
Trace against every record in Trace_1. So, assuming your table Trace contains
three records:

Swimmer Time
A 00:56.23
B 00:56.95
C 00:55.8

The expression:

FROM Trace, Trace as Trace_1

Would actually return a recordset that looks like the following:

Trace Trace Trace_1 Trace_1
Swimmer Time Swimmer Time
A 00:56.23 A 00:56.23
A 00:56.23 B 00:56.95
A 00:56.23 C 00:55.8
B 00:56.95 A 00:56.23
B 00:56.95 B 00:56.95
B 00:56.95 C 00:55.8
C 00:55.80 A 00:56.23
C 00:55.80 B 00:56.95
C 00:55.80 C 00:55.8

The expression

Sum(Trace.TimeSpec > Trace_1.timespec)

will evaluate swimmers A's time (Trace.TimeSpec) against each of the other
swimmers time, and will return a value of 0 or -1, depending on whether
swimmer A beat the opponent or not. When you sum that value over all of the
opponents you will get a value (negative) which indicates the number of
swimmers whose time was faster than swimmer A.

However, as I indicated above, since I don't see any reference to the meet
or race in the query, I'm not sure how this would work. Try substituting the
following:

Sum(iif(Trace.TimeSpecs > Trace_1.Timespecs, -1, 0))

----
HTH
Dale

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

And received the following answer

I have Trace as my race table, and t_points as the rank to points lookup
table. tested and works with my kludged data under Access 2007

q_rank:
SELECT Trace.swimmer, 1-Sum([trace].[timesecs]>[trace_1].[timesecs]) AS
rank, Trace.timesecs
FROM Trace, Trace AS Trace_1
GROUP BY Trace.swimmer, Trace.timesecs;


SELECT q_rank.rank, q_rank.swimmer, q_rank.timesecs, t_points.points
FROM q_rank INNER JOIN t_points ON q_rank.rank = t_points.rank
ORDER BY q_rank.rank, q_rank.swimmer;

BUT
The q-rank return 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

ANOTHER QUESTION

in the statement what is the difference between [trace] and [trace_1]

I would really appreciate your help as I am VERY new to Access and need this
to complete the Swimmeet database

Thank a lot

Frank