DSUM Querry

[jackjfarrell]

I am trying to create a Dsum for a query. Right now I have 4 different Workorders and several different types of workers who have labor hours associated with those workorders. I want to sum the amount of labor hours employee type "Project_Crew" has. Each time I try it gives the the sum for all workorders not just the row I want. If the picture attached there is only 1 employee with 8 hours of time for workorder 123 but it is showing in the right column that each workorder has 8 hrs when it should show that only 123 has 8 hrs. What am I doing wrong. Thanks





This is the expression i entered into the Query.


ProjectCrewST: DSum("[HoursWorked]","DSUM","[Craft]='Project_Crew' AND [Workorder]=[Workorder]")

[ranman256]

don't use DSUM in a query. The QUERY is the dsum.

[CJ_London]

I agree with ranman, but perhaps you can fix your dsum by fixing the criteria

ProjectCrewST: DSum("[HoursWorked]","DSUM","[Craft]='Project_Crew' AND [Workorder]=" & [Workorder])

[John_G]

I don't know if this will work or not, but your DSUM criteria is wrong:

In this: ProjectCrewST: DSum("[HoursWorked]","DSUM","[Craft]='Project_Crew' AND [Workorder]=[Workorder]"), the bolded part is inside the quotes, so it's like saying 1 = 1, which of course is always true, so essentially your DSUM criteria is ","[Craft]='Project_Crew' " and that's all.

You could try this: "[Craft]='Project_Crew' AND [Workorder] = " & [Workorder] as the DSUM criteria, but as I say, I don't know if it will work.

(Sorry Ajax - I didn't see your post!)

[jackjfarrell]

Thanks for your help. How would I do it without a DSUM? What would the expression look like.

[John_G]

You could try a calculated field in the query:

ProjectCrew:iif([Craft]='Project_Crew', [HoursWorked], 0)

and sum it as you did for the other two query fields.