Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-23-2019, 11:48 AM   #31
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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

briantjohnson is offline   Reply With Quote
Old 11-23-2019, 12:20 PM   #32
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,595
Thanks: 58
Thanked 1,446 Times in 1,427 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sort or Group by value

Quote:
Originally Posted by briantjohnson View Post
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...
Attached Files
File Type: zip racetest.zip (190.0 KB, 7 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-23-2019, 12:43 PM   #33
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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

briantjohnson is offline   Reply With Quote
Old 11-23-2019, 01:19 PM   #34
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

... it all seems to be changes to the select statement ... I cant see any other changes ...
briantjohnson is offline   Reply With Quote
Old 11-23-2019, 01:25 PM   #35
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,595
Thanks: 58
Thanked 1,446 Times in 1,427 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sort or Group by value

Quote:
Originally Posted by briantjohnson View Post
... it all seems to be changes to the select statement ... 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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-23-2019, 01:30 PM   #36
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

Great. Think I just need to change my DCounts to give Int values as testing for other series comes up with fractional values ...
briantjohnson is offline   Reply With Quote
Old 11-23-2019, 01:36 PM   #37
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,595
Thanks: 58
Thanked 1,446 Times in 1,427 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sort or Group by value

Quote:
Originally Posted by briantjohnson View Post
Great. Think I just need to change my DCounts to give Int values as testing for other series comes up with fractional values ...
Okay. Good luck.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
briantjohnson (11-24-2019)
Old 12-05-2019, 01:30 AM   #38
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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
Attached Files
File Type: pdf seriesresults (2).pdf (26.8 KB, 3 views)

briantjohnson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort, group and sort again kind of problem. KingBroil Queries 1 05-31-2016 05:50 AM
how do i sort on group subtotals? martinr Reports 0 09-21-2014 05:39 PM
Group but do not Sort RichMorrison Reports 2 11-29-2006 12:16 PM
How do I sort within a group? llkhoutx Reports 3 06-30-2006 08:40 AM
group by and sort problem naoliveira Queries 2 01-12-2006 02:53 AM




All times are GMT -8. The time now is 05:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World