If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating page/report sums using calculated data from subquery
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. |
#2
|
|||
|
|||
Calculating page/report sums using calculated data from subreport
The title has been corrected. I apologize for any initial confusion
"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... I am confused to no end. Any help is appreciated. |
#3
|
|||
|
|||
Calculating page/report sums using calculated data from subquery
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)) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Calculating page/report sums using calculated data from subque
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. "Marshall Barton" wrote: 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)) -- Marsh MVP [MS Access] . |
#5
|
|||
|
|||
Calculating page/report sums using calculated data from subque
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? "rickr" wrote: 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. "Marshall Barton" wrote: 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)) -- Marsh MVP [MS Access] . |
#6
|
|||
|
|||
Calculating page/report sums using calculated data from subque
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. |
#7
|
|||
|
|||
Calculating page/report sums using calculated data from subreport
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. "rickr" wrote: 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? "rickr" wrote: 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. "Marshall Barton" wrote: 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)) -- Marsh MVP [MS Access] . |
#8
|
|||
|
|||
Calculating page/report sums using calculated data from subque
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] rickr wrote: 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. "Marshall Barton" wrote: 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)) |
#9
|
|||
|
|||
Calculating page/report sums using calculated data from subque
rickr wrote:
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. -- Marsh MVP [MS Access] |
#10
|
|||
|
|||
Calculating page/report sums using calculated data from subque
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 procedu 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. "Marshall Barton" wrote: 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] rickr wrote: 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. "Marshall Barton" wrote: 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)) . |
|
Thread Tools | |
Display Modes | |
|
|