Sum Total from Subreport on main report

[fibayne]

Hi
I have looked through previous posts on this subject without being able to find where I am doing wrong,

MainRpt has a calculated control, which is a nested IIF, [DlvdNet]
SubRpt has a field [InvVal] (there can be many InvVal for each DlvdNet as each DlvdNet can have many invoices billed against it)

The other problem is that for each InvVal the main report repeats DlvdNet against each InvVal which when I sum DlvdNet overstates it by the number of InvVal entered, there should be only one DlvdNet and many InvVal ? are the relationships incorrect or is due to the nested IIf ?

I would like a field in the report footer [BalanceOwed] which is Sum([DlvdNet])-Sum([InvVal])

If anyone understands this, their advice would be greatly appreciated as always.
cheers Fi

 

[Pat Hartman]

If you are trying to sum a field from the 1-side table, you must use a subreport for the many-side data. Split the query and create a subreport to solve the problem.

 

[fibayne]

thanks Pat ..I shall try this and post back..cheers Fi

 

[fibayne]

Hi Pat, this seems to be almost working I'm using =[Report].[rpt_sr_DlvdDetail]![InvoiceValue] as the control source for the text box on the main form that picks up the Invoice Value from the subreport but it is showing the first invoice value of the whole selection only, through the whole report, I tried to sum this in the report footer but it is blank , no error message, do you know where I am going wrong..thanks for any more advice you can give..cheers Fi

 

[mithani]

Hi fibayne,

could you please upload your report?

mithani

 

[Pat Hartman]

You need to sum the data in the subreport's report footer. Then reference the field in the report footer. That will get you the sum for the subreport rather than the first detail value.

 

[fibayne]

Hi Mithiani, sorry not sure how to upload a report only ?

Pat, I have done as you said above but am still not getting it to sum correctly, when i put the sum in the report footer of the subreport it worked fine, but on the main report the Report Footer shows on the subreport as the sum of each grouped record rather the the sum of all ....
I am using
=IIf([rpt_sr_DlvdDetail].[Report].[HasData],[rpt_sr_DlvdDetail].[Report]![SubTotal],0)
to get the value form the subreport to the main form detail, named it SubInvoiceTotal and referenced a text box to it which works fine...

again any more advice would be greatly appreciated...cheers Fi

 

[fibayne]

Hi

Not sure if I explained the problem well enough in my last post as to what I am still having probs with, I have followed Pats advice and am now able to show the total from each record on the subform on the main form but still cannot work out how to make the Grand Total of the subreport show on the report footer of the main form, all I cna get it to show is randomly one of the totals of one of the records on the subform, I would really really appreciate any further help with this as it looks like it should be a fairly simple explanation but I just dont know how to fix it...

cheers Fi

 

[Rich]

It looks as though you are referrencing the wrong field on the main Report from your subreport.

 

[fibayne]

Hi Rich thanks for replying,
I agree it does look like that but the field on the DetailFooter on the subreport is SubTotal and that seems to be what I am referencing to on the Main Report.
I tried putting a GrandTotal on the SubReport, Report Footer and referenced that on the Main Report and although the SubReport totals correctly, on the SubReport footer the main report is picking up the wrong value, it picks up the same value as the referenced cell SubTotal picks up, any ideas ???

thanks again Fi

 

[boblarson]

Can you post your db? It would probably be quicker at this point to just do it quickly for you so you can see how to do it.

 

[boblarson]

Just an FYI so you didn't think I forgot about you - I'm working on it.

 

[fibayne]

Hi Bob
thanks again for looking at this I look forward to your solution...cheers Fi

 

[fibayne]

Hi..I managed to sort out the problem I mentioned above and will post how I did this for anyone else who may come across the same prob..however I now want to summarise the data by supplier ie show the list of suppliers with their total liability rather than the liability against each order, I am close but if there is more than one invoice value posted against an order it counts the order value for however many times an invoice is posted against this order, what it should do ..is show that value of the order and the sume of the invoices that make up that order value....been at this a looooong time now and would love some help ...cheers again Fi

 

[jmsgomes]

Hello Fi (fibayne),

I can't find the post on how you fixed this issue. I too am having difficulty pulling a total from a subreport to appear in the main report so I can sum it to other totals from other subreports.

Could you explain to me how you fixed it or point me to where I can find the solution?

--
Jorge

 

[boblarson]

Hello Fi (fibayne),

I can't find the post on how you fixed this issue. I too am having difficulty pulling a total from a subreport to appear in the main report so I can sum it to other totals from other subreports.

Could you explain to me how you fixed it or point me to where I can find the solution?

--
Jorge

See if this sample helps:
http://downloads.btabdevelopment.com/Samples/subforms/SAMPLE-SubformTotalOnMainForm.zip

 

[jmsgomes]

See if this sample helps:
http://downloads.btabdevelopment.com/Samples/subforms/SAMPLE-SubformTotalOnMainForm.zip

Awesome response, Bob!

Thank you! Thank you! Thank you!

The sample did help, even though at first I was slightly thrown off by it being a form but then again a form is a report is a form, and so on. It validated the concept of what I wanted to implement but I was still stumped for a while, until I noticed that the child subreport's name was the key to what I was doing wrong: even though I had a saved subreport with the right name, I needed to make sure the name of the child object in the main report matched as the source. I think this explains my error and hopefully can help others who run into the same situation.

 

[Funkbuqet]

I know this is an older thread but i have a question. I have a report in which I am trying to build a table in the main form with textboxes populated with info from the subforms. Using expressions like the one in the example, it is working out well, except - in the final form I only want the table on the mainform dispalyed and when I set the details section (where all the subforms are) as Visible:No i get a #Error. All the info is still there but it cannot recall it unless it is visible? If I make just the subtable Visible:No it works but then I am left with this large blank area under my table. By the way, the table on my main form is located in the main report "page header" section. Is there a work around for this? I originally tried using a query to combine some of the totals from different tables/queries, but that ended up beinga huge headache that I could never get to work. Any help would be appreciated. Thank you.