Report sort ordering by calculated control? 1

[TomYC]

I have numerous subreports in a larger reports. Right now they are sorting by the item of interest name, but I would like to sort by the count of particular items in the reports query.
Here is the query source for one of these:

SELECT [Complaints-Verbal].VerbalComplaintNo, [Complaints-Verbal].DateReceived, [Primary Department Description].PrimaryDepartment
FROM ([Primary Department] INNER JOIN [Complaints-Verbal] ON [Primary Department].VerbalComplaintNo = [Complaints-Verbal].VerbalComplaintNo) INNER JOIN [Primary Department Description] ON [Primary Department].PrimaryDepartment = [Primary Department Description].PrimaryDepartment
WHERE ((([Complaints-Verbal].DateReceived) Between [forms]![frm Report Generator]![cboStartDate] And [forms]![frm Report Generator]![cboEndDate]) AND (([Primary Department Description].PrimaryDepartment) Like [forms]![frm Report Generator]![cboDepartment] & "*"))
ORDER BY [Complaints-Verbal].DateReceived DESC;

Note that the query sorts by DateReceived.
The subreport is grouping by PrimaryDepartment, but I would like to sort it by a calculated control on the subreport, called CountDepartment, whose source is Count([PrimaryDepartment]).
So far I haven't been able to get this to work in the sorting and grouping dialog box. Why? I would show you the groupheader(0) code if I could, but I haven't changed it yet.

 

[dhookom]

TomYC,
You can't sort by a calculated aggregate in a report. You must build an expression using a single record in the report's record source.

You can typically create a totals/count query and add it the report's record source query. This would allow you to use the count field for sorting.

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Thanks, dhookom--
I thought I had it now: I created a new query based upon my original query, for each subreport. The new query returns a list of items and a count of the items, thus, e.g., PrimaryDepartment, and Count. That two-field query is the record source for my subreport. I grouped on Count, Descending, and every thing looked good until I realized that, in a list of items many of which had a count of one, only one displays; so grouping on Count doesn't work. But grouping on PrimaryDepartment gets me right back to the alphabetical sorting that I am trying to move away from!
What do I need to learn about grouping and sorting now, given that I have Count as a field within the report?

 

[dhookom]

I expected you to keep your original report recordsource query and just add the new query to it and join on the PrimaryDepartment.

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Now I think I'm a bit confused. And even if I proceed as you suggest here, and "add back" the Count field that I've calculated in a separate query, I'm still in the same boat: I can group by PrimaryDepartment and get alpha ordering, or group by Count and lose any PrimaryDepartment records with identical Count(s).
I'm still trying various combinations!

 

[dhookom]

You don't have to lose anything. You can sort first on the count and then the Primarydepartment.

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Group seems to trump sort!
Anytime I group on PrimaryDepartment, I get the entire list, alphabetically, and Sort doesn't affect it. Anytime I group on Count, I lose out on PrimaryDepartment records with the same count.
If there is a way I can show you the code for what I have (the report, as opposed to the query, which I don't think is the problem) I will!

 

[dhookom]

Why not group first on the count and don't use the header/footer and the group on PrimaryDepartment.

I guess I'm not sure how any of this pertains to subreports. Are you attempting to sort the main report or one or more subreports?

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

I guess it was a question of sleeping on it and looking afresh the next day. This morning I moved my fields into the detail section (probably where they belonged!) and then I didn't need any grouping at all, and sorted by count. Simpler was better, just like Einstein said--
Many thanks!