View Single Post
  #7 (permalink)  
Old September 30th, 2005, 11:27 AM
Dharam80 Dharam80 is offline
Authorized User
 
Join Date: Aug 2005
Location: , , USA.
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Dharam80
Default

its too long but here you go: SELECT DISTINCTROW [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], (([Total Hours]-[Sum Of Total Down time_Total Hours])/[Total Hours]) AS Utilization, [Total LBS Made]/[Production Total Hours] AS [Total LBS/Hr], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], IIf(IsNull([Sum Of Production D/T Hours]/[Total Hours]),0,[Sum Of Production D/T Hours]/[Total Hours]) AS [Production D/T], IIf(IsNull([Sum Of Maintenance hours]/[Total Hours]),0,[Sum Of Maintenance hours]/[Total Hours]) AS [Maintenance D/T], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], Sum((IIf(IsNull([Maintenance hours]),0,[Maintenance hours]))) AS [Sum Of Maintenance hours], Sum((IIf(IsNull([Production D/T Hours]),0,[Production D/T Hours]))) AS [Sum Of Production D/T Hours], Sum(Nz([Total Down time]![Total Hours],0)) AS [Sum Of Total Down time_Total Hours], [Production Hours Total].[Production Total Hours], Nz([Production Total Hours],0)+Nz([Sum Of Total Down time_Total Hours],0) AS [Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO
FROM ([Production Hours Total] INNER JOIN ([Daily Production Summary Totals] RIGHT JOIN ((([Extrusion Report Query] LEFT JOIN Maintenance ON ([Extrusion Report Query].DownTimeList = Maintenance.DownTimeList) AND ([Extrusion Report Query].[Operator Id] = Maintenance.[Operator Id]) AND ([Extrusion Report Query].[Shift#] = Maintenance.[Shift#]) AND ([Extrusion Report Query].[Line#] = Maintenance.[Line#]) AND ([Extrusion Report Query].Date = Maintenance.Date) AND ([Extrusion Report Query].[Total Hours] = Maintenance.[Maintenance hours])) LEFT JOIN [Production Downtime] ON ([Extrusion Report Query].[Shift#] = [Production Downtime].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Production Downtime].[Line#]) AND ([Extrusion Report Query].Date = [Production Downtime].Date) AND ([Extrusion Report Query].[Total Hours] = [Production Downtime].[Production D/T Hours]) AND ([Extrusion Report Query].DownTimeList = [Production Downtime].DownTimeList) AND ([Extrusion Report Query].[Operator Id] = [Production Downtime].[Operator Id])) LEFT JOIN [Total Down time] ON ([Extrusion Report Query].[Shift#] = [Total Down time].[Shift#]) AND ([Extrusion Report Query].[Line#] = [Total Down time].[Line#]) AND ([Extrusion Report Query].Date = [Total Down time].Date) AND ([Extrusion Report Query].[Total Hours] = [Total Down time].[Total Hours]) AND ([Extrusion Report Query].[Operator Id] = [Total Down time].[Operator Id]) AND ([Extrusion Report Query].DownTimeList = [Total Down time].DownTimeList)) ON ([Daily Production Summary Totals].Date = [Extrusion Report Query].Date) AND ([Daily Production Summary Totals].[Line#] = [Extrusion Report Query].[Line#]) AND ([Daily Production Summary Totals].[Shift#] = [Extrusion Report Query].[Shift#])) ON ([Production Hours Total].[Shift#] = [Extrusion Report Query].[Shift#]) AND ([Production Hours Total].[Line#] = [Extrusion Report Query].[Line#]) AND ([Production Hours Total].Date = [Extrusion Report Query].Date)) INNER JOIN Calendar ON [Production Hours Total].Date = Calendar.Date
GROUP BY [Extrusion Report Query].Date, [Extrusion Report Query].[Line#], [Extrusion Report Query].[Shift#], [Daily Production Summary Totals].[First Pass Yield], [Daily Production Summary Totals].[Color %], [Daily Production Summary Totals].[CACO %], [Daily Production Summary Totals].[Good LBS], [Daily Production Summary Totals].[Scrap LBS], [Daily Production Summary Totals].[Total LBS Made], [Production Hours Total].[Production Total Hours], Calendar.Week, Calendar.Month, [Daily Production Summary Totals].[Nat CLR Prime], [Daily Production Summary Totals].Regrind, [Daily Production Summary Totals].[Color LBS], [Daily Production Summary Totals].Prime, [Daily Production Summary Totals].CACO;


Dharmesh Bhavsar
Reply With Quote