locked
Use of DSum Values in Report RRS feed

  • Question

  • I have a report based on a query (that is represented by the following SQL).  My question is, what expressions are needed to sum each of the DSum values (ALbrDSum, AMESDSum, ELbrDSum, EMESDSum, ATotal and ETotal) in the Report Footer?  

    SELECT tblProj.ProjID, tblProj.Status, tblProj.CNoWBSE, tblWO.WONo, FormatCurrency(DSum("[Valin RepCurLbr]","tblALbr","WOOrder=""" & DLookUp([WONo],"tblWO") & """ And [Valin RepCurLbr] >0")) AS ALbrDSum, FormatCurrency(DSum("[Valin RepCurMES]","tblAMES","WOOrder=""" & DLookUp([WONo],"tblWO") & """")) AS AMESDSum, FormatCurrency(DSum("[ECraftCst]","tblELbr","ELbrID=" & [ProjID])) AS ELbrDSum, FormatCurrency(DSum("[EMESCst]","tblEMES","EMESID=" & [ProjID])) AS EMESDSum, DSum("[Valin RepCurLbr]","tblALbr","WOOrder=""" & DLookUp([WONo],"tblWO") & """ And [Valin RepCurLbr] >0")+DSum("[Valin RepCurMES]","tblAMES","WOOrder=""" & DLookUp([WONo],"tblWO") & """") AS ATotal, DSum("[ECraftCst]","tblELbr","ELbrID=" & [ProjID])+DSum("[EMESCst]","tblEMES","EMESID=" & [ProjID]) AS ETotal

    FROM ((((tblProj INNER JOIN tblWO ON tblProj.ProjID = tblWO.[WOID]) LEFT JOIN tblALbr ON tblWO.WONo = tblALbr.WOOrder) LEFT JOIN tblELbr ON tblProj.ProjID = tblELbr.[ELbrID]) LEFT JOIN tblEMES ON tblProj.ProjID = tblEMES.EMESID) LEFT JOIN tblAMES ON tblWO.WONo = tblAMES.WOOrder

    WHERE (((tblProj.Status)="Active"))

    ORDER BY tblProj.CNoWBSE;


    Thanks PU_RJF

    Monday, June 1, 2020 1:29 PM

All replies

  • Just like any other bound control:

    =Sum([ControlSource of the control to sum])


    -Tom. Microsoft Access MVP

    Monday, June 1, 2020 1:39 PM