advanced help sought with Report "Sorting and Grouping"

[larryww]

Do you guys use the "Sorting and Grouping" box for Access report design? I'm wondering how this runs up against what you put in the query design view. I'm hoping that I can avoid exhaustive testing if someone else (you!) is already strong on it. Can you confirm or refute the "THEORY" below?

Note that this isn't worrying with pure SQL; just the ACCESS interfaces for queries and reports.

Let me postulate an answer, and if anyone can augment or correct this, great. I'm not saying that this is correct, but it's my inclination currently:

*********THEORY*********THEORY*********
1. Call the Report sorting and grouping box "S&G". S&G sorts (i.e., its right column) rank supreme. The first shown S&G line is done first (even if not visible). Note: every S&G item is _required_ to have asc. or desc. sort order.

2. Remaining S&G lines are processed, from top to bottom. Thus, a repeated one serves no purpose at all. (I'm starting to get quite bold here - please load your arrows, because I want the correct answer, more than to be right!).

3. *Within* each of those report groups, the query interface's explicit sorts are evaluated, left to right. I.e. the leftmost is done first, and the next one is secondary to it, etc.

4. Query Group Bys are evaluated after the sorts, each implying Ascending.

5. Whatever remains can not be guaranteed to consistently follow any order, even if the table source was sorted.

Orrrrr .... shall we get funky and say 3. The query's sorts & groups are simply ignored and discarded by the report. (BION, this is my vote)
*********THEORY*********THEORY*********

Your time and help is much appreciated.

 

[LittleSmudge]

Theory clause 1 & " - Yes I'll sign up to them.
Theory clause 3 - If by "left to right in the query interface" you mean from left to right in the QBE grid as it is shown in design view then I'll sign up to that too.
Theory 4 - I must admit I've never needed to combine 1-3 with a 4 before but it is empirically what I'd expect.

Theory 5 - Oh. Now that one is NOT what I would expect to happen. I would certainly expect the underlying table's index properties to take over at this point. However, a table does retain the last sort that was done to it before it was saved. So the resulting output could be based on the manual sort order. Either way it is predictable ( It's just that making the prediction becomes too diffecult to cope with )

G LS

 

[larryww]

Thanks for the first cognitive reply I've received on this question Unfortunately, I did find a problem on item 5; as you'll see below, the result leans more to "unpredictable" than to respect of indexing.

Here's an exact sequence I ran in A2000:
created Table1 with
Mynum text indexed no dupes
Myletter text not indexed

wizarded report, with grouping on Myletter (ascending)

Entered (in this sequence, for what it's worth):
1 Z
2 Y
3 X
4 Y
5 Z

The report gave
X
3
Y
4
2
Z
5
1

So the table indexing did not hold, in this test.

 

[LittleSmudge]

It is in reverse order ( Because of the backwards way Sorting is done ) of the sequence that the records were added - regardless of the Indexing.

If you add a line 10,11 and 12 then add a line 7, 8 & 9 All with "Z" chars you'll get
9
8
7
12
11
10
5
1

Whilst I'm proved ( partially ) wrong the line
"Either way it is predictable ( It's just that making the prediction becomes too diffecult to cope with )"
still stands !

G LS