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

 

[dhookom]

Dennis,
You still haven't told us how CostCodeQuantity is attached/related to any date. There is a DateID but it doesn't help us unless we understand relationships. Also, your earlier posts had nothing about ToDayQuantity.

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

 

[djmurphy58]

JonFer
Yes, I want this done for each cost code. But right now I have my query setup such that the user will enter the cost code he wants the information for. For instance, if the user enters cost code 2030.05, the query would spit back the data just for that cost code. The data would look something like this:

Date Hours Quantity TDHours TDQuantity
11/17 8 40 8 40
11/18 12 60 20 100
11/19 12 60 32 160
11/20 10 70 42 230
11/21 14 90 56 320

Does that make more sense???

Dennis

 

[djmurphy58]

JonFer and Dhookom

I apologize if I haven't given you enough info to go on. Remember I am a novice user. I'll email both of you an excel file if you want showing you my relationships for the particular query in question. If you want me to, give me your email address.

Again, thank you for your patience and your help.

Dennis

 

[JonFer]

Now we're getting somewhere. Seeing the desired results helps a lot. Assuming [DateID] is just an Autonumber field, one thing you might want to change in your table structure is to have the [WorkDate] instead of [DateID] in tblDailyBudgetIntermediateTable. I don't see any benefit of having the ID instead of the actual date unless the [WorkPerformed] field is also a date (but my guess is that it is a Yes/No) and [DateID] represents the combination of the two dates.

This would simplify the queries a LOT. To do it, add the [WorkDate] field to the table and then run an update query using both tables (linking on [DateID]) where you update the new field to [tblDailyBudget].[WorkDate]

 

[djmurphy58]

JonFer
Can I please email you a copy of my relationships? What's your email address?

If possible, I'd like to avoid having to change my table structure - I already have a bunch of data in my DB along with a bunch of forms and reports already created.

Thanks in advance
Dennis

 

[djmurphy58]

BTW - Work performed is actually just a memo field for the user to type in a description of the work that was performed that day.

Thanks again - your ongoing help is tremendously appreciated.

Dennis

 

[JonFer]

Dennis,

No need to send me the file. From what you said, you have one record in tblDailyBudget for each job that was done on a day so you can have multiple entries for a specific date, right? DateID + CostCodeID should be the primary key in tblDailyBudgetIntermediateTable. You don't need to enter the same CostCode twice for the same job, right?

This is what I would do. Write a query which joins tblDailyBudget with tblDailyBudgetIntermediateTable and tblCostCodes. Join on DateID between the first two and CostCodeID for the second two. Include the WorkDate, CostCodeNumber, Quantity, and Hours. Group by WorkDate and CostCodeNumber and sum the Quantity and Hours. Save this as qryWorkDateCostCodeTotal.

Now write a new query and bring in qryWorkDateCostCodeTotal twice. Make a join line ONLY on CostCodeNumber. Alias one query as &quot;Daily&quot; and the other as &quot;ToDate&quot; or whatever you choose. Add a criteria for the [WorkDate] field from the &quot;ToDate&quot; table of: &quot;<= [Daily].[WorkDate]&quot;

Select Daily.WorkDate, Daily.CostCodeNumber, Daily.Hours, Daily.Quantity, 
       Sum(ToDate.Quantity) as QtyTD, Sum(ToDate.Hours) as HoursTD
From qryWorkDateCostCodeTotal as Daily Inner Join
     qryWorkDateCostCodeTotal as ToDate On Daily.CostCodeNumber=ToDate.CostCodeNumber
Where ToDate.WorkDate <= Daily.WorkDate AND
      Daily.WorkDate Between [Enter Start Date:] And [Enter End Date:] AND
      Daily.CostCodeNumber = [Enter Cost Code:]
Group By Daily.WorkDate, Daily.CostCodeNumber, Daily.Hours, Daily.Quantity
[\code]

Understanding how this works will be very helpful to you.  You'll probably find that qryWorkDateCostCodeTotal is useful as a source for other queries against your data.

 

[djmurphy58]

JonFer-
Thank you very much for your response. I understood everything until I got to:

Now write a new query and bring in qryWorkDateCostCodeTotal twice. Make a join line ONLY on CostCodeNumber. Alias one query as &quot;Daily&quot; and the other as &quot;ToDate&quot; or whatever you choose. Add a criteria for the [WorkDate] field from the &quot;ToDate&quot; table of: &quot;<= [Daily].[WorkDate]&quot;

How do I write this query using the query wizard. As I am very much a beginnner, I do not know SQL to well.

Thank you,
Dennis

 

[djmurphy58]

In other words -
1. What do you mean when you say &quot;bring in qryWorkDateCostCodeTotal twice&quot;?

2. What do you mean make a join line?

3. How do I alias a query?

Dennis

 

[djmurphy58]

Ok JonFer -

I guess I spoke to soon. I figured out what you wanted me to do. I copied and pasted your SQL into a new query and it almost works I think.

When I run it, several parameter dialog boxes come up asking me to enter the following:

Daily.Hours
Daily.Quantity
ToDate.Quantity
ToDate.Hours

Of course it also asks me to enter start date, end date, and cost code - but that is what it's supposed to do.

Any idea why it's asking me to enter the other 4 parameters?

Dennis

 

[djmurphy58]

Ok JonFer I'll just shut up now............

You ARE THE MAN!!!!! I figured it out (well you did really). I had to type &quot;SumOf&quot; in front of the field names for CostCodeHours and CostCodeQuantity. Now it finally works.

Once again, you are the man! I wish you could see the giant smile of satisfaction on my face right now!

Thanks,
Dennis

 

[djmurphy58]

I take that back.......it's not quite working. I'll screw around with it and try to figure it out.

Dennis

 

[djmurphy58]

OK, I swear this will be the last question JonFer;

I need to add two fields to the query:

DailyManHourFactor = DailyHours/DailyQuantity
ToDateManHourFactor = TDHours/TDQuantity

Dennis

 

[JonFer]

Add these fields:

Sum(ToDate.SumOfHours)/Sum(ToDate.SumOfQuantity) as ToDateFactor
Daily.SumOfHours/Daily.SumOfQuantity as DailyFactor

You'll have to add &quot;Daily.SumOfHours/Daily.SumOfQuantity&quot; in the Group By, too

When you said it wasn't working, were the numbers wrong or did you want the additional fields?

 

[djmurphy58]

Actually, everything is working great now....the numbers are correct. Now the whole point of doing this whole query was so that I could plot graphs (called Trend Charts) showing the cost code's budget manhour factor, it's daily manhour factor, and its to date manhour factor over time all on the same graph. The manhour factor is defined as hours/quantity. Now that I have all the data, I can't figure out how to get the damn chart wizard to give me the correct chart. When I run the chart wizard, I input my three MH factors and then view the sample chart before running the final chart, the sample chart looks exactly as it should. So I go ahead and run the report with the chart - but I was surprised to see that the chart is not correct!!! It only shows the three man-hour fcators for one workdate - the latest date on the report. It does not include the previous 6 workdates or so.

What's the deal??

D

 

[JonFer]

When you provide the input range for the chart, include the WorkDate and the 3 factors which should be next to each other. Also include the row with the column names so you don't have to add that.

Category values should be the date column and you should have 3 &quot;Series&quot;. The cells for a series will be those for one of the factors.

If you can't get it to work, send me an Excel file with sample data: jfernandes27@(nospam)yahoo.com. Remove the (nospam) first.

 

[djmurphy58]

JonFer
I got it to work now. The report works fine with the correct chart except for the following:

1. The Microsoft Jet SQL engine (or something like that) does not recognize the workdate parameters I put in. So I had to change my work date criteria from &quot;Between [Enter Start Date] And [Enter End Date]&quot; to &quot;Between #11/3/03# And #11/19/03#&quot;. Is there a way around this so that I can still enter start dates and end dates? - rather than &quot;hard-wiring&quot; the dates I want into the query?

2. The [Enter Cost Code] criteria works - except it pops up three times. So after entering the same cost code three times, the correct report and chart DOES come up. But why does the criteria show up 3 times?

Thank you,
Dennis

 

[JonFer]

I am not sure why this happens but I have seen it before. This is just a guess but Access may be trying to optimize the query and sees that you are filtering on CostCodeNumber at the end and so it is asking for the number for the first queries to avoid dealing with the rows for the other cost code numbers. You could put the CostCodeNumber criteria on the first query to test this. The only problem is if you want this to be flexible to run for all cost codes. If so, you could do this:

CostCodeNumber Like [Enter Cost Code:]

as the criteria on the first query. Then you enter &quot;*&quot; for all cost codes or a specific one if you want.

You might want to create a form with fields for the StartDate (use a ShortDate input mask), EndDate (same), and CostCodeNumber (drop-down reading your table).

Then the query can reference the form fields like this:

WorkDate Between Forms!FormName.StartDate and Forms!FormName.EndDate
CostCodeNumber Like Forms!FormName.CostCodeNumber

Creating a simple form like this gives you more control over the input. You can also add a button to run the report.

 

[djmurphy58]

Sounds good JonFer. But what do I use as the control source for the two fields (StartDate and EndDate) in my form??

Dennis

 

[JonFer]

Nothing - leave them all Unbound. Make sure you are not binding the CostCodeNumber field either. The source for the drop-down list is the cost code table but the value selected should not be bound to it. The query/report will work as long as the form is open and the fields have values.

If you want to know what was last run, you'll have to add a table with the three fields in it and then use those as the control sources. Make sure the form is set to Allow Edits but not deletes or adds. If the form will be used by multiple people, I would keep the fields Unbound and NOT use an internal table.