I have created a monster!

The following (is not pretty but) produces a query successfully: I have inserted carriage returns to aid readability.

I now need to provide a report in access that has a Grand Total of the Total O/S column at the report footer, but am getting Group By errors.

Have tried to use DSum instead of a subquery in the SQL but cannot get this to work in place of the existing sub queries.

Can anyone spot any howling errors or make any suggestions. Will DSum be my answer? Am sorry if I have not explained too clearly but am tired after long day. If clarification needed please ask. Thank you.

SELECT
Analysis_owner.Manager,
quo.CCS_QUO_ANALYSIS_CODE AS AnaCode,
quo.[CCS_QUO_REF_NUMBER#1] AS RefNo1,
quo.[CCS_QUO_REF_NUMBER#2] AS RefNo2,
quo.[CCS_QUO_ADDRESS#1] AS Project,
quo.CCS_QUO_COST_YTD AS CostYTD,
quo.[CCS_QUO_COST_PRICE#6] AS Income,

(SELECT SUM(Nz(sil.[CCS_SIL_TOTAL],0)-Nz(sil.[CCS_SIL_VAT],0)) FROM CONSTRUCT_CCS_S_INVOICE_LINE sil WHERE quo.[CCS_QUO_QUOTE_NUMBER] = sil.[CCS_SIL_QUOTE_NUMBER] AND sil.[CCS_SIL_INV_MODE] Not Like '5' AND sil.[CCS_SIL_INV_MODE] Not Like '8' AND sil.[CCS_SIL_POSTED] <> 3) AS InvoiceExVAT,

(SELECT ROUND(SUM(trans.[ALLOCATED_AMOUNT])/1.175,2) FROM [ACCOUNTING_SYSTEM_SALES_TRANSACTIONS] trans, [CONSTRUCT_CCS_S_INVOICE_LINE] sil WHERE trans.[UNIQUE_REFERENCE_NO] =sil.[CCS_SIL_UNIQUE_REF_NO] AND quo.[CCS_QUO_QUOTE_NUMBER] =sil.[CCS_SIL_QUOTE_NUMBER]) AS ['Cash Recd'],

Round(Nz([InvoiceExVAT],0)-Nz(['Cash Recd'],0),2) AS ['Cash O/S'],
Round(Nz([ACCRUED_INC],0),2) AS ['Accrued Inc'],
Round(Nz([WIP],0),2) AS ['WIP'],
CCur((Nz(['Cash O/S'],0)+Nz(['Accrued Inc'],0)+Nz(['WIP'],0))) AS ['Total O/S']

FROM (CONSTRUCT_CCS_QUOTES AS quo INNER JOIN MONTHLY_PL ON quo.CCS_QUO_QUOTE_NUMBER=MONTHLY_PL.QUOTE_NUMBER) INNER JOIN Analysis_owner ON quo.CCS_QUO_ANALYSIS_CODE=Analysis_owner.Analysis_ code;