Add Totals - Two Separate Tables in Report

[Dave_Willett]

Hi Guys

I have a three tables:
Customers
AppQuotesItems
AppQuotesParts

Both AppQuotesParts and AppQuotesItems have columns which calculate totals, VAT, Discount etc, they are both exactly the same in structure other than the table name.

I have a report which initially is based on Customers and pulls data from AppQuotesItems

<<Start: FILTER(“Appquotesitems”, AND([JobID]=[_THISROW].[JobID], [Status]=“Authorised”))>><<[Item]>>…and several other column data follows … this all works very well…

I can do the same for AppQuotesParts very easily:

<<Start: FILTER(“AppquotesParts”, AND([JobID]=[_THISROW].[JobID], [Status]=“Authorised”))>><<[Item]>>…etc

And here is wherethe problem lies.
How do i Sum the values of both on the same report ??

[AlexM]

You can SUM in Word

[Dave_Willett]

Thanks Alex
So how would I SUM both values?
The report does SUM data from AppQuotesItems already (example):

<< SUM(SELECT(Appquotesitems[Cost] , AND([JobID]=[_THISROW].[JobID], [Status]=”Authorised”)))>>

What would be the formula to SUM that and the data from AppQuotesParts ?

<< SUM(SELECT(AppquotesParts[Cost] , AND([JobID]=[_THISROW].[JobID], [Status]=”Authorised”)))>>

Much appreciated

*** UPDATE***

This is looking good:

<< SUM(SELECT(Appquotesitems [Total_11] , AND([JobID]=[_THISROW].[JobID], [Status]=”Authorised”)))+ SUM(SELECT(Appquotesparts [Total_11] , AND([JobID]=[_THISROW].[JobID], [Status]=”Authorised”)))>>

Unless you guys advise me differently but it seems to be working ??

[AlexM]

Dave_Willett:

lues of both on the same report

Either that or if you are using a table in Word, then you can just add a formula to the Total cell to SUM(ABOVE).

[Dave_Willett]

Thanks Alex
I am still uncertain of how to do that. Does the cell have a reference or name so I can simplify this or should I just stick with what I have?

Thanks

[Steve]

Dave_Willett:

Unless you guys advise me differently but it seems to be working ??

Don’t do the calculations in the spreadsheet as suggested by @AlexM.

[Dave_Willett]

Thanks Steve, I stuck to the original without calculating in the Word Doc, it worked so I left it as it was.
I am very greatful for all the support from everybody always.

Kind Regards

[AlexM]

It’s not the spreadsheet I was sugesting, it was in the template.

[Dave_Willett]

Thats interesting and probably where crossed lines came from. I was referring to the template and Steve mentioned the spreadsheet.
The solution does work as it is so quite happy to leave it as is, but the possibility of formula’s in the word template could prove beneficial later.

So I thank both of you guys.
Kind Regards

[AlexM]

[Dave_Willett]

Thats interesting Alex, thank you.