Sort or Group by value (1 Viewer)

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
That's a great offer, thank you. Do you thin 'restructuring' means 'just' the form/query/report? I'd be a bit nervous about changing the tables structure if there's a risk that other queries/code/forms/reports might need to be changed to suit.



I've been looking at the SQL. I can't see how to include your statement in the text ...

strwall = strwall & ", " & tr - ar & " AS tr_ar "
Thanks again for sticking with me :)
 

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,053
Do you thin 'restructuring' means 'just' the form/query/report? I'd be a bit nervous about changing the tables structure if there's a risk that other queries/code/forms/reports might need to be changed to suit.
it's been a while since i looked at ur file Brian, but I'm guessing not much would need to change. But what's the harm? The process is:

> Adam gives you new file
> Brian either wants it or not
> Brian uses either Adam's file or his old one

I've been looking at the SQL. I can't see how to include your statement in the text ...
strwall = strwall & ", " & tr - ar & " AS tr_ar "
I just added that code as a line in the group of "strwall = strwall ...." statements you are already using in the module. the math operation in the middle of the code "tr - ar" works fine cuz before the sql statement you have code to assign ur textbox values to vba varaibles "tr" and "ar".
Thanks again for sticking with me :)
We stick with people thru life here man! :p
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Adding that strwall line ... I didn't know you could do that ;) !

Result is that it adds the that ar-tr value to each record (that's the crosstab I suppose).

I then made the Order By criteria like this

iif([raced] >= (ar-tr/2), points, (raced desc, points asc)


It didn't work (see the attached file) but hey, it FEELS much closer to what I need!


Regards, Brian
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,730
Hi Brian. I downloaded your file, but I don't see an Excel spreadsheet showing what needs to be done.
 

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,053
Hi Brian. I downloaded your file, but I don't see an Excel spreadsheet showing what needs to be done.
i offered to restructure his stuff guy, but if ur gonna do it then please say so. we don't need 2 people doing this for him.

Brian, do u want one of us to give it a go with restructuring or not?
 
Last edited:

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Adam and Guy


So grateful that you are both taking an interest and as an aside, I'm learning lots about Access that I just never knew. Whilst I'd really like to be able to solve the report sorting with the structure I have and with your help I do seem to be very much closer but ... it just may not be possible :banghead: so a restructuring is probably the right way to go. I've attached the spreadsheet to show what I had hoped to achieve. It seems so easy to say but not easy to do ... :(


Thanks again, Brian
 

Attachments

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,053
guy chimed in Brian, so i'll let him do it. he always responds within minutes of new posts anyway. if he can't do it, let me know and I'll try it.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,730
Hi. I stepped out of the house. I'll take a look when I get back.

Sent from phone...
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,730
Adam and Guy


So grateful that you are both taking an interest and as an aside, I'm learning lots about Access that I just never knew. Whilst I'd really like to be able to solve the report sorting with the structure I have and with your help I do seem to be very much closer but ... it just may not be possible :banghead: so a restructuring is probably the right way to go. I've attached the spreadsheet to show what I had hoped to achieve. It seems so easy to say but not easy to do ... :(


Thanks again, Brian
Hi Brian. Thanks for posting the Excel spreadsheet. So, I hope I understood what you meant. Please take a look at the attached and let me know if it's correct or not. Hope it helps...
 

Attachments

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Hi Guy,


Not right as yet. The first sort should be to determine who has reached the target (ar-tr/2) ... that's where the value of 'raced' is 10 or above for the Snowflake Series. For those that reach that target, it's he who has the lowest number of points that should be on the top row followed by the next highest number of points and so on. These are races so the winner in first place gets 1 point, second place = 2 points and so on ...

For those that have not yet reached the target (those with a 'raced' value of 9 or below in this case), the sort is different - it's then the value of 'raced' and then the value of 'points' that determines the position in the report ...

Sorry not to make it clearer before


Regards, Brian
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,730
Hi Guy,


Not right as yet. The first sort should be to determine who has reached the target (ar-tr/2) ... that's where the value of 'raced' is 10 or above for the Snowflake Series. For those that reach that target, it's he who has the lowest number of points that should be on the top row followed by the next highest number of points and so on. These are races so the winner in first place gets 1 point, second place = 2 points and so on ...

For those that have not yet reached the target (those with a 'raced' value of 9 or below in this case), the sort is different - it's then the value of 'raced' and then the value of 'points' that determines the position in the report ...

Sorry not to make it clearer before


Regards, Brian
Hi Brian. Take 2...
 

Attachments

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
That's it! :):):). I will work on the report to get rid of the duplicate 'points' and 'raced' columns but can you say how you cracked it?



Regards, Brian
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,730
... it all seems to be changes to the select statement ... :confused: I cant see any other changes ...
Hi Brian. Not in front of a computer now, but you're right. I added the data for sorting into the query and then sorted the report with them.

Sent from phone...
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Great. Think I just need to change my DCounts to give Int values as testing for other series comes up with fractional values ...
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
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 [email protected]
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
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom