Feb 20 2023 07:27 AM
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?
Feb 20 2023 09:01 AM - edited Feb 20 2023 09:03 AM
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
Feb 20 2023 09:21 AM
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
Feb 20 2023 02:11 PM
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
Feb 21 2023 08:41 AM
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?