Tags: access, adding, database, microsoft, monthyl, mysql, oracle, report, sql, subreports, system, together, totals, values

Adding totals from two subreports

On Database » Microsoft Access

3,986 words with 2 Comments; publish: Fri, 06 Jun 2008 12:25:00 GMT; (250101.56, « »)

Hi,

Can anyone tell me how to add together two values from two subreports.

I have a Monthyl Report system within which there are two subreports - one

lists CashSales the other lists Invoices.

At the bottom of each subreport there is a total of sales value.

I want to be abale to add together these two totals to produce an overall

total.

I have tried using the expression builder and got this =Sum([CashSales

Query]![SubTotal])+Sum([Invoices Query]![SubTotal]) but that just gives an

Error value.

Any help is appreciated.

All Comments

Leave a comment...

  • 2 Comments
    • Colin, there are several steps to this.

      Firstly, make sure the subreport has a Report Footer section (View menu in

      report design.) Add a text box there, and set these properties:

      Control Source: =Sum([Amount])

      Format: Currency

      Name txtSubTotal

      You can set the Visible property of the section to No if you don't want to

      see it.

      Now you can bring this value back onto the main report:

      =[Sub1].[Report].[txtSubTotal]

      Replace "Sub1" with the name of your subreport control.

      That generates an error if the subreport has no entries, so you need to test

      the HasData property. Also recommend using Nz() in case a Null value is

      returned, since you want to add it to another value, so you really want:

      =IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtSubTotal],0), 0)

      Once you have that working for both working, you can add them together.

      Allen Browne - Microsoft MVP. Perth, Western Australia.

      Tips for Access users - http://allenbrowne.com/tips.html

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

      "Colin" <Colin.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      news:3264A71F-E780-467C-BC14-2A90418BC041.ms-access.questionfor.info.microsoft.com...

      > Hi,

      > Can anyone tell me how to add together two values from two subreports.

      > I have a Monthyl Report system within which there are two subreports - one

      > lists CashSales the other lists Invoices.

      > At the bottom of each subreport there is a total of sales value.

      > I want to be abale to add together these two totals to produce an overall

      > total.

      > I have tried using the expression builder and got this =Sum([CashSales

      > Query]![SubTotal])+Sum([Invoices Query]![SubTotal]) but that just gives an

      > Error value.

      > Any help is appreciated.

      #1; Fri, 06 Jun 2008 12:27:00 GMT
    • Hi,

      Thanks for help, worked prefectly

      "Allen Browne" wrote:

      > Colin, there are several steps to this.

      > Firstly, make sure the subreport has a Report Footer section (View menu in

      > report design.) Add a text box there, and set these properties:

      > Control Source: =Sum([Amount])

      > Format: Currency

      > Name txtSubTotal

      > You can set the Visible property of the section to No if you don't want to

      > see it.

      > Now you can bring this value back onto the main report:

      > =[Sub1].[Report].[txtSubTotal]

      > Replace "Sub1" with the name of your subreport control.

      > That generates an error if the subreport has no entries, so you need to test

      > the HasData property. Also recommend using Nz() in case a Null value is

      > returned, since you want to add it to another value, so you really want:

      > =IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtSubTotal],0), 0)

      > Once you have that working for both working, you can add them together.

      > --

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

      > Tips for Access users - http://allenbrowne.com/tips.html

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

      > "Colin" <Colin.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      > news:3264A71F-E780-467C-BC14-2A90418BC041.ms-access.questionfor.info.microsoft.com...

      >

      >

      #2; Fri, 06 Jun 2008 12:28:00 GMT