Calculating page/report sums using calculated data from subquery

[rickr]

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

I am confused to no end. Any help is appreciated.

 

[rickr]

The title has been corrected. I apologize for any initial confusion

 

[Marshall Barton]

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

 

[rickr]

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

 

[rickr]

I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

 

[rickr]

Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').

CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub

END CODE

Any help is appreciated.

 

[rickr]

Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').
CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub


Any help is appreciated.

I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

rickr wrote:

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...


If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

 

[Marshall Barton]

Sorry, I was so focused on the subreport reference that I
missed the thee fact that you were using Sum to try to total
the subreport values. That won't work because the aggregate
functions (Count, Sum, etc) can only operate on revord
source fields, they are unaware of controls in the report.

Instead you should use a running sum text box (named
txtRunTotal) in the same section as the subreport. The text
box's expression would be like:
=IIf([Product - Pegged].Report.HasData, [Product -
Pegged].Report![PegStdLabor], 0)

Then the report footer textbox can use the expression:
=Sum([StdLabor]) + txtRunTotal
--
Marsh
MVP [MS Access]

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

 

[Marshall Barton]

I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

Stop right there. Step away from the keybord and keep your
hands up ;-)

Because report sections are processed as many times and in
whatever order is necessary for Access to produce the
formatting and display you want, you can not use code to
calculate values from more than the one section with the
code.

See my earlier reply about using a Running Sum text box
instead of the doomed idea of using code to calculate a
total.

 

[rickr]

That definitely did the trick. Thank you ever so much.

Sometimes it is the simple things that really cause the most difficulties. I
truly appreciate your help.

So, for those that may want a solution recap:
If you have a report with a sub report, and need to use values from the
sub-report in order to calculate total report values, follow this procedure:
1. For each sub-report value that needs to be referenced on the main report
totals
a. Create a text-box and place it on the main detail section, name it
something useful like 'col_1_Sum'
b. In the Control Source, reference the sub-report data element like so:

=IIf([Sub-Report_Name].Report.HasData,Nz([Sub-Report_Name].Report.column1,0),0)
c. Set the text-box 'Running Sum' property as necessary (I created 2
text boxes, 1 for the primary group and the second for the 'Over All' option)
d. Optional: Make the text-box NOT Visible
2. In the Report Footer, set the text-box where the total is going to be
displayed like this (using the text box with Over All running sum):
= Sum([col1]) + [col_1_sum]

Works like a charm.

Thanks again.

Sorry, I was so focused on the subreport reference that I
missed the thee fact that you were using Sum to try to total
the subreport values. That won't work because the aggregate
functions (Count, Sum, etc) can only operate on revord
source fields, they are unaware of controls in the report.

Instead you should use a running sum text box (named
txtRunTotal) in the same section as the subreport. The text
box's expression would be like:
=IIf([Product - Pegged].Report.HasData, [Product -
Pegged].Report![PegStdLabor], 0)

Then the report footer textbox can use the expression:
=Sum([StdLabor]) + txtRunTotal
--
Marsh
MVP [MS Access]

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

rickr wrote:
Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...


If the subreport might not have any records, change the
expression to:

=Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
[Product - Pegged].Report![PegStdLabor], 0))

.

 

[Marshall Barton]

That definitely did the trick. Thank you ever so much.

Sometimes it is the simple things that really cause the most difficulties. I
truly appreciate your help.

So, for those that may want a solution recap:
If you have a report with a sub report, and need to use values from the
sub-report in order to calculate total report values, follow this procedure:
1. For each sub-report value that needs to be referenced on the main report
totals
a. Create a text-box and place it on the main detail section, name it
something useful like 'col_1_Sum'
b. In the Control Source, reference the sub-report data element like so:

=IIf([Sub-Report_Name].Report.HasData,Nz([Sub-Report_Name].Report.column1,0),0)
c. Set the text-box 'Running Sum' property as necessary (I created 2
text boxes, 1 for the primary group and the second for the 'Over All' option)
d. Optional: Make the text-box NOT Visible
2. In the Report Footer, set the text-box where the total is going to be
displayed like this (using the text box with Over All running sum):
= Sum([col1]) + [col_1_sum]

Works like a charm.

Nice wrap up summary. Here's hoping a lot of people with
the same issue find it when they search for a solution for
this problem.