Report with multiple subreports

[Stephen @ ZennHAUS]

Hi Guys and Gals

I have a problem creating complex reports. In this case, I have a report
which is grouped on 2 fields (Region and Material) and needs to be grouped
on a 3rd (MaterialClass). However, when I group using a 3rd field, every
time I run the report, Access crashes and wants to send an error report off
to Microsoft.

So ... I created a new version of the report with two subreports. Each of
the two subreports is based on one of the two values that MaterialClass can
have [Product1 and Waste] (it can actually have more than that but for this
scenario we only ever report on two of the values). So in the final
report, I am essentially able to "group" by the MaterialClass value with the
two subreports.

Now the fun part. I have to create a grand total section that totals the
two subreports. I have no idea how to do this, so what I tried was to
create a 3rd subreport which is based on all the data for the first two
subreports. My 3rd subreport works just fine on its own. BUT when I add
the subreport to the main report and try to save or run the report, I get
the same issue Access crashing.

Another scenario is where I have to divide the data into Day and Night
shifts. So now essentially I am grouping by 4 fields. Needless to say it's
all going pear shaped at this point.

Cheers

Stephen @ ZennHAUS

 

[Larry Linson]

That's not enough information for me to make a worthwhile suggestion, but I
have, many times in several versions of Access, successfully created reports
grouped on more than 2 fields, and, if memory serves, I've done reports with
more than 2 subreports at the same level.

If you can clarify the version of Access, the OS you are using, and
additional details about your data, perhaps someone can make a suggestion so
you don't have to try a workaround based on subreports, or help you with the
problems you are having with subreports.

Larry Linson
Microsoft Office Access MVP

 

[Stephen @ ZennHAUS]

I am running WindowsXP SP3.3311 with Access 2003 SP3 (11.8166.8221)

The report is based on a query and has 20 fields which are combination of
fields drawn from 10 related tables and a number of 1st and 2nd level
calculated fields.

The query draws date criteria from a form where the user specifies the date
range they wish to report on.

I'm not sure what else I should be telling you.

That's not enough information for me to make a worthwhile suggestion, but
I have, many times in several versions of Access, successfully created
reports grouped on more than 2 fields, and, if memory serves, I've done
reports with more than 2 subreports at the same level.

If you can clarify the version of Access, the OS you are using, and
additional details about your data, perhaps someone can make a suggestion
so you don't have to try a workaround based on subreports, or help you
with the problems you are having with subreports.

Larry Linson
Microsoft Office Access MVP

Hi Guys and Gals

I have a problem creating complex reports. In this case, I have a report
which is grouped on 2 fields (Region and Material) and needs to be
grouped on a 3rd (MaterialClass). However, when I group using a 3rd
field, every time I run the report, Access crashes and wants to send an
error report off to Microsoft.

So ... I created a new version of the report with two subreports. Each
of the two subreports is based on one of the two values that
MaterialClass can have [Product1 and Waste] (it can actually have more
than that but for this scenario we only ever report on two of the
values). So in the final report, I am essentially able to "group" by
the MaterialClass value with the two subreports.

Now the fun part. I have to create a grand total section that totals the
two subreports. I have no idea how to do this, so what I tried was to
create a 3rd subreport which is based on all the data for the first two
subreports. My 3rd subreport works just fine on its own. BUT when I add
the subreport to the main report and try to save or run the report, I get
the same issue Access crashing.

Another scenario is where I have to divide the data into Day and Night
shifts. So now essentially I am grouping by 4 fields. Needless to say
it's all going pear shaped at this point.

Cheers

Stephen @ ZennHAUS

 

[Amy E. Baggott]

I had a problem with Access crashing every time I tried to get into the
Expression Builder from the shortcut menu, so I can understand what you're
going through. I ended up having to do a complete clear out and reinstall of
Access on my computer, and that solved the problem.

As to totalling your subreports, you can actually do one of two things: you
can create a field in your main report that is
Sum(field1) + Sum(field2),
but what I usually do with mine is create a non-visible field in the report
footer of each subreport with a calculation of

Nz(Sum([field]), 0)

which returns 0 if there are no records in the report, then I create a field
in the main report that references each of these fields

=Reports!MyReport!Report!SubReport1![TotalField] +
Reports!MyReport!Report!SubReport2![TotalField]

The easiest way to make sure the syntax is correct on this field is to use
the Expression Builder.

I have several reports that have either multiple grouping levels or multiple
subreports, and mine work fine. I have long suspected, however, that every
Microsoft program comes with a secret, randomly-accessed subroutine called
"DriveTheUserStraightUpTheNearestWall".
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

I am running WindowsXP SP3.3311 with Access 2003 SP3 (11.8166.8221)

The report is based on a query and has 20 fields which are combination of
fields drawn from 10 related tables and a number of 1st and 2nd level
calculated fields.

The query draws date criteria from a form where the user specifies the date
range they wish to report on.

I'm not sure what else I should be telling you.

That's not enough information for me to make a worthwhile suggestion, but
I have, many times in several versions of Access, successfully created
reports grouped on more than 2 fields, and, if memory serves, I've done
reports with more than 2 subreports at the same level.

If you can clarify the version of Access, the OS you are using, and
additional details about your data, perhaps someone can make a suggestion
so you don't have to try a workaround based on subreports, or help you
with the problems you are having with subreports.

Larry Linson
Microsoft Office Access MVP

Hi Guys and Gals

I have a problem creating complex reports. In this case, I have a report
which is grouped on 2 fields (Region and Material) and needs to be
grouped on a 3rd (MaterialClass). However, when I group using a 3rd
field, every time I run the report, Access crashes and wants to send an
error report off to Microsoft.

So ... I created a new version of the report with two subreports. Each
of the two subreports is based on one of the two values that
MaterialClass can have [Product1 and Waste] (it can actually have more
than that but for this scenario we only ever report on two of the
values). So in the final report, I am essentially able to "group" by
the MaterialClass value with the two subreports.

Now the fun part. I have to create a grand total section that totals the
two subreports. I have no idea how to do this, so what I tried was to
create a 3rd subreport which is based on all the data for the first two
subreports. My 3rd subreport works just fine on its own. BUT when I add
the subreport to the main report and try to save or run the report, I get
the same issue Access crashing.

Another scenario is where I have to divide the data into Day and Night
shifts. So now essentially I am grouping by 4 fields. Needless to say
it's all going pear shaped at this point.

Cheers

Stephen @ ZennHAUS