Bizarre break-up of groups in a report

[PDC]

I have a problem with an Access report that I cannot solve. Can anyone help?
I am using Access 2003, and have been using and programming Access on and
off for many years.

The problem is with a fairly complex report, so I have created a stripped
down version that still exhibits the same behaviour, and it is this I
describe below.

There are 6 tables. I have joined 3 of the tables together in a query (Q1),
and the three other tables together in a second query (Q2). I have then
joined these two queries together into a third query Q3, which is the control
source for the report. This is a way of getting all the hierarchic
information about the detail records into one place.

The report has two sections only, and no special properties or events. The
detail section contains 10 text box controls, each of whose control source is
a field in Q3. The other section is a header section for the field X, on
which the report is grouped, and it contains 5 text box controls, all of
which depend on X. There is another field (Y – a Boolean) on which the
report is sorted, and Y depends on X.

When I run this report with the sort on Y after the sort/grouping of X then
I get the results I expect – group headers followed by detail records. If I
put Y above X (so the sort on Y comes before that on X, which is what I want
to do) then all the groups break up and the group header is printed above
each single detail record. It is this group break-up behaviour that I am
trying to cure. Bizarrely, if I then delete any single control from either
of the two sections the aberrant behaviour disappears.

Am I doing something wrong? Have I hit some limit in Access? Before I try
other ways to get round the problem, can you suggest any simple ways?

Thanks.

 

[Allen Browne]

PDC, the really odd thing here is that you can pick *any* control from
either section and delete it to solve the problem.

That suggests Access is very confused about the names of the objects. The
source of the confusion could be:
a) Name AutoCorrect mis-identifying fields;
b) an ambiguity (e.g. you used a reserved name or property);
c) a corruption in the database (such as a bad index.)
d) a data type being misidentified;
e) a reference problem;
f) a JET bug.

Suggested sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If the problem persists:

7. Take a look at:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
Search the list on the webpage for each of the fields in the query, and also
for each of the controls on the report. (A control may or may not have the
same name as the Field it is bound to.)

8. Still broken? It could be a bug in JET (the Access query engine.)
What data type is X? If Decimal, see:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

9. Does the query have outer joins?
JET has several examples of bugs that fail in various ways under these
conditions. Here's a simple example:
Outer join queries fail on Yes/No fields
at:
http://allenbrowne.com/bug-14.html

 

[JoyAA]

I think I know what the problem is that you are seeing, I'm do not have a
solution. You problem sounds remarkably similar to the problem I had and
posted on 11/7 (ACC: How to Sort Concatenated List of Items from a Many-Side
Table).
I didn't get any reply so I'm thinking that it's a tough nut to crack.

The issue of ungrouping seems to be related to the use of groups to do
"sorting" only. Unfortunately, there doesn't seem to be a way around this as
ORDER BY in the query or Report properties won't help once you introduce any
grouping.

If you do find a workaround or get solution, please post.

 

[Gary Walter]

PMFBI

No doubt Allen has probably nailed it as always,
but if the problem still remains....

may I suggest trying....

before you open the report,
empty a "report table"
and then append with data from Q3....

 

[PDC]

Allen, Sorry not have been back earlier, but I have been busy with other
things. I have tried half of what you suggest and so far no improvement.
When I complete the list I will let you know the outcome.