Sort or Group by value (1 Viewer)

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Folks,


I have a report that's done sterling service for some years (Access 2003). The detail is currently sorted by info in the report record source, a number descending (raced) and then another number ascending (pos). There's a new requirement to add another sort (or group?) before these two. It's to test the value of 'raced' against a value obtained by a DCount in the report header and then used to populate a text box in the header (txtlatestrtc) ...

I have tried things like =[Raced]>=[txtlatestrtc] in both the report Order By and Sorting and Grouping areas. I generally get "Reserved Error" ...

Am I trying to do the impossible?


Thanks for any insight


Brian
 

vba_php

Banned
Joined
Oct 6, 2019
Messages
1,974
There's a new requirement to add another sort (or group?) before these two. It's to test the value of 'raced' against a value obtained by a DCount in the report header and then used to populate a text box in the header (txtlatestrtc) ...
reports are my weakest area of expertise here, but I'll try to help. is "raced" a field in your table? if it is, I think ur gonna want to write an equation in the control source of the "raced" textbox, which is what it looks like ur attempting. what r the equations written in the other controls' control sources that ur referring to that you have to reference?
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Thanks for that vba-php


The report is run from a querydef of a transformed/pivoted SQL query in the form that drives the report. 'raced' is not in a table, it's the count of a table field 'pos'. 'raced' and 'pos' are both currently used to sort the report. The report gets additional data running DCount scripts to get additional data to display in the report - one of these DCounts is used to populate a text box and I was hoping to use that for a new sort (or group if that's more appropriate).



The querydef is (for me!) very complex so I've avoided looking there for this new sort value ...


Hope this makes sense and offers an answer to the questions in your response ... I think it's been said that one can only sort on values that are in the report's underlying query ... I just hoped that I wouldn't need to go there!


Thanks again, Brian
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,564
Hi Brian. Welcome to AWF! You should be able to sort on calculated values as well. Of course, if you can include the same calculation in the record source, then it simply becomes a column for sorting.
 

vba_php

Banned
Joined
Oct 6, 2019
Messages
1,974
Of course, if you can include the same calculation in the record source, then it simply becomes a column for sorting.
I would certainly with that! always easier to do calculations that way than try to modify display objects.


'raced' is not in a table, it's the count of a table field 'pos'. 'raced' and 'pos' are both currently used to sort the report
I don't think this makes much sense Brian. If 'raced' is just a count of the number of records (transformed or not) in a query, how could u possibly use that as sort criteria? it would return a single number and I'm sure none of the data in ur query would match that number. why would it? if 'pos' IS a field in the query, sorting on it certainly makes sense and should work fine.
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Sorry not to be clearer ... I'm not a programmer, just an occasional dabbler.


It's all about whether someone has qualified or not for an award. To qualify for that award, the number 'raced' has to meet a criteria. Each time the report is run, the number 'raced' is either below, equal to or above the criteria number. The criteria number is established by a DCount in the report header.



The sort (or group?) is trying to establish all the records that meet the criteria and those that do are then sorted then sort by 'raced' (desc) and 'pos' (asc). That's the first group in the report.



After the first group, those records that don't meet the criteria are then sorted by 'raced' and 'pos'.



The more races that are run, the more people should qualify.


I hope that's a better explanation of what I'm trying to do


Thank you, Brian
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Sorry for the delay and hope the attached helps. It shows the code which supported the original report where the SQL included the fields 'raced' and 'pos' used to sort the report. No problem with that. With the new requirement to first sort on a calculated criteria, it's here (sorry not in the report header) that I added the DCounts to determine that criteria. The criteria value = (TR minus AR).

I'd hoped to get the report to sort by 'raced' >= (TR minus AR) . The records that met the criteria would then be sorted by 'pos' . Those records that did not meet the criteria would be sorted by 'raced' and then 'pos'.



Apologies once again that I've struggled to get across to you what I'm trying to do.


Regards, Brian
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
I've now managed to upload (rather than insert :rolleyes:) the code that drives the report in question. Trust it makes sense and help find a way forward. Thanks again, Brian
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,564
I've now managed to upload (rather than insert :rolleyes:) the code that drives the report in question. Trust it makes sense and help find a way forward. Thanks again, Brian
Hi Brian. Since we're trying to sort/group the data, I think it would be better if we could see the data. Are you able to post a small sample copy of your db with test data? You can also add an Excel mockup of how you want the same data sorted in the report. By looking at the current report in the sample db and comparing it with the mockup Excel spreadsheet, we can easily determine what you're trying to do. Looking at code alone doesn't really give a good picture of the desired outcome. Thanks.
 

briantjohnson

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


Thanks for coming in. That's a great offer, thank you. The current size of the database is 60Mb. Is that too large? If it is, I could seek to produce a version with most of the forms and reports stripped out. Sorry but I know very little about spreadsheets other than simple one page things with no code.



Regards, Brian
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,564
Hi DBguy,

Thanks for coming in. That's a great offer, thank you. The current size of the database is 60Mb. Is that too large? If it is, I could seek to produce a version with most of the forms and reports stripped out. Sorry but I know very little about spreadsheets other than simple one page things with no code.

Regards, Brian
Hi Brian. Yes, that's too big. We just need enough sample data to see how it is sorting. So, we really only need the tables involved, the query for the report, and the one report itself. As for the spreadsheet, yes, a simple, no code, one page sheet with the sample data sorted as you would like it to show on the report is all we need for comparison.
 
Last edited:

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
OK. Smallest I can make the db is 3Mb after compact/repair. I see that the forum max file size is 3Mb for an mdb ... should I try to zip it?


Regards, Brian
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Zip file created with test db and Excel spreadsheet showing current sort and the sort I hope to achieve. Apologies to vba-php for confusing 'pos' and 'points'.


Form to select a series (Snowflake) is 'wallchart'. Report is 'seriesresults'


Thanks again everyone


Brian
 

Attachments

vba_php

Banned
Joined
Oct 6, 2019
Messages
1,974
Brain,

This is a pretty tough thing to do I think and I tried fooling around with ur file for a couple minutes and did not come up with anything. maybe dbGuy can help better than I can because you can't ORDER BY a field that is calculated by an aggregate function like SUM() and COUNT(), which are the 2 fields ur wanting to include form the sort criteria
"tr - ar". you *can* include the calculated field "tr - ar", however, in the query by simply adding this to ur SQL statement behind combo3:
Code:
strwall = strwall & ", " & tr - ar & " AS tr_ar "
my only suggestion here would be to write a separate module of code to loop thru the records once the query is created, write the data to a temporary table and sort it accordingly, then use that table as the recordsource for the report.

but regardless of what is done, there has got to be an easier way for you to get what you want. it seems to me that ur data should be easier to work with than this, thus the need for a crosstab query should not even be necessary.
 

briantjohnson

Registered User
Joined
Nov 21, 2019
Messages
26
Hi vba-php



Thanks for this, thought it was not going to be easy!



Right now I am doing Order By sorting the report on 'raced' and 'points' which are both calculated in this line of the query so it seems that at least some element of calculation can be used to sort ...


strwall = strwall & " SELECT [membername] & Space(30) & '.' & [seriesname]& Space(30)& '..' & [tblseries].[rtc] AS HELM, count (tblResults.pos) as Raced, sum (tblResults.pos) as Points "


I'm going to play with the SQL as you suggest. I have found, in the past that it's sometimes better to drop results into another table and reselect ...



Crosstabs ... I don't find them easy to understand ... but it was part of a query/ report solution for this report that I found on an Access support web site some years ago .... and has worked well until now! Not using a crosstab would be better if I could achieve it ...



Thanks again, Brian
 

vba_php

Banned
Joined
Oct 6, 2019
Messages
1,974
Not using a crosstab would be better if I could achieve it ...
well ur sample file isn't that big and a data structure wouldn't be difficult I wouldn't think. If you want, I would be willing to restructure ur stuff for free, as there isn't really enuf there to warrant a payment request.
 

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

Top Bottom