Dsum

[RJF61]

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 each of the tables.

I have tried the following expressions...

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

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

Can someone assist with what I'm missing?  

[#2]

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

[#3]

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

[#4]

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

[#5]

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?