Results 1 to 3 of 3
  1. #1
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39

    Query to complex....

    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:

    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.

    Thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Just out of curiosity, how many tables in your database?
    Can you post a jpg of your tables and relationships?

  3. #3
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    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.

    Click image for larger version. 

Name:	relate.jpg 
Views:	7 
Size:	67.1 KB 
ID:	11648

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query too Complex
    By Bob Blooms in forum Access
    Replies: 9
    Last Post: 09-17-2012, 08:23 AM
  2. Complex Query.... I think?
    By Dannat in forum Queries
    Replies: 6
    Last Post: 02-14-2012, 01:22 PM
  3. Query is too Complex
    By ihealy1 in forum Queries
    Replies: 2
    Last Post: 11-18-2011, 04:46 PM
  4. Complex query to me anyway
    By AndycompanyZ in forum Queries
    Replies: 3
    Last Post: 06-28-2011, 03:08 PM
  5. Help with complex query (for me at least)
    By GrindEspresso in forum Queries
    Replies: 5
    Last Post: 01-26-2011, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums