Select the Runners best time for a given race (1 Viewer)

GoodyGoody · Sep 11, 2019

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 · Sep 11, 2019

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

Micron · Sep 11, 2019

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 · Sep 11, 2019

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 · Sep 11, 2019

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 · Sep 11, 2019

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 · Sep 11, 2019

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 · Sep 11, 2019

Code:

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

GoodyGoody · Sep 11, 2019

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 · Sep 11, 2019

see the Grouping of the query.

GoodyGoody · Sep 11, 2019

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 · Sep 11, 2019

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

arnelgp · Sep 11, 2019

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

GoodyGoody · Sep 11, 2019

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 · Sep 11, 2019

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

This works ...

Code:

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 ...

Code:

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

Micron · Sep 11, 2019

isladogs said:
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 · Sep 11, 2019

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

Select the Runners best time for a given race (1 Viewer)

GoodyGoody

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

isladogs

MVP / VIP

GoodyGoody

Registered User.

isladogs

MVP / VIP

GoodyGoody

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

GoodyGoody

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

GoodyGoody

Registered User.

isladogs

MVP / VIP

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

GoodyGoody

Registered User.

isladogs

MVP / VIP

Micron

AWF VIP

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread