getting totals from subreports

[Craig Armitage]

Hi again,

I have 2 tables, one for customers and one for jobs.. the jobs table links
back to the customer using foreign key. in the jobs table i have a field
that lists the job charge.

I have made a report that lists all the customers and a subreport under each
customer that lists all the jobs with charges.

I want to get a total of all the charges on the report for all jobs in all
customers to put at the bottom of the page.

Also, if i open the report with a filter, i need it to only show the charges
for the filtered customers.

i cant seem to get access to do this yet im sure its simple...

please help!

 

[Tom Wickerath]

Hi Craig,

Do you really need the subreport? Your task is generally easier if you
simply use View > Sorting and Grouping (in report design view) to add a group
header and footer for the customer. List the jobs in the detail section. In
the report footer, you can add a text box that sums the appropriate numeric
field.

Take a look at the Invoices report in the sample Northwind database. You
will see a text box in the OrderID footer named InvoiceSubtotal, with control
source: =Sum([ExtendedPrice]). Click on View > Report Header/Footer to
display a footer (if this option is not already selected). Then copy the text
box in the OrderID footer to the report footer.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Graham Mandeno]

Hi Craig

I would not use a subreport for this, but instead base the report on a
single query including all the relevant fields from both tables and then
group by customer with a group header and footer. Put everything you
currently have above the subform into the group header, and everything below
in the group footer. Add the job detail fields to the detail section, and
in the group footer put a textbox with the ControlSource:
=Sum([JobCharge])
....where JobCharge is the charge field from your Jobs table.

Copy and paste this textbox to the report footer section. If the group
footer it will sum only the charges for that group (customer) and in the
report footer it will sum the charges for the entire report.

If for some reason you need to continue with the subreport format, then add
a field to the recordsource of your main report:
TotalJobCharge: DSum( "JobCharge", "Jobs", "CustID=" & CustID)
(substitute your own field names, naturally)

Then add a textbox to your report footer:
=Sum([TotalJobCharge])

 

[Ian Craig Armitage]

Hi Graham and Tom,

Thanks for both your replies..

Graham,

Im struggling to find the "group header" did you mean the page header? Im
trying out what you said, but struggling as it seems to be dropping all the
jobs in regardless of whether they are for a customer or not.. I will just
take a brief moment to show you guys the layout i was hoping to achieve..


1: Customer: Mr Jones Location: North Wales Contact
No: 223827387 Refferal: Yellow Pages
Job #1 Problem: Leaky Boiler Fault: blah blah
Labour Cost: £50 Parts Cost: £70
Job #2 Problem: Leaky Boiler Fault: blah blah
Labour Cost: £40 Parts Cost: £30
Job #3 Problem: Leaky Boiler Fault: blah blah
Labour Cost: £70 Parts Cost: £50
Job #4 Problem: Leaky Boiler Fault: blah blah
Labour Cost: £70 Parts Cost: £70
Totals
£230 £220

2: Customer: Mr Smith Location: North Wales Contact
No: 657565765 Refferal: Weekly News
Job #1 Problem: Leaky Boiler Fault: blah blah
Labour Cost: £50 Parts Cost: £70
Totals
£50 £70

------------------------------------------------------------------------------------------------------------------------
Overall Totals:
£280 £290


Im assuming that if the report looked like this, i could run a filter to
just show a specific customer report or even only jobs from referalls of a
specific type.

Hopefully you guys can help!

Hi Craig

I would not use a subreport for this, but instead base the report on a
single query including all the relevant fields from both tables and then
group by customer with a group header and footer. Put everything you
currently have above the subform into the group header, and everything
below in the group footer. Add the job detail fields to the detail
section, and in the group footer put a textbox with the ControlSource:
=Sum([JobCharge])
...where JobCharge is the charge field from your Jobs table.

Copy and paste this textbox to the report footer section. If the group
footer it will sum only the charges for that group (customer) and in the
report footer it will sum the charges for the entire report.

If for some reason you need to continue with the subreport format, then
add a field to the recordsource of your main report:
TotalJobCharge: DSum( "JobCharge", "Jobs", "CustID=" & CustID)
(substitute your own field names, naturally)

Then add a textbox to your report footer:
=Sum([TotalJobCharge])
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi again,

I have 2 tables, one for customers and one for jobs.. the jobs table
links back to the customer using foreign key. in the jobs table i have a
field that lists the job charge.

I have made a report that lists all the customers and a subreport under
each customer that lists all the jobs with charges.

I want to get a total of all the charges on the report for all jobs in
all customers to put at the bottom of the page.

Also, if i open the report with a filter, i need it to only show the
charges for the filtered customers.

i cant seem to get access to do this yet im sure its simple...

please help!

 

[Ian Craig Armitage]

Just to let you know that the post has changed the layout of the report,
basically each customer and each job would be on a line each.

Hi Graham and Tom,

Thanks for both your replies..

Graham,

Im struggling to find the "group header" did you mean the page header?
Im trying out what you said, but struggling as it seems to be dropping all
the jobs in regardless of whether they are for a customer or not.. I will
just take a brief moment to show you guys the layout i was hoping to
achieve..


1: Customer: Mr Jones Location: North Wales
Contact No: 223827387 Refferal: Yellow Pages
Job #1 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £50 Parts Cost: £70
Job #2 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £40 Parts Cost: £30
Job #3 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £70 Parts Cost: £50
Job #4 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £70 Parts Cost: £70
Totals £230 £220

2: Customer: Mr Smith Location: North Wales
Contact No: 657565765 Refferal: Weekly News
Job #1 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £50 Parts Cost: £70
Totals £50 £70

------------------------------------------------------------------------------------------------------------------------
Overall Totals: £280 £290


Im assuming that if the report looked like this, i could run a filter to
just show a specific customer report or even only jobs from referalls of a
specific type.

Hopefully you guys can help!

Hi Craig

I would not use a subreport for this, but instead base the report on a
single query including all the relevant fields from both tables and then
group by customer with a group header and footer. Put everything you
currently have above the subform into the group header, and everything
below in the group footer. Add the job detail fields to the detail
section, and in the group footer put a textbox with the ControlSource:
=Sum([JobCharge])
...where JobCharge is the charge field from your Jobs table.

Copy and paste this textbox to the report footer section. If the group
footer it will sum only the charges for that group (customer) and in the
report footer it will sum the charges for the entire report.

If for some reason you need to continue with the subreport format, then
add a field to the recordsource of your main report:
TotalJobCharge: DSum( "JobCharge", "Jobs", "CustID=" & CustID)
(substitute your own field names, naturally)

Then add a textbox to your report footer:
=Sum([TotalJobCharge])
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi again,

I have 2 tables, one for customers and one for jobs.. the jobs table
links back to the customer using foreign key. in the jobs table i have
a field that lists the job charge.

I have made a report that lists all the customers and a subreport under
each customer that lists all the jobs with charges.

I want to get a total of all the charges on the report for all jobs in
all customers to put at the bottom of the page.

Also, if i open the report with a filter, i need it to only show the
charges for the filtered customers.

i cant seem to get access to do this yet im sure its simple...

please help!

 

[Graham Mandeno]

Hi Craig

In the "Sorting and Grouping" window, (View>Sorting and Grouping) you choose
the field on which you wish to group (CustomerID?) in the "Field/Expression"
column.

Then, in the "Group Properties" section below, set both "Group Header" and
"Group Footer" to "Yes". This will add two new sections to your report.

Now, move the customer header line into the "CustomerID_Header" section and
the customer totals line into the "CustomerID_Footer" section. The job
details stay in the Detail section.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham and Tom,

Thanks for both your replies..

Graham,

Im struggling to find the "group header" did you mean the page header?
Im trying out what you said, but struggling as it seems to be dropping all
the jobs in regardless of whether they are for a customer or not.. I will
just take a brief moment to show you guys the layout i was hoping to
achieve..


1: Customer: Mr Jones Location: North Wales
Contact No: 223827387 Refferal: Yellow Pages
Job #1 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £50 Parts Cost: £70
Job #2 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £40 Parts Cost: £30
Job #3 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £70 Parts Cost: £50
Job #4 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £70 Parts Cost: £70
Totals £230 £220

2: Customer: Mr Smith Location: North Wales
Contact No: 657565765 Refferal: Weekly News
Job #1 Problem: Leaky Boiler Fault: blah blah Labour
Cost: £50 Parts Cost: £70
Totals £50 £70

------------------------------------------------------------------------------------------------------------------------
Overall Totals: £280 £290


Im assuming that if the report looked like this, i could run a filter to
just show a specific customer report or even only jobs from referalls of a
specific type.

Hopefully you guys can help!

Hi Craig

I would not use a subreport for this, but instead base the report on a
single query including all the relevant fields from both tables and then
group by customer with a group header and footer. Put everything you
currently have above the subform into the group header, and everything
below in the group footer. Add the job detail fields to the detail
section, and in the group footer put a textbox with the ControlSource:
=Sum([JobCharge])
...where JobCharge is the charge field from your Jobs table.

Copy and paste this textbox to the report footer section. If the group
footer it will sum only the charges for that group (customer) and in the
report footer it will sum the charges for the entire report.

If for some reason you need to continue with the subreport format, then
add a field to the recordsource of your main report:
TotalJobCharge: DSum( "JobCharge", "Jobs", "CustID=" & CustID)
(substitute your own field names, naturally)

Then add a textbox to your report footer:
=Sum([TotalJobCharge])
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi again,

I have 2 tables, one for customers and one for jobs.. the jobs table
links back to the customer using foreign key. in the jobs table i have
a field that lists the job charge.

I have made a report that lists all the customers and a subreport under
each customer that lists all the jobs with charges.

I want to get a total of all the charges on the report for all jobs in
all customers to put at the bottom of the page.

Also, if i open the report with a filter, i need it to only show the
charges for the filtered customers.

i cant seem to get access to do this yet im sure its simple...

please help!