Sort but don't group

[paulkaye]

I'm trying to sort the Details section of a report by one field so
that items appear in the report in a particular order. I do not want
to group items at all. How can I achieve this? The Sorting & Grouping
dialogue seems to insist I group too! The original problem is posted
here:
http://groups.google.com/group/microsoft.public.access/browse_thread/thread/cf649d6c8d865cd6

 

[Dirk Goldgar]

In

I'm trying to sort the Details section of a report by one field so
that items appear in the report in a particular order. I do not want
to group items at all. How can I achieve this? The Sorting & Grouping
dialogue seems to insist I group too! The original problem is posted
here:
http://groups.google.com/group/microsoft.public.access/browse_thread/thread/cf649d6c8d865cd6

Use the Sorting & Grouping dialog, but specify No for the Group Header
and Group Footer properties.

 

[tina]

in the Sorting & Grouping window in report Design view, choose a field you
want to sort on, and set the Sort Order to Ascending or Descending. just do
NOT change any of the Group Properties at the bottom, which (AFAIK) default
to "not grouped" settings in Access versions A97 - A2003.

hth

 

[paulkaye]

Dirk: Specifying no seems to simply delete the group header from the
report!

Tina: I'm using Access 2003 and have not seen any option for "not
grouped"!

Any more suggestions?

 

[Dirk Goldgar]

In

Dirk: Specifying no seems to simply delete the group header from the
report!

Isn't that what you wanted? You said you wanted the data sorted but not
grouped. Not grouped, in a report, means no group header or footer.
Specifying a "group" in the Sorting and Grouping dialog, with no group
header or footer, will sort the records at that grouping level. If you
didn't observe this in your report, then maybe you had other groups
overriding that one.

 

[paulkaye]

In


Isn't that what you wanted? You said you wanted the data sorted but not
grouped. Not grouped, in a report, means no group header or footer.
Specifying a "group" in the Sorting and Grouping dialog, with no group
header or footer, will sort the records at that grouping level. If you
didn't observe this in your report, then maybe you had other groups
overriding that one.

I see what you mean but I don't really understand Access that well.
The button I have set up in my form prints the report with only
records relevant to that form. Perhaps this is a form of grouping?
A clue may be that before I altered any sorting/grouping settings the
title of the Header section in the report's design view was simply
"Header". Once I tried to sort the order of the records in the Details
section, the title of the Header section became "Item Number
Header" (the field being sorted on was Item Number). Does this explain
anything to you?

 

[John W. Vinson]

I see what you mean but I don't really understand Access that well.
The button I have set up in my form prints the report with only
records relevant to that form. Perhaps this is a form of grouping?
A clue may be that before I altered any sorting/grouping settings the
title of the Header section in the report's design view was simply
"Header". Once I tried to sort the order of the records in the Details
section, the title of the Header section became "Item Number
Header" (the field being sorted on was Item Number). Does this explain
anything to you?

Possibly you're making the very common jargon error: "Sorting" means "to take
a set of records and put it in order". Many people use the term in the sense
of "selecting a subset of the records". If you want the report to display only
one record or a subset of records, this is not "sorting", and it doesn't
involve the Sorting and Grouping dialog at all; instead, you would *filter*
the records, or base the report on a query with criteria to select only the
records on the form. Is that what you have in mind?

John W. Vinson [MVP]

 

[tina]

i didn't say there was a "not grouped" OPTION, hon. i said the default
settings are already set so that there is no grouping. you have to actively
change a setting to apply grouping to the data.

hth

Dirk: Specifying no seems to simply delete the group header from the
report!

Tina: I'm using Access 2003 and have not seen any option for "not
grouped"!

Any more suggestions?

 

[tina]

comments inline.

I see what you mean but I don't really understand Access that well.
The button I have set up in my form prints the report with only
records relevant to that form. Perhaps this is a form of grouping?

no, that is filtering the records so that only the relevant one(s) are
included in the report's recordset.

A clue may be that before I altered any sorting/grouping settings the
title of the Header section in the report's design view was simply
"Header".

i doubt that. there is a Report Header section available in every report,
which can be turned on or off. there is a Page Header section available in
every report, which can also be turned on and off. you can create group
header sections in the Sorting and Grouping box, by selecting a fieldname in
the Field/Expression column in the top half of the box, and *then going to
the Group Properties section in the bottom half of the box and setting the
Group Header property to Yes.

Once I tried to sort the order of the records in the Details
section, the title of the Header section became "Item Number
Header" (the field being sorted on was Item Number). Does this explain
anything to you?

see my comments above. go back into report Design view and open the Sorting
and Grouping box. click on the Item Number fieldname in the Field/Expression
column to select it, and then look at the Group Properties section at the
bottom of the box. i expect that you'll see you set the Group Header section
to Yes. change it back to No.

hth

 

[paulkaye]

comments inline.







no, that is filtering the records so that only the relevant one(s) are
included in the report's recordset.


i doubt that. there is a Report Header section available in every report,
which can be turned on or off. there is a Page Header section available in
every report, which can also be turned on and off. you can create group
header sections in the Sorting and Grouping box, by selecting a fieldname in
the Field/Expression column in the top half of the box, and *then going to
the Group Properties section in the bottom half of the box and setting the
Group Header property to Yes.


see my comments above. go back into report Design view and open the Sorting
and Grouping box. click on the Item Number fieldname in the Field/Expression
column to select it, and then look at the Group Properties section at the
bottom of the box. i expect that you'll see you set the Group Header section
to Yes. change it back to No.

hth

John: I specifically want to sort, not to filter. My trouble is that
when I try to sort, I am forced to group!
Tina: You were right about the titles - I must have got rid of some
grouping that was there before without realising. There IS a group -
it is "Invoice number" - this is how only the items for a particular
invoice are displayed.

I seem to be having difficulty explaining myself so I have made a
screenshot which should explain things better ( http://www.conceptwheel.com/screenshot.jpg
). I would be most grateful if you could have a look and try to
understand what I am trying to do. I hope the image will explain
things better than I can!

Many thanks again for all your time,

Paul

 

[Dirk Goldgar]

In

I seem to be having difficulty explaining myself so I have made a
screenshot which should explain things better (
http://www.conceptwheel.com/screenshot.jpg ). I would be most
grateful if you could have a look and try to
understand what I am trying to do. I hope the image will explain
things better than I can!

So what field is it that you want to sort on? Underneath the "Invoice
Number" group in your Sorting & Grouping dialog, your screenshot shows a
row with the Field/Expression column blank, and "Descending" specified
for Sort Order. I didn't know Access would let you do that, but it
makes no sense. On that row, in the Field/Expression column, you have
to specify what field or expression you want to sort or group on. For
example, if you want to sort on Item Description, you have to put "Item
Description" in the Field/Expression column.

 

[tina]

okay, i looked. so you want to sort the Detail records; just choose which
field *in the Detail section* that you want to sort by, and then select that
fieldname from the droplist in the Sorting and Grouping box *on the line
directly below the line that shows "Invoice number" in the screenshot*. and
DO NOT change any of the default values in the Group Properties section in
the bottom half of the box, for that line. it's really that simple.

hth