UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Sort Count Field From Query In Report, Access 2016    
 
   
brastedhouse
post Oct 9 2019, 07:37 PM
Post#1



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


Greetings all, I have a query with a count column. When I use it in a report it won't sort. It is set as DESC in the query, but the report does not show the sort. Can someone explain a way to sort a count field in a report?

Thanks, Scott
Go to the top of the page
 
theDBguy
post Oct 9 2019, 07:49 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi Scott. To sort the records on a report, you'll have to use the report's sorting and grouping feature. Otherwise, reports just ignore the order by clause in the query.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
brastedhouse
post Oct 9 2019, 08:34 PM
Post#3



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


hi, yes but it will not sort on a count field. So query works, but reports ignores the query and won't sort on count, ugh.
Go to the top of the page
 
theDBguy
post Oct 9 2019, 09:38 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure what you mean by that. A report should be able to sort on any data it has.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Oct 9 2019, 09:43 PM
Post#5



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


hmm, PMFJI, i'm just wondering if you misunderstood what theDBguy said.

to put it a slightly different way: you can create a field in a query, or use an existing field from an underlying table, to sort on. but if you're going to use the query as the RecordSource of a report, it does not matter if you sort the records within the query or not. a report will never display records according to any sort order that was applied within the query.

in order to sort the records from a report's RecordSource, you have to use the Sorting And Grouping feature that is within the report.

now, if you did that, and find that the report does not seem to sort records - or not sort them as you expect - on the field(s) you chose in the Sorting and Grouping dialog, then that's a different issue. we'd want to know what is the Data Type of each field you're sorting on, and perhaps see a screenshot of the open Sorting And Grouping dialog.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Oct 9 2019, 10:41 PM
Post#6


UtterAccess VIP
Posts: 10,097
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but just curious ... how are you getting the count in the query? Is the result numeric? Could you show us the SQL of the query?

I agree, of course, with the others, that if the count field is a numeric type and the sort is applied correctly (as, for example, tina indicated) then the order in the report should be as you require.

If this is still an issue, perhaps you could post a cutdown db with relevant objects and example data (zipped), and someone could take a look and see if there is something we are missing about how you are doing this.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
brastedhouse
post Oct 9 2019, 11:06 PM
Post#7



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


I'm sorry, I know that you all know miles more about this than I do. That's why I come here for help. But I have tried every way possible to sort these count numbers in a report. They do not sort. If I move the fields from a footer to detail I loose the count. If I move them to the footer the count comes back but no sort. I have a header and footer where the fields reside. When I add a sort in the report of the query count field it does nothing. So unfortunately, you cannot always sort on a field because it is in the report data. I am looking at it right here and it is not sorting.

SQL for query with count field:

CODE
SELECT Count(([callTableFK])) AS CallCount, tblCall.callType, ([deptMemberFirst] & " " & [deptMemberLast]) AS memberName, (Year([callDate])) AS callYear, tblDeptMember.deptMemberID
FROM tblLUCallType INNER JOIN (tblDeptMember INNER JOIN (tblCall INNER JOIN tblCallMember ON tblCall.callID = tblCallMember.callTableFK) ON tblDeptMember.deptMemberID = tblCallMember.callMemberFK) ON tblLUCallType.callTypeID = tblCall.callType
GROUP BY tblCall.callType, (Year([callDate])), tblDeptMember.deptMemberID, tblDeptMember.deptMemberLast, tblDeptMember.deptMemberFirst, tblLUCallType.callTypeID;


callCount is the field I am trying to sort on. It is a count of a foreign key long integer field.

Thanks, Scott
Go to the top of the page
 
projecttoday
post Oct 9 2019, 11:09 PM
Post#8


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


Is this a saved query? By that I mean you developed it separately from the report and then saved it and it is listed among the queries in the navigation pane.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Oct 9 2019, 11:13 PM
Post#9



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


Yes, it is a saved query that I made the Record Source of the report.
This post has been edited by brastedhouse: Oct 9 2019, 11:14 PM
Go to the top of the page
 
projecttoday
post Oct 9 2019, 11:41 PM
Post#10


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


I just did a test and I was able to sort on a count field. The grouping field did not appear in the choices, though.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Oct 10 2019, 08:04 AM
Post#11



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


good morning, how did you do it? I am still experimenting and I cannot get it sort.
Go to the top of the page
 
RJD
post Oct 10 2019, 08:53 AM
Post#12


UtterAccess VIP
Posts: 10,097
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI again, but if we have the report in an operating db we can usually solve the issue pretty quickly. Could you post a cut-down db, with relevant objects (tables, query, report ... non-sensitive test data to show the issue), zipped, for us to work with? There is apparently something in the report design we are missing ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Oct 10 2019, 09:51 AM
Post#13


Access Wiki and Forums Moderator
Posts: 76,416
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (brastedhouse)
good morning, how did you do it? I am still experimenting and I cannot get it sort.

Hi. I agree with Joe. It might be better if we could show you how to do it using your own db in case there is something special about it we don't know.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Oct 10 2019, 10:05 AM
Post#14


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


I did it the usual way: click on create a sort and then select the field from the list on the popup.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Oct 10 2019, 10:45 AM
Post#15



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


OK, I will start to set this up later today. I have to go do the work that pays for me to do this. Best, Scott
Go to the top of the page
 
brastedhouse
post Oct 13 2019, 11:29 AM
Post#16



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


Hi, I'm back. Sorry to be so long. Work got in the way.

I am attaching a db of the tables, query and report that I am using to try and get a count sort.

4 tables, one query and a test report. I would like to sort the last (Count) column of the report. I cannot get it to work. Any help would be appreciated.

Best, Scott

Attached File  UASortTemp.zip ( 35.75K )Number of downloads: 4
Go to the top of the page
 
arnelgp
post Oct 13 2019, 12:28 PM
Post#17



Posts: 1,480
Joined: 2-April 09
From: somewhere out there...


see changes in query (last column) and on the report.
Attached File(s)
Attached File  UASortTemp.zip ( 29.56K )Number of downloads: 1
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
brastedhouse
post Oct 13 2019, 12:52 PM
Post#18



Posts: 116
Joined: 16-March 15
From: Chautauqua, NY


Zowee! So, I have 2 questions right away, but first Thank you!

1. Why can you sort by your new count column and not by the original?
2. Is "T" a name you are creating to use in the expression to assign the count function to callMemberFK?
3. Is your count column a subquery?

Best, Scott
This post has been edited by brastedhouse: Oct 13 2019, 12:56 PM
Go to the top of the page
 
projecttoday
post Oct 13 2019, 12:58 PM
Post#19


UtterAccess VIP
Posts: 11,082
Joined: 10-February 04
From: South Charleston, WV


I don't see the count fields in the query on the report.

--------------------
Robert Crouser
Go to the top of the page
 
arnelgp
post Oct 13 2019, 01:02 PM
Post#20



Posts: 1,480
Joined: 2-April 09
From: somewhere out there...


T is an alias to a table.
yes I used sub query.
ms access can't seem to sort on the expression (=count("*")) so I added it
in the query (using sub query).

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:28 AM