Go Back   Access World Forums > Microsoft Access Discussion > Reports

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

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

briantjohnson is offline   Reply With Quote
Old 11-21-2019, 03:26 AM   #2
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Sort or Group by value

Quote:
Originally Posted by briantjohnson View Post
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?
vba_php is offline   Reply With Quote
Old 11-21-2019, 05:13 AM   #3
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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

briantjohnson is offline   Reply With Quote
Old 11-21-2019, 05:24 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,238
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Sort or Group by value

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.
__________________
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 offline   Reply With Quote
Old 11-21-2019, 06:34 AM   #5
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Sort or Group by value

Quote:
Originally Posted by theDBguy View Post
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.


Quote:
Originally Posted by briantjohnson View Post
'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.
vba_php is offline   Reply With Quote
Old 11-21-2019, 07:22 AM   #6
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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 is offline   Reply With Quote
Old 11-21-2019, 07:55 AM   #7
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Sort or Group by value

Quote:
Originally Posted by briantjohnson View Post
I hope that's a better explanation of what I'm trying to do
not really my friend. maybe dbGuy can chime here, but look at this image and do the same for us (us ms paint or something) so we understand what you want. make sure the code / function syntax is fully visible in ur photo.
Attached Images
File Type: jpg indicate what you want.jpg (96.2 KB, 13 views)

vba_php is offline   Reply With Quote
Old 11-21-2019, 10:55 AM   #8
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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 is offline   Reply With Quote
Old 11-21-2019, 02:03 PM   #9
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

I've now managed to upload (rather than insert ) the code that drives the report in question. Trust it makes sense and help find a way forward. Thanks again, Brian
Attached Files
File Type: doc Sorting Report.doc (29.5 KB, 6 views)
briantjohnson is offline   Reply With Quote
Old 11-21-2019, 02:08 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,238
Thanks: 63
Thanked 1,678 Times in 1,633 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
I've now managed to upload (rather than insert ) 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.
__________________
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 offline   Reply With Quote
Old 11-21-2019, 02:20 PM   #11
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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
briantjohnson is offline   Reply With Quote
Old 11-21-2019, 02:27 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,238
Thanks: 63
Thanked 1,678 Times in 1,633 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 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.
__________________
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.

Last edited by theDBguy; 11-21-2019 at 02:34 PM.
theDBguy is offline   Reply With Quote
Old 11-21-2019, 02:31 PM   #13
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

Right, I've started work on that.



Thanks again, Brian
briantjohnson is offline   Reply With Quote
Old 11-21-2019, 02:35 PM   #14
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,238
Thanks: 63
Thanked 1,678 Times in 1,633 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
Right, I've started work on that.

Thanks again, Brian
Sounds good. I'll check back tomorrow.
__________________
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 offline   Reply With Quote
Old 11-21-2019, 03:37 PM   #15
briantjohnson
Newly Registered User
 
Join Date: Nov 2019
Posts: 26
Thanks: 3
Thanked 0 Times in 0 Posts
briantjohnson is on a distinguished road
Re: Sort or Group by value

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 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 06:03 PM.


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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World