Query to complex....

[zero3ree]

I have been compiling a query for sometime to create a project summary page. It is working but one user was getting the "Query to complex" error. I thought it was the fact it was an xp machine until today. I added altered the week ending expression to go back to last week and also had to change a few others to do its math off week ending instead of "today()". When I did this i now am getting the "Query to complex" error on a fully capable Win 7 machine. I searched around some and couldn't get a definitive answer on why this is happening other than I have to many things going on in my sql, so I came to you guys because you never let me down.



I was wondering if I put my sql on here someone could look at what I can do to condense it, if that is the issue. I will say that the naming conventions are a little crazy but I had to name these things in such a way that it helped me figure out how it all worked as it was being requested. I just wanted to get that out there. Everything was working just fine until I tried to change the Week ending.

Here it is:

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.

Thanks in advance.

[orange]

Just out of curiosity, how many tables in your database?
Can you post a jpg of your tables and relationships?

[zero3ree]

There are many tables in the database. For this query all information is coming from one unfortunately. This is because it was given as a spreadsheet and I couldn't convince the powers that be to let me break it up.

I am wondering if I need to create a few queries for the data and then combine them into one query that feeds the end report. This would make the end query less complex.



Again the allotted time allowed to get this up and running forced me to be unconventional in design. I have done what I needed to do to get this DB up and running with minimal overhead.

I really just need to make the error go away the simplest way possible. I tried erasing as many expressions out of the query that I could but it still gives me the error.