SQL too big ??

Joined
Jun 12, 2008
Messages
7
Reaction score
0
Hi here is the SQL, but it falls down , can anyone steeamline this down to work?

SELECT [Current Fleet].[A/C Family], [Current Fleet].[A/C Type], [Current Fleet].[A/C Model Series], [Current Fleet].[A/C Age], [Current Fleet].[Service Date], [Current Fleet].[Operator Name], [Current Fleet].[ECS Reference], [Current Fleet].[Number of Engines], [Current Fleet].[A/C Status], [Current Fleet].[Engine Family], [Current Fleet].[Engine Model Series], [Current Fleet].[Engine Model], [Current Fleet].[Total Flying Hours], [Current Fleet].[Last 12 Months Flying Hours], [Current Fleet].[Average Annual Flying Hours], [Current Fleet].[Operator World Region], Data_Input_Table.Accessory, Data_Input_Table.[CMP1 Hours], Data_Input_Table.[CMP2 Hours], Data_Input_Table.[CMP3 Hours], Data_Input_Table.[CMP4 Hours], Data_Input_Table.[Engine Overhaul], Data_Input_Table.[Non CMP Unit], Data_Input_Table.[Low Level Repair], Data_Input_Table.[Medium Level Repair], Data_Input_Table.[Major Repair], Data_Input_Table.OH, Data_Input_Table.MRO, [Current Fleet].[Arising Region], Data_Input_Table.OP_MTBUR, Data_Input_Table.[Qty_of_Accessory_ per_ Engine], acas_current_fleet.[Number of Engines]*[Qty_of_Accessory_ per_ Engine] AS TotalACCperaircraft, ACAS_AVG_FlyingHours2.[Collated AVG HRS], Nz([total flying hours],0+NZ([total flying hours])) AS [Total Hours], Nz([last 12 months flying hours],0+NZ([last 12 months flying hours])) AS [Balanced Average Hours], IIf([balanced average hours]=0,[collated avg hrs],[balanced average hours]) AS StdHRS, ([total hours]+([stdhrs]*1)) AS FH1, ([total hours]+([stdhrs]*2)) AS FH2, ([total hours]+([stdhrs]*3)) AS FH3, ([total hours]+([stdhrs]*4)) AS FH4, ([total hours]+([stdhrs]*5)) AS FH5, ([total hours]+([stdhrs]*6)) AS FH6, ([total hours]+([stdhrs]*7)) AS FH7, ([total hours]+([stdhrs]*8)) AS FH8, ([total hours]+([stdhrs]*9)) AS FH9, ([total hours]+([stdhrs]*10)) AS FH10, ([a/c age]+1) AS AGE1, ([a/c age]+2) AS AGE2, ([a/c age]+3) AS AGE3, ([a/c age]+4) AS AGE4, ([a/c age]+5) AS AGE5, ([a/c age]+6) AS AGE6, ([a/c age]+7) AS AGE7, ([a/c age]+8) AS AGE8, ([a/c age]+9) AS AGE9, ([a/c age]+10) AS AGE10, IIf([age1]>35,0,IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0))*[TotalACCperaircraft] AS [Arising YR1/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR1/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR1/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR1/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR1/4/3], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR2/1], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR2/2], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR2/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR2/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR3/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR3/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR3/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR3/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR3/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR4/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR4/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR4/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR4/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR4/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR5/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR5/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR5/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR5/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR5/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR6/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR6/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR6/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR6/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR6/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR7/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR7/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR7/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR7/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR7/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR8/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR8/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR8/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR8/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR8/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR9/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR9/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR9/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR9/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR9/4/3], IIf([fh1] Between [cmp1 hours] And [cmp2 hours],1,0)*[TotalACCperaircraft] AS [Arising YR10/1], IIf([fh1] Between [cmp2 hours] And [cmp3 hours],1,0)*[TotalACCperaircraft] AS [Arising YR10/2], IIf([fh1] Between [cmp3 hours] And [cmp4 hours],1,0)*[TotalACCperaircraft] AS [Arising YR10/3], IIf([fh1] Between [cmp4 hours] And ([cmp1 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/4], IIf([fh1] Between ([cmp1 hours]+[cmp4 hours]) And ([cmp2 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/1/2], IIf([fh1] Between ([cmp2 hours]+[cmp4 hours]) And ([cmp3 hours]+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/2/2], IIf([fh1] Between ([cmp3 hours]+[cmp4 hours]) And ([cmp4 hours]*2),1,0)*[TotalACCperaircraft] AS [Arising YR10/3/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/4/2], IIf([fh1] Between (([cmp4 hours]*2)+[cmp1 hours]) And (([cmp4 hours]*2)+[cmp2 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/1/3], IIf([fh1] Between (([cmp4 hours]*2)+[cmp2 hours]) And (([cmp4 hours]*2)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/2/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp2 hours]) And (([cmp4 hours]*3)+[cmp3 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/3/3], IIf([fh1] Between (([cmp4 hours]*3)+[cmp3 hours]) And (([cmp4 hours]*3)+[cmp4 hours]),1,0)*[TotalACCperaircraft] AS [Arising YR10/4/3], Data_Input_Table.Goodrich_MTBUR, Nz([op_MTBUR],0+Nz([op_mtbur])) AS OPMTBUR, IIf([opmtbur]=0,[goodrich_mtbur],[opmtbur]) AS AircraftMTBUR, IIf([aircraftmtbur] Between ([stdhrs]*1) And ([stdhrs]*2),1,0) AS MTBUR1, IIf([aircraftmtbur] Between ([stdhrs]*2) And ([stdhrs]*3),1,0) AS MTBUR2, IIf([aircraftmtbur] Between ([stdhrs]*3) And ([stdhrs]*4),1,0) AS MTBUR3, IIf([aircraftmtbur] Between ([stdhrs]*4) And ([stdhrs]*5),1,0) AS MTBUR4, IIf([aircraftmtbur] Between ([stdhrs]*5) And ([stdhrs]*6),1,0) AS MTBUR5, IIf([aircraftmtbur] Between ([stdhrs]*6) And ([stdhrs]*7),1,0) AS MTBUR6, IIf([aircraftmtbur] Between ([stdhrs]*7) And ([stdhrs]*8),1,0) AS MTBUR7, IIf([aircraftmtbur] Between ([stdhrs]*8) And ([stdhrs]*9),1,0) AS MTBUR8, IIf([aircraftmtbur] Between ([stdhrs]*9) And ([stdhrs]*10),1,0) AS MTBUR9, IIf([aircraftmtbur] Between ([stdhrs]*10) And ([stdhrs]*11),1,0) AS MTBUR10, IIf([cmp1 hours]=0,1,0) AS OHonly, [engine overhaul]*1 AS YR1ENGOH, [engine overhaul]*2 AS YR2ENGOH, [engine overhaul]*3 AS YR3ENGOH, [engine overhaul]*4 AS YR4ENGOH, IIf([fh1]>[yr1engoh]+([collated avg hrs]*2),0,IIf([fh1] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR1/1], IIf([fh1]>[yr2engoh]+([collated avg hrs]*2),0,IIf([fh1] Between [yr2engoh] And [yr3engoh],1,0))*[TotalACCperaircraft] AS [OHYR1/2], IIf([fh1]>[yr3engoh]+([collated avg hrs]*2),0,IIf([fh1] Between [yr3engoh] And [yr4engoh],1,0))*[TotalACCperaircraft] AS [OHYR1/3], IIf([fh1]>[yr4engoh]+([collated avg hrs]*2),0,IIf([fh1] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0))*[TotalACCperaircraft] AS [OHYR1/4], IIf([ohyr1/1]>=1,0,IIf([fh1]>[yr1engoh]+([collated avg hrs]*2),0,IIf([fh1] Between [yr1engoh] And [yr2engoh],1,0)))*[TotalACCperaircraft] AS [OHYR2/1], IIf([fh2] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR2/2], IIf([fh2] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR2/3], IIf([fh2] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR2/4], IIf([ohyr1/1]+[ohyr2/1]>=1,0,IIf([fh3] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR3/1], IIf([fh3] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR3/2], IIf([fh3] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR3/3], IIf([fh3] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR3/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]>=1,0,IIf([fh4] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR4/1], IIf([fh4] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR4/2], IIf([fh4] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR4/3], IIf([fh4] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR4/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]>=1,0,IIf([fh5] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR5/1], IIf([fh5] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR5/2], IIf([fh5] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR5/3], IIf([fh1] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR5/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]+[ohyr5/1]>=1,0,IIf([fh6] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR6/1], IIf([fh6] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR6/2], IIf([fh6] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR6/3], IIf([fh1] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR6/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]+[ohyr5/1]+[ohyr6/1]>=1,0,IIf([fh7] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR7/1], IIf([fh7] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR7/2], IIf([fh7] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR7/3], IIf([fh7] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR7/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]+[ohyr5/1]+[ohyr6/1]+[ohyr7/1]>=1,0,IIf([fh8] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR8/1], IIf([fh8] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR8/2], IIf([fh8] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR8/3], IIf([fh8] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR8/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]+[ohyr5/1]+[ohyr6/1]+[ohyr7/1]+[ohyr8/1]>=1,0,IIf([fh9] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR9/1], IIf([fh9] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR9/2], IIf([fh9] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR9/3], IIf([fh9] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR9/4], IIf([ohyr1/1]+[ohyr2/1]+[ohyr3/1]+[ohyr4/1]+[ohyr5/1]+[ohyr6/1]+[ohyr7/1]+[ohyr8/1]+[ohyr9/1]>=1,0,IIf([fh10] Between [yr1engoh] And [yr2engoh],1,0))*[TotalACCperaircraft] AS [OHYR10/1], IIf([fh10] Between [yr2engoh] And [yr3engoh],1,0)*[TotalACCperaircraft] AS [OHYR10/2], IIf([fh10] Between [yr3engoh] And [yr4engoh],1,0)*[TotalACCperaircraft] AS [OHYR10/3], IIf([fh10] Between [yr4engoh] And ([yr4engoh]+[yr1engoh]),1,0)*[TotalACCperaircraft] AS [OHYR10/4]
FROM ACAS_AVG_FlyingHours2 INNER JOIN ([Current Fleet] INNER JOIN Data_Input_Table ON ([Current Fleet].[ECS Reference] = Data_Input_Table.[ECS Reference]) AND ([Current Fleet].[Operator Name] = Data_Input_Table.[Operator Name])) ON ACAS_AVG_FlyingHours2.[ECS Reference] = Data_Input_Table.[ECS Reference]
WITH OWNERACCESS OPTION;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top