Sorting and Grouping Trouble in Access 2000 Report

[candi]

I am a new user of Access so please be patient with my ignorance, but
I'm having trouble getting a report to sort results the way I'd like.
My report is based on a query that determines the sums the qty of
unique product #s. The query results look like something like this:
Product ID# Sum of Records
123456 10
234567 8
234568 8
345678 6
456789 5

I'd like the report to group on qty first and then by a unique product
#. When the Sorting and Grouping Properties are set at: Product ID#
Ascending and SumofRecords Descending, the report sorts the Product
ID#s into five groups, which is what I'd like, however, I'd also like
the report to be sorted in descending order by SumofRecords. Since
two of my sums are the same (8 and 8), if I switch the sorting and
grouping order to look at SumofRecords first, the 2nd and 3rd Product
ID#s are grouped together giving me only four groups.

There is probably a simple solution but I've been stuck on this for
days now. Any advice anyone could give would be greatly appreciated.
Thanks, Candi

 

[Larry Linson]

I am just unable to reproduce the outcome that you describe. Perhaps it is
the order in which you are displaying the information, as well as the
sorting and grouping.

I created a report based on a totals query against the sample Northwind
Traders Order Detail. I included Product ID, twice, in a Select Query, then
on the menu, chose View | Totals. Under the first occurrence of Product ID,
I set the Total: to Group By, and under the second occurence of product
ID, I set the Total to Count. If I understand correctly, that gives me
report results similar to the ones you describe.

Then I created a Report using AutoReport (which created a columnar report,
with no sorting/grouping). I moved the Count of Product ID field to the
left, the Product ID itself on the right, on the same line. I moved the
associated labels to the page header.

I open Sorting and Grouping, and realized that I needed no grouping in the
report -- thus I chose to sort on Count of Product ID, and then on Product
ID itself. This gave me the output I expected, a Detail line for each
record, ordered by Count and within that, by Product ID. Note that I think I
got the results you want without using Grouping at all; if you want the
Count to show only the first time for a particular count, you can set the
HideDuplicates property in that Control's Format tab of its Property Sheet.

Then, thinking I might have missed something, I went back, Grouped on each
of the two fields, and moved the text boxes to the appropriate Group
Header... and eliminated the now-empty Detail line. That also seemed to
approximate what you said you wanted.

If I have misunderstood, please clarify here, and either I or someone else
can possibly assist.

Larry Linson
Microsoft Access MVP

Because the purpose of grouping is to put similar "things" together, and it
is easier to read if the highest grouping level is to the left (assuming a
language that reads left to right), I placed shows the Count (sum of qty?)
first, then the P