Forum Discussion

RJF61's avatar
RJF61
Copper Contributor
Feb 20, 2023

Dsum

I am trying to develop the following query/report in Access...   I believe what I want is to Dsum EMESCst values from tblEMES and Dsum AMESCst values for tblAMES where tblWO WONo = WOOrder in...
2013
2016
access
developer
Karl_Donaubauer's avatar
Karl_Donaubauer
MVP
Feb 20, 2023

Hi,

 

I can't judge if it's correct in logic, field types and some names due to lack of details, but in syntax you need 2 changes. The equals sign must be inside the quotes, and the parameters must be concatenated with ampersand:

 

SumofAMES: DSum("AMESCst","tblAMES","WOOrder=" & WONo)

SumofEMES: DSum("EMESCst","tblEMES","WOOrder=" & WONo)

 

If you get stuck, share more details and post the SQL text of the query you tried.

 

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

RJF61's avatar
RJF61
Copper Contributor
Feb 20, 2023

Hello Karl_Donaubauer 

Thanks for the response.  Following is the SQL and field support info...

 

SELECT tblProj.CNoWBSE, tblWO.WOID, DSum("AMESCst","tblAMES","WOOrder=" & [WONo]) AS SumofAMES, DSum("EMESCst","tblEMES","WOOrder=" & [WONo]) AS SumofEMES
FROM ((tblProj LEFT JOIN tblWO ON tblProj.ProjID = tblWO.WOID) LEFT JOIN tblEMES ON tblWO.WONo = tblEMES.WOOrder) LEFT JOIN tblAMES ON tblWO.WONo = tblAMES.WOOrder;

 

For each table below, the WONo and WOOrder fields are Short text - Field Size 10

 

 

 

  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Feb 20, 2023

    Hi,

     

    You have to surround text parameters with single quotes, i.e.

    DSum("AMESCst","tblAMES","WOOrder='" & [WONo] & "'")

    etc.

     

    The other question is, if you need DSum() at all. You could try to instead activate the Totals button in the ribbon and use the Sum aggregat function for the two fields, which is faster than DSum(). Have a look if you get the desired results with it.

     

    Servus
    Karl
    ****************
    Access Bug Trackers
    Access News
    Access DevCon

     

    • RJF61's avatar
      RJF61
      Copper Contributor
      Feb 21, 2023

      Hello,

      Following is what I have based on your suggestion...

      The SQL appears as...

      SELECT tblProj.CNoWBSE, tblWO.WOID, tblWO.WONo, Sum(tblEMES.EMESCst) AS SumOfEMESCst, Sum(tblELbr.ECraftCst) AS SumOfECraftCst, Sum(tblAMES.[Valin RepCurMES]) AS [SumOfValin RepCurMES], Sum(tblALbr.[Valin RepCurLbr]) AS [SumOfValin RepCurLbr]
      FROM ((((tblProj LEFT JOIN tblWO ON tblProj.ProjID = tblWO.WOID) LEFT JOIN tblEMES ON tblWO.WONo = tblEMES.WOOrder) LEFT JOIN tblAMES ON tblWO.WONo = tblAMES.WOOrder) LEFT JOIN tblELbr ON tblWO.WONo = tblELbr.WOOrder) LEFT JOIN tblALbr ON tblWO.WONo = tblALbr.WOOrder
      WHERE (((tblALbr.[Cost Element])<>890001)) OR (((tblALbr.[Cost Element])<>890002))
      GROUP BY tblProj.CNoWBSE, tblWO.WOID, tblWO.WONo;

       

      As shown, what I really need is to add the sum of the EMESCst and sum of ECraftCst  to create EBudget and add the sum of the Valin repCurMES and sum of Valin repCurLbr to create AExpense.  Then create a Var (expression) calculating the difference between EBudget and AExpense.

       

      Does this make sense? 

Resources