Hi again thedbguy, I hope it is OK to add a piece to this ...
The solution works perfectly. The query and the report grouping give the counting and summing I asked for. The first column shows the number of races undertaken
and the second the number of points as shown in the attached report.
I've realised though that I've missed a point ...
When sort 1 is true, only the TOP values (asc) of sort1 are used to determine the sort so if sort1 = 9 and there are 12 records then just the 9 lowest 'points' should be counted and this becomes the sorted position.
In the report, Miles has 'raced' 9 times so all his points are counted. Brian has 'raced' 12 times so his TOP (asc) 'points' would be 23. The sort order, in this case remains the same.
To keep things simple (!), I've tried to add another sort column something like this but end up with invalid syntax ...
sort3:iif([sort1], top [sort1] points,0) or like this
sort3:iif([sort1], top sort1 points,0)
Here's the QDF as it stands right now, grateful as always for your or other insight ...
'11/23/2019 - modified by
thedbguy@gmail.com
strwall = strwall & " TRANSFORM Sum(tblResults.pos) AS SumOfpos "
strwall = strwall & " SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1, IIf([sort1],[points],0) as sort2, "
strwall = strwall & " [membername] & Space(30) & '.' & [seriesname]& Space(30)& '..' & [tblseries].[rtc] AS HELM, count (tblResults.pos) as Raced, sum (tblResults.pos) as Points "
strwall = strwall & " FROM (tblRace INNER JOIN tblResults ON tblRace.raceauto=tblResults.raceID)"
strwall = strwall & " INNER JOIN tblSeries ON tblRace.seriesID=tblSeries.seriesID "
strwall = strwall & " WHERE (((tblRace.seriesID)= " & Me!Combo3 & "))"
strwall = strwall & " GROUP BY [tblresults].[membername],[tblseries].[seriesname],[tblRace].[seriesID], [tblSeries].[seriesname],[tblSeries].[rtc] "
strwall = strwall & " ORDER BY format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm') "
strwall = strwall & " PIVOT format([date],'mm/dd') & ' ' & format([tblresults].[time],'hh:mm');"
Set qdf = db.CreateQueryDef("qryWallQuery", strwall)
qdf.Close