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