Query Help (1 Viewer)

bouncingtigers

Registered User.
Local time
Today, 06:02
Joined
Aug 9, 2016
Messages
18
I have a team of athletes who compete in 3 different sporting events.

I am trying to work out how I can get my query to only look at the 5 most recent events

I want to score them as a percentage variance based on target times set to complete each sporting event.

Any help is greatly appreciated

My table is set out as follows:

Name - DateOfEvent - Hurdle - Run - Bike



Than you in advance
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:02
Joined
Jul 9, 2003
Messages
12,458
That's a spreadsheet. You need to think "MS Access"

Sent from my SM-G925F using Tapatalk
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:02
Joined
Feb 19, 2013
Messages
12,906
something like

Code:
 SELECT T.*
 FROM myTable T INNER JOIN (SELECT Top 5 DateOfEvent FROM (SELECT DISTINCT DateOfEvent FROM myTable) ORDER BY DateOfEvent) D ON T.DateOfEvent=D.DateOfEvent
 ORDER BY T.DateOfEvent

Note, your table does not look normalised so you may find your calculations difficult to do and if these are your real field names, be aware that Name is a reserved word, using it as a field name is likely to cause problems in the future, suggest change to something like 'participant' or 'athlete'
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:02
Joined
Jul 9, 2003
Messages
12,458
Two New Tables:-

Slight change to Results Table:-
fName - fDateOfEvent - fEventID

Event Table:-
fEventID --- fEventDescription

Competitor Table:-
fCompetitorID --- fCompetitorName


Results Table:-
fName - fDateOfEvent - fEventID

Results Table - Data Map
fName - fDateOfEvent --- fEventID
fCompetitorID - Date - fEventID

Example Data:-

Results Table:-
fName - fDateOfEvent --- fEventID
142 ------- 2016_05_06 ------ 3
142 ------- 2016_05_06 ------ 8
142 ------- 2016_05_06 ------ 2
148 ------- 2016_05_06 ------ 1
148 ------- 2016_05_06 ------ 2
148 ------- 2016_05_06 ------ 3
148 ------- 2016_05_06 ------ 8

Sent from my SM-G925F using Tapatalk
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:02
Joined
Jul 9, 2003
Messages
12,458
Now all you need is a simple Top 5 Query....

Sent from my SM-G925F using Tapatalk
 

bouncingtigers

Registered User.
Local time
Today, 06:02
Joined
Aug 9, 2016
Messages
18
something like

Code:
 SELECT T.*
 FROM myTable T INNER JOIN (SELECT Top 5 DateOfEvent FROM (SELECT DISTINCT DateOfEvent FROM myTable) ORDER BY DateOfEvent) D ON T.DateOfEvent=D.DateOfEvent
 ORDER BY T.DateOfEvent

Note, your table does not look normalised so you may find your calculations difficult to do and if these are your real field names, be aware that Name is a reserved word, using it as a field name is likely to cause problems in the future, suggest change to something like 'participant' or 'athlete'

Thank you for the reply, not really sure what I am doing with this SQL.

I created a new query and modified your code to this:

Code:
SELECT T.*
 FROM tblSourceData T INNER JOIN (SELECT Top 5 Competition Date FROM (SELECT DISTINCT Competition Date FROM tblSourceData) ORDER BY Competition Date) D ON T.competition date=D.competition date
 ORDER BY T.competition date

But I keep getting a syntax error for missing operator.

Thanks in advance
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:02
Joined
Feb 19, 2013
Messages
12,906
if you have spaces in field names you have to use square brackets around the name. e.g Competition Date needs to be [Competition Date]
 

bouncingtigers

Registered User.
Local time
Today, 06:02
Joined
Aug 9, 2016
Messages
18
Thank you CJ

I didnt realise it was so sensitive.

Its doing something but not 100% sure what exactly - Could you break it down slightly so I understand how its doing what I need

I get the gist but a little explanation so I learn from my experience if thats OK?

Why does it start with SELECT T.*, and not the table name of tblsourcedata?

SELECT T.*
FROM tblSourceData T INNER JOIN (SELECT Top 5 Competition Date FROM (SELECT DISTINCT Competition Date FROM tblSourceData) ORDER BY Competition Date) D ON T.competition date=D.competition date
ORDER BY T.competition date
 

Minty

AWF VIP
Local time
Today, 14:02
Joined
Jul 26, 2013
Messages
8,040
Whilst you are still learning I would adopt a naming convention, then spaces etc. won't hurt you in the long run. As you may have found out adding [ ] around every instance of a field/table name is a pain in the backside. Have a look here http://access.mvps.org/access/general/gen0012.htm
And as has already been pointed out your data is not normalised, if you need to add another type of event you will have to rebuild any queries or forms to accommodate it.

The general rule of thumb is - if a field name implies a specific data element, e.g. bike, run, pogostick ... instead of a multitude of fields you should have an element type field instead - eg- EventType.

Finally CJ used a way of not having to type the table name in full by using a table alias see the red T below.
SELECT T.*
FROM tblSourceData T INNER JOIN
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:02
Joined
Feb 19, 2013
Messages
12,906
I didnt realise it was so sensitive.
yeah, the language has to be clear, unambiguous and follow a specific syntax - hence the warning about reserved words. I'll also add the warning about using non alphanumeric characters (eg. # instead of something like No or Count) - # for example is used to convert the string format of a date to a date/time type.

Why T? - because I have used aliasing - T is the alias (FROM tblSourceData T)

Why use aliasing? it cuts down on typing and in a situation such as this where the same table is referred to in different contexts, identifies which context you are referring to (i.e. unambiguous above)

You can also write 'FROM tblSourceData T' as 'FROM tblSourceData AS T'

the query is in 3 parts

(SELECT DISTINCT [Competition Date] FROM tblSourceData)

could also be written as

(SELECT [Competition Date] FROM tblSourceData GROUP BY [Competition Date])

but group by's would only normally be used when you are summing/counting one or more fields.

This is used to only select each distinct competition date

Next we just need the top 5 of these which is what this bit does

(SELECT Top 5 [Competition Date] FROM (SELECT DISTINCT ]Competition Date] FROM tblSourceData) ORDER BY [Competition Date])

we order it to ensure we get the latest 5 - if we didn't we would get a random top 5.

one problem with the top 5 syntax is that if you had 6 events on the same day, then all 6 would be returned. This would be solved by using a different field which uniquely identifies a specific event such a event name+location+year

so now we have the top 5 event dates, you can join that back to the original table, joining on the date to bring back all the athletes that performed on those days.

The query design window helps you to build queries, but it has its limitations.

If you wanted to do it in individual parts, you can. Put the first bit of code discussed in a query called say qry1 (without the outer brackets). then create a 2nd query (qr2) with sql

SELECT Top 5 [Competition Date] FROM qr1 ORDER BY [Competition Date]

and then a 3rd query inner joining to qry2 instead of the bit in brackets
 

Users who are viewing this thread

Top Bottom