Problem with Calculated Control in Report

[Jere Shaw]

I created a small application primarily to print invoices for a small
business. The invoices work as expected. However, I am having a problem
creating another report that contains a calculated control in the report
footer section that should return a total for all records in one of the
fields. Allow me to explain.



The application has two tables: Job and Job Details in one- to- many
relationship. Among other fields, the Job table contains JobID, Name,
JobDate, and LaborCost fields. The Job Details table contains JobID,
PartDescription, PartPrice, and Quantity fields.



The report with the problem, which shows all records in the database, is
based on a query that contains fields from each table as well as a
calculated field. The report has several columns. One column is for
LaborCost and another is for PricePart. When I create a calculated control
in the report footer section of the report to total the PartPrice field all
works well. The LaborCosts, however, is a different matter. The calculated
control in the footer section that is suppose to total LaborCost returns a
value as if there is a labor cost for each PartPrice.



To explain further, one job has only one LaborCost but may have several
parts in the Job Detail table. For example, the LaborCost for a job may be
$200. If there are two parts, the calculated control in the report footer
section shows $400 in labor costs instead of $200. It doesn't matter if I
group the records or not.



Does anyone have a solution to my problem?

 

[Allen Browne]

Hi Jere.

Your query repeats the labor cost on each line, so when the report sums it
it is inflated *way* above what it should be. Yes that makes sense.

To work around this, you need to create a group header/footer based on the
JobID. (You may even have this already). You can then use a running sum to
get a total of the labor for the entire report.

Steps:

1. Open the report in Design view.

2. Open the Sorting And Grouping dialog (View menu).

3. In the upper pane of the dialog, select the JobID field.
In the lower pane set Group Header to Yes, and Group Footer to Yes.
You should now see grey bands across your report for these sections.

4. Put the relevant fields into the JobID group header section. Typically,
the address panel, invoice date, and so on.

5. In the Group Footer section, put the LabourCost field, and also a the
text box to show the parts total for the invoice. Its Control Source will
be:
=Sum([Quantity] * [PartPrice])

6. Add another text box to the group footer section to collect the
progressive total for the laborcosts. Give it these properties:
Name txtLaborCostRS
Control Source =[LaborCost]
Running Sum Over All
Format Currency
Visible No

7. In the Report Footer section, show a text box that displays the running
sum of the labor costs. It's ControlSource will be:
=txtLaborCostRS

 

[tina]

i tore my hair over this one last night, and all the time it was that
simple. another addition to my (weak) Reports arsenal - thanks, Allen!

Hi Jere.

Your query repeats the labor cost on each line, so when the report sums it
it is inflated *way* above what it should be. Yes that makes sense.

To work around this, you need to create a group header/footer based on the
JobID. (You may even have this already). You can then use a running sum to
get a total of the labor for the entire report.

Steps:

1. Open the report in Design view.

2. Open the Sorting And Grouping dialog (View menu).

3. In the upper pane of the dialog, select the JobID field.
In the lower pane set Group Header to Yes, and Group Footer to Yes.
You should now see grey bands across your report for these sections.

4. Put the relevant fields into the JobID group header section. Typically,
the address panel, invoice date, and so on.

5. In the Group Footer section, put the LabourCost field, and also a the
text box to show the parts total for the invoice. Its Control Source will
be:
=Sum([Quantity] * [PartPrice])

6. Add another text box to the group footer section to collect the
progressive total for the laborcosts. Give it these properties:
Name txtLaborCostRS
Control Source =[LaborCost]
Running Sum Over All
Format Currency
Visible No

7. In the Report Footer section, show a text box that displays the running
sum of the labor costs. It's ControlSource will be:
=txtLaborCostRS

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I created a small application primarily to print invoices for a small
business. The invoices work as expected. However, I am having a problem
creating another report that contains a calculated control in the report
footer section that should return a total for all records in one of the
fields. Allow me to explain.



The application has two tables: Job and Job Details in one- to- many
relationship. Among other fields, the Job table contains JobID, Name,
JobDate, and LaborCost fields. The Job Details table contains JobID,
PartDescription, PartPrice, and Quantity fields.



The report with the problem, which shows all records in the database, is
based on a query that contains fields from each table as well as a
calculated field. The report has several columns. One column is for
LaborCost and another is for PricePart. When I create a calculated control
in the report footer section of the report to total the PartPrice field
all works well. The LaborCosts, however, is a different matter. The
calculated control in the footer section that is suppose to total
LaborCost returns a value as if there is a labor cost for each PartPrice.



To explain further, one job has only one LaborCost but may have several
parts in the Job Detail table. For example, the LaborCost for a job may be
$200. If there are two parts, the calculated control in the report footer
section shows $400 in labor costs instead of $200. It doesn't matter if I
group the records or not.