Select the Runners best time for a given race

[GoodyGoody]

Hi, I have a table RaceEventRunners and I want to select the runner's best time for a given race. I have written the following nested SQL but ACCESS returns 'Syntax error in join operation'.

SELECT R.RaceEvent, R.RaceRunner, R.RaceTimeSecs, R.Racetime
FROM
(raceeventrunners R
INNER JOIN
(SELECT raceevent, racerunner, MIN(racetimesecs) AS MinTime FROM raceeventrunners X
GROUP BY X.raceevent, X.racerunner)
R ON R.raceevent = X.raceevent AND R.RaceRunner = X.RaceRunner)
where R.Racetimesecs = X.MinTime;

Can anyone help please? Thanks in advance Stephen

 

[theDBguy]

Hi. What do you mean for a given race? Do runners have multiple race times for each race?

 

[Deleted member 147267]

I wondered the same thing. Regardless, you cannot have spaces in any name unless it is enclosed in brackets, so [raceeventrunners R] or X not raceeventrunners R.
I think it would be best if you read up on naming things:

http://access.mvps.org/access/general/gen0012.htm

https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

 

[isladogs]

The 'spaces' are actually setting the table aliases, R and X. I prefer to use … AS R …as I think its clearer but it works perfectly without.

Like the previous responders, I'd like clarity about what a 'race' means.
Is it e.g. all recorded times for 200m or for all times in heats, semi final, final in the same meeting or …. ?

 

[GoodyGoody]

Hi Guys,

The 'R' is a way of giving a table name an alias so that's a red herring. The first select statement works no problem. Re the table structure, the primary key is Raceevent and Racerunner. Each runner can run each event mulitple times (imagine a Saturday park run) but I want to see the fastest time (the least seconds) that a runner has ever run a given event. I hope that helps. Thanks, Stephen

 

[isladogs]

Tables can only have one primary key. Did you mean you have a composite PK consisting of two fields? If so, why?

I think the issue may be the R at the start of the penultimate line which I think is superfluous. If that's not it, please post a stripped down copy removing confidential data. Zip it as you have less than ten posts

 

[GoodyGoody]

Hi, Yes, it's a composite primary key. The R is the alias because I'm referring to the table twice so it would be unreadable without it. Put in 'AS R' if that's clearer. The SELECT statement runs fine on its own. A runner has run a race, say, 4 times and I want to know his fastest time for that race. The problem is in the INNER JOIN statement but I can't see anything wrong with it. Thanks Stephen

 

[arnelgp]

SELECT RaceEventRunners.RaceEvent, 
RaceEventRunners.RaceRunner, 
Min(RaceEventRunners.RaceTimeSecs) AS MinOfRaceTimeSecs
FROM RaceEventRunners
GROUP BY RaceEventRunners.RaceEvent, RaceEventRunners.RaceRunner;

 

[GoodyGoody]

Thanks. I did that as that was what I thought the min function would do but it returns all the times for each race/runner combination not the MIN. Hence trying something different. No idea why the MIN function returns all records?

RaceEvent RaceRunner MinOfRaceTimeSecs
1 Alan 1819
1 Alan 1862
1 Alan 1886
1 Alan 2088
1 Amy 1600
1 Andrew 1471
1 Andrew 1723
1 Ben 1175
1 Brian 1368
1 Brian 1359
1 Charles 1447

 

[arnelgp]

see the Grouping of the query.

 

[GoodyGoody]

Hi, not sure what you mean? I would assume that it would group by RaceEvent and RaceRunner and select the MIN value if there are multiple entries. The MIN option isn't doing anything. If I take it out I get the same results set. So that was why I tried the JOIN sub-select query. Canyou go back to that and see if you can see anything wrong with the syntax on the JOIN query as that is what Access is complaining about.

 

[isladogs]

It works for me using your example data and arnel's query.
It would have helped to have posted the data earlier.

 

[arnelgp]

view your query in Design view.
do you have same design as I have.

 

[GoodyGoody]

Sorry, I'm being an idiot. One of the problems having COMBO fields built into tables in access is that you are not necessarily seeing the actual data. It turns out that the RaceEvent field is unique and the '1' is from the linked table in the combo box not the actual underlying data. I'm still curious as to why the JOIN doesn't work as I can't see an issue with it so any thoughts on the JOIN query would be really appreciated but thanks for all your input.

 

[isladogs]

You've found out the hard way ... NEVER use table level lookups!

This works ...

SELECT R.RaceEvent, R.RaceRunner, R.Racetimesecs
FROM raceeventrunners AS R INNER JOIN (SELECT raceevent, racerunner, MIN(racetimesecs) AS MinTime FROM raceeventrunners 
GROUP BY raceevent, racerunner)  AS X ON (R.RaceRunner = X.RaceRunner) AND (R.raceevent = X.raceevent)
WHERE (((R.Racetimesecs)=X.MinTime));

as does this ...

SELECT R.RaceEvent, R.RaceRunner, Min(X.RaceTimeSecs) AS MinOfRaceTimeSecs
FROM RaceEventRunners AS R INNER JOIN RaceEventRunners AS X ON (R.RaceEvent = X.RaceEvent) AND (R.RaceRunner = X.RaceRunner)
GROUP BY R.RaceEvent, R.RaceRunner;

...but why do something unnecessarily complicated when a simple solution works? No need for a self join query here

 

[Deleted member 147267]

The 'spaces' are actually setting the table aliases, R and X. I prefer to use … AS R …as I think its clearer but it works perfectly without.

I see that all the time, but forgot because it's not my style.

 

[isladogs]

Hi Micron
I made exactly the same 'mistake' a few months ago and someone else pointed out to me that AS wasn't necessary .
I rarely alias tables except for self join queries but, when I do alias, I always use AS .... as does the query designer