Trouble with DSum in a Query 1

[djmurphy58]

Novice user here.

Having trouble getting DSUM to work in a query. My expression is:

DSum(&quot;[CostCodeQuantity]&quot;,&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[Date]<=&quot; & [Date])

The problem is that when I run the query, nothing shows up in this new field.

What did I do wrong? My field &quot;CostCodeQuantity&quot; comes from tblDailyBudgetIntermediateTable and the field &quot;Date&quot; comes from another table called tblDailyBudget.

Did I write the expression incorrectly??

Thank you,
Dennis

 

[nicsin]

I don't think you can use Date as a field since it is a reserved keyword. try renaming your field to something like myDate.

nicsin

 

[dhookom]

In addition to the comments regarding using Date as a field name, you can try add date delimiters (#). Try:
DSum(&quot;[CostCodeQuantity]&quot;,&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[Date]<=#&quot; & [Date] & &quot;#&quot

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[djmurphy58]

OK-I did both suggestions.

First I changed my field name to WorkDate. Then I used Duane's expression.

But I still get the same result - nothing appears in my new RunningSum field......it's not calculating it for some reason.

 

[djmurphy58]

Is it a problem that my WorkDate field is in a different table than my CostCodeQuantity Field???

Dennis

 

[dhookom]

It might help if you posted your entire SQL and possibly your table and field names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Use a correlated subquery instead of DSum.

(Select Sum(CostCodeQuantity) From tblDailyBudgetIntermediateTable as a
Where a.WorkDate<=OtherTable.WorkDate) as BudgetSum

I believe this is more efficient.

 

[djmurphy58]

OK-
So here is my SQL:

SELECT tblDailyBudget.WorkDate, tblCostCodes.CostCodeNumber, tblCostCodes.CostCodeName, tblCostCodes.CostCodeUnits, tblCostCodes.BudgetMHFactor, tblDailyBudgetIntermediateTable.CostCodeHours, tblDailyBudgetIntermediateTable.CostCodeQuantity, DSum([CostCodeHours],&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[WorkDate]<=&quot; & [WorkDate]) AS ToDateHours
FROM tblDailyBudget INNER JOIN (tblCostCodes INNER JOIN tblDailyBudgetIntermediateTable ON tblCostCodes.CostCodeID = tblDailyBudgetIntermediateTable.CostCodeID) ON tblDailyBudget.DateID = tblDailyBudgetIntermediateTable.DateID
WHERE (((tblDailyBudget.WorkDate) Between #11/3/2003# And #11/19/2003#) AND ((tblCostCodes.CostCodeNumber)=[Enter Cost Code]));

Also, here are the three tables in my query:

tblDaily Budget tblDailyBudgetIntermediateTable
DateID CostCodeID
WorkDate DateID
WorkPerformed CostCodeHours
CostCodeQuantity

tblCostCodes
CostCodeID
CostCodeName
CostCodeNumber
CostCodeUnits
BudgetUnitCost
BudgetMHFactor

Thanks alot for your help, it is much appreciated.

Dennis

 

[dhookom]

You have issues with:
DSum([CostCodeHours],&quot;tblDailyBudgetIntermediateTable&quot;, &quot;[WorkDate]<=&quot; & [WorkDate])

1) You don't have a field [WorkDate] in tblDailyBudgetIntermediateTable.
2) [CostCodeHours] would need to have quotes around it
3) You need to delimit the WorkDate with
&quot;[WorkDate]<=#&quot; & [Workdate] & &quot;#&quot;
You may need to replace the table in the DSum() with a query based on tblDailyZBudget and tblDailyBudgetIntermediateTable.

I agree with JonFer that a subquery is generally more efficient. The major weekness of the subquery is that it make the query read-only.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jebry]

Hi Dennis!

DSum, nor any other domain aggregate, is recognized by SQL to my knowledge. You must use Sum instead, with a subquery if that is appropriate. If not, then do what you need using two queries with the second query using the first as a table.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[dhookom]

Jeff,
DSum() can be used anywhere in Access (mdb) that any other function can be used provided the syntax is correct. I agree that subqueries would be more efficient though.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[djmurphy58]

Ok-thanks a bunch for the help guys.

How exactly do I do a subquery?? What do I do with JonFer's statement:

(Select Sum(CostCodeQuantity) From tblDailyBudgetIntermediateTable as a
Where a.WorkDate<=OtherTable.WorkDate) as BudgetSum

Do I add it to the &quot;build&quot; statement in the query field??

Thanks,
Dennis

 

[JonFer]

There are still a couple problems with your DSum (did we mention subqueries would be more efficient?) -

DSum([CostCodeHours],&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[WorkDate]<=&quot; & [WorkDate])

1. You need double quotes around [CostCodeHours] at beginning. Duane had this in his response above but it appears you did not copy it exactly.

2. You need &quot;#&quot; around [WorkDate] and WorkDate is in at least two of the tables in the query so you need to specify the table you want:

&quot;[WorkDate]<=#&quot; & [tblDailyBudget].[WorkDate] & &quot;#&quot;

Final:

DSum(&quot;[CostCodeHours]&quot;,&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[WorkDate]<=#&quot; & [tblDailyBudget].[WorkDate] & &quot;#&quot

That should work. Good luck.

 

[djmurphy58]

Ok-
I used JonFer's subquery, but I got an &quot;invalid operand&quot; error. Is somthing wrong with my syntax?:

(Select Sum(CostCodeHours)From tblDailyBudgetIntermediateTable as a Where a.WorkDate<=tblDailyBudget.WorkDate) As ToDateHours

Thanks,
Dennis

 

[djmurphy58]

JonFer-
Ok-yes, I did get the message that a subquery is more efficient - but I got an operand error. In the meantime, I just tried your statement:

DSum(&quot;[CostCodeHours]&quot;,&quot;tblDailyBudgetIntermediateTable&quot;,&quot;[WorkDate]<=#&quot; & [tblDailyBudget].[WorkDate] & &quot;#&quot

I copied and pasted it - but still nothing shows up when I run the query.

I'd rather do the subquery if that's more efficient - but apparently something is wrong with my syntax.

Thanks again for all your help.

Dennis

 

[dhookom]

You might try:
SELECT tblDailyBudget.WorkDate, tblCostCodes.CostCodeNumber, tblCostCodes.CostCodeName, tblCostCodes.CostCodeUnits, tblCostCodes.BudgetMHFactor, tblDailyBudgetIntermediateTable.CostCodeHours, tblDailyBudgetIntermediateTable.CostCodeQuantity,
(SELECT Sum([CostCodeHours]) FROM tblDailyBudgetIntermediateTable I INNER JOIN [tblDaily Budget] B ON B.DateID = I.DateID HAVING B.[WorkDate]<= [tblDaily Budget].[WorkDate]) AS ToDateHours
FROM tblDailyBudget INNER JOIN (tblCostCodes INNER JOIN tblDailyBudgetIntermediateTable ON tblCostCodes.CostCodeID = tblDailyBudgetIntermediateTable.CostCodeID) ON tblDailyBudget.DateID = tblDailyBudgetIntermediateTable.DateID
WHERE (((tblDailyBudget.WorkDate) Between #11/3/2003# And #11/19/2003#) AND ((tblCostCodes.CostCodeNumber)=[Enter Cost Code]));


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jebry]

Hi again!

One thing wrong is that tblDailyBudgetIntermediateTable doesn't have a WorkDate field and that is probably why your DSum comes back empty. Tell us the conditions you need to sum the hours, by CostCodeID? or by CostCodeID where the matching WorkDate is < some particular date? Maybe we can come up with a working subquery for you.

BTW Duane, you are absolutely correct DSum is available in SQL. I guess I assumed it wasn't because there never seemed to be a good time to use it. Always learning something on this site!!



Jeff Bridgham
bridgham@purdue.edu

 

[djmurphy58]

Duane-
I appreciate the trouble you went through to type up the SQL statement for it.

I copied your exactly, but I'm afraid I get the same result - nothing appears in my ToDateHours or ToDateQuantity field still.

Any other ideas???

Dennis

 

[JonFer]

In the subquery you posted, you are missing a space before the FROM but that is not the main problem.

I just looked at your SQL statement and your tables again. [WorkDate] is not in tblDailyBudgetIntermediateTable so you shouldn't be trying to find records there based on it! The common field is [DateID] but if that is an autonumber field (guessing), then you probably cannot just use that directly unless [WorkDate]'s are always entered in increasing order.

Also, are you trying to do a rolling sum for all cost codes or just one at a time? It would help if you posted what you are expecting in your result.

 

[djmurphy58]

Ok-
Here's what I am trying to do.

On any given day, our construction crews do work for multiple cost codes. For example, on 11/18/03, they worked on cost code 2030.05 - Demolish Concrete. On that day they did 100 SY (square yards) of concrete demolition. It took them 16 MH (man-hours) to do that. Then let's say on 11/19/03, they did 80 SY of cost code 2030.05 and it took them 12 MH. I want the following two columns in my query: ToDateHours and ToDate Quantity. So the ToDateHours would show up as 16 for the work on 11/18 and then 28 for the work on 11/18 and 11/19. Similarly, the ToDateQuantity would show up as 180 SY demolished through 11/19/03.

Does that make sense? Basically I'm looking to show the running sum for both hours and quantity. I had no problem doing this in my report (using RunningSum). But I need to create a graph to track the daily hours and the daily quantity - so I need to get the running sums into my query.

Does that make sense??

Dennis