Sort report by sum of counts

  • Thread starter Thread starter Whitney
  • Start date Start date
W

Whitney

I have a report that takes all issues from a certain date range and counts
them. The report shows the sums only. I'm trying to sort by the sum in
descending order, but it only sorts the detail, not the sum. Issues is the
header, so it sorts first by alphabetically and then by the count for each
day.

I created another query that sums the orignal query data and created a
report off the new query, but it still will only allow my to sort by the
issue if issue is the header, instead of by sum.

What am I missing? How can I sort the report by the overall sum of each
issue for the date range, instead of by alphabetically.
 
You are using Count and Sum as if they are the same thing.
You can make a header for Issue and have it sort on another field by adding
it but not display in the header.
 
I'm a bit confused by your record source and what value you would like to use
for sorting.

- Sorting in reports is reliably set using the Sorting
and Grouping dialog in design

- You can only sort a report based on a field or
expression from a single record in the report's
record source

- You can't sort a report based on an aggregate
(sum, count, ...) value calculated in a group
header or footer

Possible Solution
- You can generally create a totals query that would
calculate the aggregate value by the group field.
Don't format this value as you want it to be a
numeric value (no currency or whatever)

- Add this new totals query to your report's record source
and join the "group" fields. This should not change the
number of records returned by your record source.

- Add the aggregate field to the grid so it becomes
available for sorting and grouping in your report
 
Sorry, here is a more specific explanation...

Table: tbl_Tracker
Issue Date
Orders 12/2/2009
Invites 12/3/2009
Orders 12/4/2009
Misc. 12/5/2009
Orders 12/6/2009

Query: qry_Issue_Count
Issue Issue Date
Group by Count Code to select start and end date from a form
Is Not Null

Report: rpt_Issue_Count
Page Header
Issue Count
Issue Header (displayed on report)
Issue =sum([countofissue])
Detail (not visible on report)
Date CountOfIssue
Page Footer
code for date and number of pages.

So I'm trying to sort the results of the sum of the counts in descending
order on the report.

The only option I get is to sort by CountofIssue or by Issue (name). I would
like to sort by sum of [countofissue].
 
If the query groups by Issue and counts, how is that different from the
report grouping by query? Isn't the sum of countofissue the same as
countofissue?

Did you understand my suggestion to calculate whatever value would be in the
issue header in a separate totals query?

--
Duane Hookom
Microsoft Access MVP


Whitney said:
Sorry, here is a more specific explanation...

Table: tbl_Tracker
Issue Date
Orders 12/2/2009
Invites 12/3/2009
Orders 12/4/2009
Misc. 12/5/2009
Orders 12/6/2009

Query: qry_Issue_Count
Issue Issue Date
Group by Count Code to select start and end date from a form
Is Not Null

Report: rpt_Issue_Count
Page Header
Issue Count
Issue Header (displayed on report)
Issue =sum([countofissue])
Detail (not visible on report)
Date CountOfIssue
Page Footer
code for date and number of pages.

So I'm trying to sort the results of the sum of the counts in descending
order on the report.

The only option I get is to sort by CountofIssue or by Issue (name). I would
like to sort by sum of [countofissue].


Duane Hookom said:
I'm a bit confused by your record source and what value you would like to use
for sorting.

- Sorting in reports is reliably set using the Sorting
and Grouping dialog in design

- You can only sort a report based on a field or
expression from a single record in the report's
record source

- You can't sort a report based on an aggregate
(sum, count, ...) value calculated in a group
header or footer

Possible Solution
- You can generally create a totals query that would
calculate the aggregate value by the group field.
Don't format this value as you want it to be a
numeric value (no currency or whatever)

- Add this new totals query to your report's record source
and join the "group" fields. This should not change the
number of records returned by your record source.

- Add the aggregate field to the grid so it becomes
available for sorting and grouping in your report
Click to expand...
Click to expand...
 
Back
Top