seeking query advice

[santoni]

I'm rephrasing an earlier post. From my tables I have managed to get
track results into one table. I have runners with race times from
different meets. One runner has compteted in 15 meets and a second
runner in seven. How can I compare their averages for the five meets
they competed against one another. I also want to make other, similar
1-to-1 comparisons.

Thank you.

[Jason Lepack]

Without having an example of your table structure, I'm going to step
out on a very thin limb and assume that your database is well
designed.

tbl_runner:
runner_id
runner_name
etc.

tbl_location:
location_id
location_name

tbl_race:
race_id
location_id
race_date

tbl_run_race:
run_race_id
runner_id
race_id
run_race_time

A query to get each runner average would be:
SELECT R.runner_name,
R.runner_id,
avg(RR.run_race_time) AS avg_time
FROM tbl_runner AS R
INNER JOIN tbl_run_race AS RR
ON R.runner_id = RR.runner_id
GROUP BY R.runner_name
R.runner_id
ORDER BY R.runner_name ASC

Then if you had a subform on your form that was based on this query,
then you could use two combo boxes on the main form to create a
dynamic filter of this recordset using the afterupdate event of the
combo boxes.

Cheers,
Jason Lepack

On Mar 16, 9:30 am, "santoni" <[e-mail]:

I'm rephrasing an earlier post. From my tables I have managed to get
track results into one table. I have runners with race times from
different meets. One runner has compteted in 15 meets and a second
runner in seven. How can I compare their averages for the five meets
they competed against one another. I also want to make other, similar
1-to-1 comparisons.

Thank you.

[Tom van Stiphout]

On 16 Mar 2007 06:30:09 -0700, "santoni" <sa*******@gmail.comwrote:

OK, I envision a table tblRaces like this:

MeetID RunnerID RaceTime
1 1000 123.5
1 1001 124.6
1 1002 124.7
1 1003 124.8
2 1001 130.1
2 1002 128.6
2 1004 127.9
3 1001 123.4

Then to compare two runners (in this case 1001 and 1002), I create 3
queries:
qryMeets_1001:
SELECT tblRaces.MeetID, tblRaces.RunnerID, tblRaces.RaceTime
FROM tblRaces
WHERE (((tblRaces.RunnerID)=1001));

qryMeets_1002:
SELECT tblRaces.MeetID, tblRaces.RunnerID, tblRaces.RaceTime
FROM tblRaces
WHERE (((tblRaces.RunnerID)=1002));

And finally to get the data about the races they both ran in:
SELECT qryMeets_1001.MeetID, qryMeets_1001.RunnerID,
qryMeets_1002.RunnerID, qryMeets_1001.RaceTime, qryMeets_1002.RaceTime
FROM qryMeets_1001 INNER JOIN qryMeets_1002 ON qryMeets_1001.MeetID =
qryMeets_1002.MeetID;

Using this query, you can perform your analysis, for example
calculating the Avg(RaceTime) or the time for 1001 minus the time for
1002.

-Tom.

>I'm rephrasing an earlier post. From my tables I have managed to get
track results into one table. I have runners with race times from
different meets. One runner has compteted in 15 meets and a second
runner in seven. How can I compare their averages for the five meets
they competed against one another. I also want to make other, similar
1-to-1 comparisons.

Thank you.