Return Totals for multiple categories from a subreport

  • Thread starter Thread starter lbbeurmann
  • Start date Start date
L

lbbeurmann

I am having a problem getting my report to return categorized totals from a
subreport. Specifically, my report only returns the first record in the
subreport and none of the subsequent records. I will try to explain using a
simplified example...

My subreport is adding fruit totals from a query for different types of
fruit and consists of 2 text boxes "FRUIT TYPE" and "TOTAL" where "TOTAL" is
defined by the Control Source =Sum([PIECES OF FRUIT]) from my first query.
This report works fine by itself and I get what I expect, the results look
like this:

Apples 10
Grapes 13
Oranges 7

I am using this report as a subreport in another report based on a separte
but similar query that also totals fruit, but from a different source. What I
am attempting to do is add the category totals from the subreport to the
category totals for the Main report, but I find that only the first record of
the subreport is added and the rest of the categories are not added together.
After this first total, only the main report values are shown and no
subreport values are added.

This is the Control Source that I am using in my attempt to add the two in
the main report:
=Sum([Pieces of Fruit])+(IIf([Fruit report Sub].Report.HasData And [Fruit
Report Sub].[Report]![FRUIT TYPE]=[FRUIT TYPE],IIf(IsError([Fruit Report
Sub].Report![TOTAL]),0,[Fruit Report Sub].Report![TOTAL]),0))

It works correctly for the first record returned by the subreport. So, for
the sake of simplicity, if both queries returned the same numbers as shown
above and the subreport was sorted by "FRUIT TYPE" Ascending, I get:

Apples 20
Grapes 13
Oranges 7

And if it was sorted by "FRUIT TYPE" Descending, I get:

Oranges 14
Grapes 13
Apples 10

I have the subreport in the Report Footer of the Main report, and the Main
report "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT TYPE" header.

In the subreport, the "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT
TYPE" header.

I have been pulling my hair out for 2 days trying to get this to work. The
frustrating thing is that it partially works, but only for 1 record! I would
appreciate any help as to what I am doing wrong. Thanks in advance.

Regards, L.B.
 
I think you are going about this wrong. Can I assume you want to get the
totals for the different "fruit types" from the main report and subreport
into the main report footer?

So, if the main report has (I don't know which section):
Oranges 14
Grapes 13
Apples 10
and the subreport had detail records of:
Oranges 10
Grapes 3
Apples 5
You want to get this into the Report Footer of your main report:
Oranges 24
Grapes 16
Apples 15

If this is correct, I would create a query that returned all of these values
and put them into a subreport to place in the main report footer.
--
Duane Hookom
Microsoft Access MVP


lbbeurmann said:
I am having a problem getting my report to return categorized totals from a
subreport. Specifically, my report only returns the first record in the
subreport and none of the subsequent records. I will try to explain using a
simplified example...

My subreport is adding fruit totals from a query for different types of
fruit and consists of 2 text boxes "FRUIT TYPE" and "TOTAL" where "TOTAL" is
defined by the Control Source =Sum([PIECES OF FRUIT]) from my first query.
This report works fine by itself and I get what I expect, the results look
like this:

Apples 10
Grapes 13
Oranges 7

I am using this report as a subreport in another report based on a separte
but similar query that also totals fruit, but from a different source. What I
am attempting to do is add the category totals from the subreport to the
category totals for the Main report, but I find that only the first record of
the subreport is added and the rest of the categories are not added together.
After this first total, only the main report values are shown and no
subreport values are added.

This is the Control Source that I am using in my attempt to add the two in
the main report:
=Sum([Pieces of Fruit])+(IIf([Fruit report Sub].Report.HasData And [Fruit
Report Sub].[Report]![FRUIT TYPE]=[FRUIT TYPE],IIf(IsError([Fruit Report
Sub].Report![TOTAL]),0,[Fruit Report Sub].Report![TOTAL]),0))

It works correctly for the first record returned by the subreport. So, for
the sake of simplicity, if both queries returned the same numbers as shown
above and the subreport was sorted by "FRUIT TYPE" Ascending, I get:

Apples 20
Grapes 13
Oranges 7

And if it was sorted by "FRUIT TYPE" Descending, I get:

Oranges 14
Grapes 13
Apples 10

I have the subreport in the Report Footer of the Main report, and the Main
report "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT TYPE" header.

In the subreport, the "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT
TYPE" header.

I have been pulling my hair out for 2 days trying to get this to work. The
frustrating thing is that it partially works, but only for 1 record! I would
appreciate any help as to what I am doing wrong. Thanks in advance.

Regards, L.B.
Click to expand...
 
Thanks for the help. I tried what you suggested, and the result was the
same. Only the first record returned in the subreport was added to the main
report's total. Otherwise, nothing was added to the other types.

Could it have something to do with the expression that I am using for my
control source? If I do not use and IIf to attempt to break out the differnt
types, it just adds the first record returned in the subreport to all of the
types, which is incorrect. Is there another expression that I can use to
break out the differnt types and add them appropriately?

Duane Hookom said:
I think you are going about this wrong. Can I assume you want to get the
totals for the different "fruit types" from the main report and subreport
into the main report footer?

So, if the main report has (I don't know which section):
Oranges 14
Grapes 13
Apples 10
and the subreport had detail records of:
Oranges 10
Grapes 3
Apples 5
You want to get this into the Report Footer of your main report:
Oranges 24
Grapes 16
Apples 15

If this is correct, I would create a query that returned all of these values
and put them into a subreport to place in the main report footer.
--
Duane Hookom
Microsoft Access MVP


lbbeurmann said:
I am having a problem getting my report to return categorized totals from a
subreport. Specifically, my report only returns the first record in the
subreport and none of the subsequent records. I will try to explain using a
simplified example...

My subreport is adding fruit totals from a query for different types of
fruit and consists of 2 text boxes "FRUIT TYPE" and "TOTAL" where "TOTAL" is
defined by the Control Source =Sum([PIECES OF FRUIT]) from my first query.
This report works fine by itself and I get what I expect, the results look
like this:

Apples 10
Grapes 13
Oranges 7

I am using this report as a subreport in another report based on a separte
but similar query that also totals fruit, but from a different source. What I
am attempting to do is add the category totals from the subreport to the
category totals for the Main report, but I find that only the first record of
the subreport is added and the rest of the categories are not added together.
After this first total, only the main report values are shown and no
subreport values are added.

This is the Control Source that I am using in my attempt to add the two in
the main report:
=Sum([Pieces of Fruit])+(IIf([Fruit report Sub].Report.HasData And [Fruit
Report Sub].[Report]![FRUIT TYPE]=[FRUIT TYPE],IIf(IsError([Fruit Report
Sub].Report![TOTAL]),0,[Fruit Report Sub].Report![TOTAL]),0))

It works correctly for the first record returned by the subreport. So, for
the sake of simplicity, if both queries returned the same numbers as shown
above and the subreport was sorted by "FRUIT TYPE" Ascending, I get:

Apples 20
Grapes 13
Oranges 7

And if it was sorted by "FRUIT TYPE" Descending, I get:

Oranges 14
Grapes 13
Apples 10

I have the subreport in the Report Footer of the Main report, and the Main
report "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT TYPE" header.

In the subreport, the "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT
TYPE" header.

I have been pulling my hair out for 2 days trying to get this to work. The
frustrating thing is that it partially works, but only for 1 record! I would
appreciate any help as to what I am doing wrong. Thanks in advance.

Regards, L.B.
Click to expand...
Click to expand...
 
One other issue I thought of that might be causing problems: As I stated
before, the main report is based on a separate query than the subreport. The
main report displays several more totals of "pieces of fruit" from its query
for each "fruit type" based on other higher order categories. The subreport,
however, only totals "pieces of fruit" by type for one of the categories, but
from a separate source.

Currently the subreport and the main report are not linked by any Child or
Master fields. Should they be? "Fruit type" is the only way I could see to
link them, but I have tried it and it does not solve the problem. In fact,
it does not return even one correct total, just zeroes. If I do link them,
will I need to change my control source for the text box in the main report
to correctly pull the values from the subreport?

Thanks in advance.


Duane Hookom said:
I think you are going about this wrong. Can I assume you want to get the
totals for the different "fruit types" from the main report and subreport
into the main report footer?

So, if the main report has (I don't know which section):
Oranges 14
Grapes 13
Apples 10
and the subreport had detail records of:
Oranges 10
Grapes 3
Apples 5
You want to get this into the Report Footer of your main report:
Oranges 24
Grapes 16
Apples 15

If this is correct, I would create a query that returned all of these values
and put them into a subreport to place in the main report footer.
--
Duane Hookom
Microsoft Access MVP


lbbeurmann said:
I am having a problem getting my report to return categorized totals from a
subreport. Specifically, my report only returns the first record in the
subreport and none of the subsequent records. I will try to explain using a
simplified example...

My subreport is adding fruit totals from a query for different types of
fruit and consists of 2 text boxes "FRUIT TYPE" and "TOTAL" where "TOTAL" is
defined by the Control Source =Sum([PIECES OF FRUIT]) from my first query.
This report works fine by itself and I get what I expect, the results look
like this:

Apples 10
Grapes 13
Oranges 7

I am using this report as a subreport in another report based on a separte
but similar query that also totals fruit, but from a different source. What I
am attempting to do is add the category totals from the subreport to the
category totals for the Main report, but I find that only the first record of
the subreport is added and the rest of the categories are not added together.
After this first total, only the main report values are shown and no
subreport values are added.

This is the Control Source that I am using in my attempt to add the two in
the main report:
=Sum([Pieces of Fruit])+(IIf([Fruit report Sub].Report.HasData And [Fruit
Report Sub].[Report]![FRUIT TYPE]=[FRUIT TYPE],IIf(IsError([Fruit Report
Sub].Report![TOTAL]),0,[Fruit Report Sub].Report![TOTAL]),0))

It works correctly for the first record returned by the subreport. So, for
the sake of simplicity, if both queries returned the same numbers as shown
above and the subreport was sorted by "FRUIT TYPE" Ascending, I get:

Apples 20
Grapes 13
Oranges 7

And if it was sorted by "FRUIT TYPE" Descending, I get:

Oranges 14
Grapes 13
Apples 10

I have the subreport in the Report Footer of the Main report, and the Main
report "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT TYPE" header.

In the subreport, the "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT
TYPE" header.

I have been pulling my hair out for 2 days trying to get this to work. The
frustrating thing is that it partially works, but only for 1 record! I would
appreciate any help as to what I am doing wrong. Thanks in advance.

Regards, L.B.
Click to expand...
Click to expand...
 
As I stated earlier, you are trying to implement the wrong solution. Did you
try to create a query that returns all of the results you need to display?
That would be the first step. If you can't figure this out, tell us about all
of the tables and field that contain the values you want to aggregate and
display in the report.

--
Duane Hookom
Microsoft Access MVP


lbbeurmann said:
One other issue I thought of that might be causing problems: As I stated
before, the main report is based on a separate query than the subreport. The
main report displays several more totals of "pieces of fruit" from its query
for each "fruit type" based on other higher order categories. The subreport,
however, only totals "pieces of fruit" by type for one of the categories, but
from a separate source.

Currently the subreport and the main report are not linked by any Child or
Master fields. Should they be? "Fruit type" is the only way I could see to
link them, but I have tried it and it does not solve the problem. In fact,
it does not return even one correct total, just zeroes. If I do link them,
will I need to change my control source for the text box in the main report
to correctly pull the values from the subreport?

Thanks in advance.


Duane Hookom said:
I think you are going about this wrong. Can I assume you want to get the
totals for the different "fruit types" from the main report and subreport
into the main report footer?

So, if the main report has (I don't know which section):
Oranges 14
Grapes 13
Apples 10
and the subreport had detail records of:
Oranges 10
Grapes 3
Apples 5
You want to get this into the Report Footer of your main report:
Oranges 24
Grapes 16
Apples 15

If this is correct, I would create a query that returned all of these values
and put them into a subreport to place in the main report footer.
--
Duane Hookom
Microsoft Access MVP


lbbeurmann said:
I am having a problem getting my report to return categorized totals from a
subreport. Specifically, my report only returns the first record in the
subreport and none of the subsequent records. I will try to explain using a
simplified example...

My subreport is adding fruit totals from a query for different types of
fruit and consists of 2 text boxes "FRUIT TYPE" and "TOTAL" where "TOTAL" is
defined by the Control Source =Sum([PIECES OF FRUIT]) from my first query.
This report works fine by itself and I get what I expect, the results look
like this:

Apples 10
Grapes 13
Oranges 7

I am using this report as a subreport in another report based on a separte
but similar query that also totals fruit, but from a different source. What I
am attempting to do is add the category totals from the subreport to the
category totals for the Main report, but I find that only the first record of
the subreport is added and the rest of the categories are not added together.
After this first total, only the main report values are shown and no
subreport values are added.

This is the Control Source that I am using in my attempt to add the two in
the main report:
=Sum([Pieces of Fruit])+(IIf([Fruit report Sub].Report.HasData And [Fruit
Report Sub].[Report]![FRUIT TYPE]=[FRUIT TYPE],IIf(IsError([Fruit Report
Sub].Report![TOTAL]),0,[Fruit Report Sub].Report![TOTAL]),0))

It works correctly for the first record returned by the subreport. So, for
the sake of simplicity, if both queries returned the same numbers as shown
above and the subreport was sorted by "FRUIT TYPE" Ascending, I get:

Apples 20
Grapes 13
Oranges 7

And if it was sorted by "FRUIT TYPE" Descending, I get:

Oranges 14
Grapes 13
Apples 10

I have the subreport in the Report Footer of the Main report, and the Main
report "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT TYPE" header.

In the subreport, the "TOTAL" and "FRUIT TYPE" text boxes are under a "FRUIT
TYPE" header.

I have been pulling my hair out for 2 days trying to get this to work. The
frustrating thing is that it partially works, but only for 1 record! I would
appreciate any help as to what I am doing wrong. Thanks in advance.

Regards, L.B.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top