Code:
SELECT Projects_tbl.[EASi-Project ID], Projects_tbl.[Station Name], Projects_tbl.[Status @EASi-AEP], Projects_tbl.W1E, Projects_tbl.W2E, Projects_tbl.W3E, Projects_tbl.W4E, Projects_tbl.W5E, Projects_tbl.W6E, Projects_tbl.W7E, Projects_tbl.W8E, Projects_tbl.[15E], Projects_tbl.W1A, Projects_tbl.W2A, Projects_tbl.W3A, Projects_tbl.W4A, Projects_tbl.W5A, Projects_tbl.W6A, Projects_tbl.W7A, Projects_tbl.W8A, Projects_tbl.[15A], [W1E]+[W2E]+[W3E]+[W4E]+[W5E]+[W6E]+[W7E]+[W8E]+([15E]*1.5) AS [Estimated Hrs], Projects_tbl.[Actual Comp], Projects_tbl.[Weekly Status Comments], [W1A]+[W2A]+[W3A]+[W4A]+[W5A]+[W6A]+[W7A]+[W8A]+([15A]*1.5) AS Actual, Now() AS Today, Projects_tbl.ID, Projects_tbl.[Requested Above Grade IFC], Projects_tbl.[EASi Design Start Date], IIf([Total Days]>5,([Requested Above Grade IFC]-[EASi Design Start Date])-[WeekEnds],[Total Days]) AS [Actual Days], [Requested Above Grade IFC]-[EASi Design Start Date] AS [Total Days], ([Week Ending]-[EASi Design Start Date]) AS Days_Done, ([W1E]/[Actual Days])*[Actual Days TD] AS W1E_HRSDAY, ([W2E]/[Actual Days])*[Actual Days TD] AS W2E_HRSDAY, ([W3E]/[Actual Days])*[Actual Days TD] AS W3E_HRSDAY, ([W4E]/[Total Days])*[Days_Done] AS W4E_HRSDAY, ([W5E]/[Total Days])*[Days_Done] AS W5E_HRSDAY, ([W6E]/[Total Days])*[Days_Done] AS W6E_HRSDAY, ([W7E]/[Total Days])*[Days_Done] AS W7E_HRSDAY, ([W8E]/[Total Days])*[Days_Done] AS W8E_HRSDAY, ([15E]/[Total Days])*[Days_Done] AS 15E_HRSDAY, ([W1E_Total]+[W2E_Total]+[W3E_Total]+[W4E_Total]+[W5E_Total]+[W6E_Total]+[W7E_Total]+[W8E_Total]+[15E_Total]) AS TOT_EST, IIf([TOT_EST]<=[ERN10PLUS] And [TOT_EST]>=[ERN10MIN],"Green",IIf([TOT_EST]<=[ERN20PLUS] And [TOT_EST]>=[ERN20MIN],"Yellow",IIf([TOT_EST]>[ERN20PLUS] Or [TOT_EST]<[ERN20MIN],"Red","No Data"))) AS TIME_STAT, IIf([Earned]>=[Actual],"Green",IIf([Earned]<[Actual] And [Earned]>[ACT10MIN],"Yellow",IIf([Earned]<[ACT10MIN],"Red","No Data"))) AS COST_STAT, Projects_tbl.QUAL_STAT, [Actual Comp]*[Estimated Hrs] AS Earned, [Actual]-([Actual]*0.15) AS ACT10MIN, ([Week Ending]-[EASi Design Start Date])/7 AS [Total Weeks TD], [Total Weeks TD]*2 AS [WeekEnds TD], IIf([Total Days TD]>5,([Today]-[EASi Design Start Date])-[WeekEnds TD],[Total Days TD]) AS [Actual Days TD], [Week Ending]-[EASi Design Start Date] AS [Total Days TD], IIf([W1E_HRSDAY]<[W1E],([W1E]/[Actual Days])*[Actual Days TD],[W1E]) AS W1E_Total, IIf([W2E_HRSDAY]<[W2E],([W2E]/[Actual Days])*[Actual Days TD],[W2E]) AS W2E_Total, IIf([W3E_HRSDAY]<[W3E],([W3E]/[Actual Days])*[Actual Days TD],[W3E]) AS W3E_Total, IIf([W4E_HRSDAY]<[W4E],([W4E]/[Actual Days])*[Actual Days TD],[W4E]) AS W4E_Total, IIf([W5E_HRSDAY]<[W5E],([W5E]/[Actual Days])*[Actual Days TD],[W5E]) AS W5E_Total, IIf([W6E_HRSDAY]<[W6E],([W6E]/[Actual Days])*[Actual Days TD],[W6E]) AS W6E_Total, IIf([W7E_HRSDAY]<[W7E],([W7E]/[Actual Days])*[Actual Days TD],[W7E]) AS W7E_Total, IIf([W8E_HRSDAY]<[W8E],([W8E]/[Actual Days])*[Actual Days TD],[W8E]) AS W8E_Total, IIf([15E_HRSDAY]<[W1E],([15E]/[Actual Days])*[Actual Days TD],[15E]) AS 15E_Total, IIf([TIME_STAT]="Red" Or [COST_STAT]="Red" Or [QUAL_STAT]="Red","Red",IIf([TIME_STAT]="Green" And [COST_STAT]="Green" And [QUAL_STAT]="Yellow","Yellow",IIf([TIME_STAT]="Yellow" And [COST_STAT]="Yellow" And [QUAL_STAT]="Green","Yellow",IIf([TIME_STAT]="Yellow" And [COST_STAT]="Green" And [QUAL_STAT]="Green","Yellow",IIf([TIME_STAT]="Green" And [COST_STAT]="Yellow" And [QUAL_STAT]="Green","Yellow",IIf([TIME_STAT]="Green" And [COST_STAT]="Green" And [QUAL_STAT]="Yellow","Yellow",IIf([TIME_STAT]="Yellow" And [COST_STAT]="Green" And [QUAL_STAT]="Yellow","Yellow",IIf([TIME_STAT]="Green" And [COST_STAT]="Yellow" And [QUAL_STAT]="Yellow","Yellow",IIf([TIME_STAT]="Green" And [COST_STAT]="Green" And [QUAL_STAT]="Green","Green","No Data"))))))))) AS OS, [Today]-Weekday([Today]) AS [Week Ending], [Earned]+([Earned]*0.25) AS ERN20PLUS, [Earned]-([Earned]*0.15) AS ERN10MIN, [Earned]-([Earned]*0.25) AS ERN20MIN, Projects_tbl.[AEP Station Lead]
FROM Projects_tbl
WHERE (((Projects_tbl.[Status @EASi-AEP]) In ("WIP","FC","INI")) AND ((Projects_tbl.[EASi Design Start Date]) Is Not Null));
I have been looking at this for a while and I just wanted to get some fresh eyes on it.