simple query question

[devagupt]

I have a query with many fields populated from a form with time entered in mins. I need to convert them into time in hrs.how would i do it? Below is the code for the query. I need to convert all the fields from the 4th field onwards into time in hrs.

Thanks
SELECT Format([Date01],'mm/dd') AS day, tblDowntimeTable.Shift AS Expr2, tblDowntimeTable.Line AS Expr3, tblDowntimeTable.[Defective Product], tblDowntimeTable.[CH/LD Change], tblDowntimeTable.[Fine Tuning], tblDowntimeTable.[Label Machine], tblDowntimeTable.[Product Change], tblDowntimeTable.[Roll count], tblDowntimeTable.Rotation, tblDowntimeTable.[Comp Control], tblDowntimeTable.[Start up], tblDowntimeTable.Equipment, tblDowntimeTable.[Long Breaks], tblDowntimeTable.[Re-ins/Re-work], tblDowntimeTable.Dabrico, tblDowntimeTable.[Morning meet], tblDowntimeTable.[Make boxes], tblDowntimeTable.[Bad components], tblDowntimeTable.Other
FROM tblDowntimeTable
GROUP BY Format([Date01],'mm/dd'), tblDowntimeTable.Shift, tblDowntimeTable.Line, tblDowntimeTable.[Defective Product], tblDowntimeTable.[CH/LD Change], tblDowntimeTable.[Fine Tuning], tblDowntimeTable.[Label Machine], tblDowntimeTable.[Product Change], tblDowntimeTable.[Roll count], tblDowntimeTable.Rotation, tblDowntimeTable.[Comp Control], tblDowntimeTable.[Start up], tblDowntimeTable.Equipment, tblDowntimeTable.[Long Breaks], tblDowntimeTable.[Re-ins/Re-work], tblDowntimeTable.Dabrico, tblDowntimeTable.[Morning meet], tblDowntimeTable.[Make boxes], tblDowntimeTable.[Bad components], tblDowntimeTable.Other
HAVING (((tblDowntimeTable.Shift)=1))
ORDER BY Format([Date01],'mm/dd');

 

[bborissov]

First, when you add ALL fields in GROUP BY there is NO NEED of GROUP BY AT ALL.
Also when you use fields from table you select from use WHERE not HAVING. HAVING must be used ONLY if you want the result to be filtered by some new created field, like SUM(), AVG() or other.

About how to convert minutes to hours:
Just divide all minute field by 60:
I don't know what fields in that query are minutes, but check this:

SELECT Format([Date01],'mm/dd') AS day,
       tblDowntimeTable.Shift AS Expr2,
       tblDowntimeTable.Line AS Expr3,
       tblDowntimeTable.[Defective Product],
--- Convert [CH/LD Change] field from minutes to hours
       tblDowntimeTable.[CH/LD Change]/60*1.0 AS [CH/LD Change], 
       tblDowntimeTable.[Fine Tuning],
       tblDowntimeTable.[Label Machine],
       tblDowntimeTable.[Product Change],
       tblDowntimeTable.[Roll count],
       tblDowntimeTable.Rotation,
       tblDowntimeTable.[Comp Control],
       tblDowntimeTable.[Start up],
       tblDowntimeTable.Equipment,
       tblDowntimeTable.[Long Breaks],
       tblDowntimeTable.[Re-ins/Re-work],
       tblDowntimeTable.Dabrico,
       tblDowntimeTable.[Morning meet],
       tblDowntimeTable.[Make boxes],
       tblDowntimeTable.[Bad components],
      tblDowntimeTable.Other
FROM tblDowntimeTable
WHERE tblDowntimeTable.Shift=1
ORDER BY Format([Date01],'mm/dd');

Also please when you post code portion round it with [ code ] [ /code ] tags (w/o spaces) to make it more readable.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP