Group By Question 1

[ribhead]

I'm trying to get my teams set up hours and production hours for certain jobs called project jobs. I'm trying to get them broken down by the Date entered the Dept. then the work center. I am only getting the Hours of production for one run. If two shifts run the same job on the same day I'm only getting the hours of production for one shift to show up in my list box. Below is the code I'm trying to work with. Any help would be great!


strSQL = "SELECT [Date entered],[Department],[Machine area],[Part number] ,Sum([Hrs running prod] + [Hrs s/u time]) as [Total Hours Worked]" & _
" FROM QueryProjects" & _
" GROUP BY [Part Number],[Date entered],[Department],[Machine area]" & _
" ORDER BY [Date entered]DESC;"

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Add the shift in the GROUP BY clause

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PH I got the same result. This is what I have.

SELECT [Date entered],[Shift],[Department],[Machine area],[Part number] ,Sum([Hrs running prod] + [Hrs s/u time]) as [Total Hours Worked]
FROM QueryProjects
GROUP BY [Date entered],[Shift],[Part number],[Department],[Machine area]
ORDER BY [Date entered]DESC;

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

And this ?
SELECT [Date entered],[Department],[Machine area],[Part number],[Shift],Sum([Hrs running prod] + [Hrs s/u time]) as [Total Hours Worked]
FROM QueryProjects
GROUP BY [Date entered],[Part number],[Department],[Machine area],[Shift]
ORDER BY [Date entered]DESC;

If you still got the same (supposed wrong) result, feel free to post the QueryProjects SQL code, input data samples and expected result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

Here is the Project Query SQL code:
SELECT tblprod.Counter, tblprod.Shift, tblprod.[Date entered], tblprod.[Part number], tblprod.Department, tblprod.[Machine area], tblprod.[Hrs running prod], tblprod.[Hrs s/u time]
FROM tblprod
WHERE (((tblprod.[Date entered]) Between Now() And Now()-90) AND ((tblprod.[Part number]) Like "*Proj-*"))
ORDER BY tblprod.[Date entered] DESC , tblprod.[Time entered] DESC;

Here is a couple lines of data with entries.

Counter Shift Date entered Part number Department Machine area Hrs running prod Hrs s/u time
619 2nd 1/4/2005 PROJ-7160137 121 LC30 Okuma 8
609 1st 1/4/2005 PROJ-7160137 121 LC30 Okuma 3 5



Here is my result:

Date entered Department Machine area Part number Shift Total Hours Worked
1/3/2005 121 LC30 Okuma PROJ-7160137 1st

Kind of hard to tell by this but the numbers 8 3 and 5 are hours worked and hours setting up.

What I need is something like this:

Date entered Department Machine area Part number Total Hours Worked
1/4/2005 121 LC30 Okuma PROJ-7160137 16

Where the hours worked for that day add up to 16 (8,3,5) If I don't have to have shift then that would be great!

Hope this helps. It looks a bit cluttered.




Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[ribhead]

Ignore the middle date it is supposed to be 1/4/2005

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

And this ?
strSQL = "SELECT [Date entered],[Department],[Machine area],[Part number] ,Sum(Nz([Hrs running prod],0) + Nz([Hrs s/u time],0)) as [Total Hours Worked]" & _
" FROM QueryProjects" & _
" GROUP BY [Part Number],[Date entered],[Department],[Machine area]" & _
" ORDER BY [Date entered] DESC;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PH,
Yup this works Great! Thanks! Why do I need the NZ and the ,0 for the sum?

If I may ask.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

In fact I think this is only necessary for [Hrs s/u time] that can be null and then will nullify the addition.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PH,

Thanks for the info. Obviously I wasn't aware of it. So if I'm going to be adding values that could possibly be null should I be setting the default value to zero in my table? Zero is different than Null isn't it?

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Zero is different than Null isn't it
Yes, Null is an UNKNOWN value.
For example, the msgbox never display in the following code:
If Null = Null Then
MsgBox "Can't happen"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244