briantjohnson
Registered User.
- Local time
- Today, 05:45
- Joined
- Nov 21, 2019
- Messages
- 26
Hi all,
With help from Thedbguy, I have created quite a complex query feeding into a report. I now need to change the query to include a subset of the records for sorting and grouping. It looks to me like I need the Top predicate (asc) but is this so and where should it go within the query? The query looks like this:-
Dim frm As Form
Dim qdf As DAO.QueryDef, strwall As String
------------------------------------------------------------------------------------------
'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
--------------------------------------------------------------------------------
This piece determines the first sort ...
SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1,
and this piece the second ...
IIf([sort1],[points],0) as sort2, " ...
What I need for Sort2 is to sort on just the Top values (asc) rather than all the values that meet the Sort1 criteria. The value of Top is in this expression in the query:-
Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2
I've tried all sorts of 'Top' positioning but not found the answer yet. I'd be happy to have both All and the Top (asc) values for use in the subsequent report but would settle for just the Top values as Sort2.
I can upload a zipped copy of the current test db if that would help
Thanks for all and any views
Brian
With help from Thedbguy, I have created quite a complex query feeding into a report. I now need to change the query to include a subset of the records for sorting and grouping. It looks to me like I need the Top predicate (asc) but is this so and where should it go within the query? The query looks like this:-
Dim frm As Form
Dim qdf As DAO.QueryDef, strwall As String
------------------------------------------------------------------------------------------
'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
--------------------------------------------------------------------------------
This piece determines the first sort ...
SELECT Count([pos])>=" & Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2 & " as sort1,
and this piece the second ...
IIf([sort1],[points],0) as sort2, " ...
What I need for Sort2 is to sort on just the Top values (asc) rather than all the values that meet the Sort1 criteria. The value of Top is in this expression in the query:-
Int(Forms!wallchart!txttotalraces - Int(Forms!wallchart!txtabandoned)) \ 2
I've tried all sorts of 'Top' positioning but not found the answer yet. I'd be happy to have both All and the Top (asc) values for use in the subsequent report but would settle for just the Top values as Sort2.
I can upload a zipped copy of the current test db if that would help
Thanks for all and any views
Brian