Query is too complex

[seocavaz]

I have a database that was working great until I added a calculation and now, I cant run a bunch of my queries without getting this error. I have 10 tables and none of them are very big. I have 13 queries and they kind of stack on top of each other, however none of them seem like they would be too complex. After I started receiving this error I went through and streamlined as many of them as I could. Simplified the process as much as possible. I also split the database up where there was the back end with the tables and the majority of the queries. Then I had a front end just link to the tables and queries, to run a few more queries and have all of my forms, but it didn't change he results at all.



What else can I do to alleviate this problem? I don't know how to post the entire SQL for the database. I can paste the stacked queries if that helps though.

I start with this query:
Qry_BaselineScoring

SELECT DISTINCT tbl_characteristic_data.id, tbl_characteristic_data.NumberLegs, tbl_characteristic_data.BracingLong, tbl_characteristic_data.BracingTran, tbl_characteristic_data.Heritage, tbl_characteristic_data.InstallDate, tbl_characteristic_data.Grouting, ([Tbl_BraceScoringlong].[BraceScore]*6) AS BracingScorelong, ([Tbl_BraceScoringTran].[BraceScore]*6) AS BracingScoreTran, (IIf([BracingScorelong]>[BracingScoreTran],[BracingScorelong],[BracingScoreTran])) AS maxbracescore, (IIf([Heritage]="Chevron U.S.A. Inc.",IIf([InstallDate]<#1/1/1965#,10,IIf([installdate] Between #1/1/1965# And #1/1/1970#,6,IIf([installdate] Between #1/1/1971# And #12/31/1979#,4,IIf([InstallDate]>#12/31/1979#,1)))),IIf([InstallDate]<#12/31/1965#,10,IIf([installdate] Between #1/1/1965# And #1/1/1970#,10,IIf([installdate] Between #1/1/1971# And #12/31/1979#,6,IIf([InstallDate]>#12/31/1979#,4))))))*8 AS VintageHeritage, IIf([Grouting]=0,10,0)*2 AS GroutScore, [vintageheritage]+[maxbracescore]+[groutscore] AS LOF, tbl_characteristic_data.[Dont Include]
 FROM (tbl_characteristic_data LEFT JOIN Tbl_BraceScoringTran ON (tbl_characteristic_data.numberlegs = Tbl_BraceScoringTran.numberlegs) AND (tbl_characteristic_data.BracingTran = Tbl_BraceScoringTran.BracingTran)) INNER JOIN Tbl_BraceScoringLong ON (tbl_characteristic_data.NumberLegs = Tbl_BraceScoringLong.NumberLegs) AND (tbl_characteristic_data.BracingLong = Tbl_BraceScoringLong.BracingTran)
 WHERE (((tbl_characteristic_data.[Dont Include])=False) AND ((tbl_characteristic_data.RemovalDate) Is Null));

the goal on that one is to "score" the platform based on age, bracing etc. the [LOF] is the result of that, that carries on to the next one.
Then I use the results to do this query:
Qry_RobustGrade

SELECT DISTINCT Qry_BaselineScoring.id, Qry_BaselineScoring.LOF, Tbl_RobustGrade.Grade, IIf([Grade]="A",[Grade],IIf([Qry_BaselineScoring].[InstallDate]<23377 And ([Tbl_Characteristic_Data].[NumberLegs]<3),IIf([grade]="E","D",IIf([Grade]="D","C",IIf([Grade]="C","B",IIf([Grade]="B","A")))),[Grade])) AS vintoverride, IIf([Tbl_Characteristic_Data].[SignificantDamage],IIf([grade]="E","D",IIf([Grade]="D","C",IIf([Grade]="C","B",IIf([Grade]="B","A")))),[vintoverride]) AS FinalLOF, Tbl_Characteristic_Data.SignificantDamage
 FROM Tbl_RobustGrade, Qry_BaselineScoring INNER JOIN Tbl_Characteristic_Data ON Qry_BaselineScoring.id = Tbl_Characteristic_Data.ID
 WHERE (((Qry_BaselineScoring.LOF) Between [ubscore] And [lbscore]));

from here I give that score (LOF) a letter grade based on a look up table. that grade carries on to the next query to determine (based off another look up) what the [RSR] should be:
Qry_RSRCalc

 SELECT Qry_RobustGrade.id, Qry_RobustGrade.FinalLOF, Tbl_FPHJEDdata.WIDRSR, Tbl_iSIMSRSR.RSRKnown, Tbl_POFRanges.RSRub, Tbl_POFRanges.RSRlb, Round(IIf([RSRlb]<[WIDRSR] And [WIDRSR]<[RSRub],([WIDRSR]+[RSRub])/2,IIf([WIDRSR]>[RSRub],[RSRub],IIf([WIDRSR]<[RSRlb],[RSRlb]))),2) AS RSRCalc
 FROM ((Qry_RobustGrade INNER JOIN Tbl_FPHJEDdata ON Qry_RobustGrade.id = Tbl_FPHJEDdata.ID) INNER JOIN Tbl_iSIMSRSR ON Qry_RobustGrade.id = Tbl_iSIMSRSR.ID) INNER JOIN Tbl_POFRanges ON Qry_RobustGrade.FinalLOF = Tbl_POFRanges.QualLOF;

Then I have some data tables that are from some curves an engineer has done. It is hard data, and I do a linear equation to give me where at on the graph it would hit basically. It produces what we call [SHPOF] and that is the main goal:
Qry_SHPOF

SELECT DISTINCT Tbl_Characteristic_Data.ID, Round(([CalcRP1]+[CalcRP2]+[CalcRP3]+[CalcRP4]+[CalcRP5]+[CalcRP6]+[CalcRP7]+[CalcRP8]+[CalcRP9]+[CalcRP10]+[CalcRP11]+[CalcRP12]+[CalcRP13]),0) AS sHPOF, (Switch([SHPOF]<21,1,[SHPOF]<101,2,[SHPOF]<501,3,[SHPOF]<1001,4,[SHPOF]<2501,5,[SHPOF]<10001,6)) AS Likelihood, Switch([qry_rsrcalc]![rsrknown] Is Not Null,[qry_rsrcalc]![rsrknown],[Qry_RSRCalc]![RSRCalc] Is Not Null,[Qry_RSRCalc]![RSRCalc]) AS RSR, (IIf([rsr]<=[Tbl_sHJEDdata].[Y1],10,0)) AS CalcRP1, IIf([rsr]<=[Tbl_sHJEDdata].[Y2] And [rsr]>[Tbl_sHJEDdata].[y1],10^(Log([Tbl_sHJEDdata].[x1])/Log(10)+(Log([Tbl_sHJEDdata].[x2])/Log(10)-Log([Tbl_sHJEDdata].[x1])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y1])/([Tbl_sHJEDdata].[y2]-[Tbl_sHJEDdata].[y1])),0) AS CalcRP2, IIf([rsr]<=[Tbl_sHJEDdata].[Y3] And [rsr]>=[Tbl_sHJEDdata].[y2],10^(Log([Tbl_sHJEDdata].[x2])/Log(10)+(Log([Tbl_sHJEDdata].[x3])/Log(10)-Log([Tbl_sHJEDdata].[x2])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y2])/([Tbl_sHJEDdata].[y3]-[Tbl_sHJEDdata].[y2])),0) AS CalcRP3, IIf([rsr]<=[Tbl_sHJEDdata].[Y4] And [rsr]>=[Tbl_sHJEDdata].[y3],10^(Log([Tbl_sHJEDdata].[x3])/Log(10)+(Log([Tbl_sHJEDdata].[x4])/Log(10)-Log([Tbl_sHJEDdata].[x3])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y3])/([Tbl_sHJEDdata].[y4]-[Tbl_sHJEDdata].[y3])),0) AS CalcRP4, IIf([rsr]<=[Tbl_sHJEDdata].[Y5] And [rsr]>=[Tbl_sHJEDdata].[y4],10^(Log([Tbl_sHJEDdata].[x4])/Log(10)+(Log([Tbl_sHJEDdata].[x5])/Log(10)-Log([Tbl_sHJEDdata].[x4])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y4])/([Tbl_sHJEDdata].[y5]-[Tbl_sHJEDdata].[y4])),0) AS CalcRP5, IIf([rsr]<=[Tbl_sHJEDdata].[Y6] And [rsr]>=[Tbl_sHJEDdata].[y5],10^(Log([Tbl_sHJEDdata].[x5])/Log(10)+(Log([Tbl_sHJEDdata].[x6])/Log(10)-Log([Tbl_sHJEDdata].[x5])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y5])/([Tbl_sHJEDdata].[y6]-[Tbl_sHJEDdata].[y5])),0) AS CalcRP6, IIf([rsr]<=[Tbl_sHJEDdata].[Y7] And [rsr]>=[Tbl_sHJEDdata].[y6],10^(Log([Tbl_sHJEDdata].[x6])/Log(10)+(Log([Tbl_sHJEDdata].[x7])/Log(10)-Log([Tbl_sHJEDdata].[x6])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y6])/([Tbl_sHJEDdata].[y7]-[Tbl_sHJEDdata].[y6])),0) AS CalcRP7, IIf([rsr]<=[Tbl_sHJEDdata].[Y8] And [rsr]>=[Tbl_sHJEDdata].[y7],10^(Log([Tbl_sHJEDdata].[x7])/Log(10)+(Log([Tbl_sHJEDdata].[x8])/Log(10)-Log([Tbl_sHJEDdata].[x7])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y7])/([Tbl_sHJEDdata].[y8]-[Tbl_sHJEDdata].[y7])),0) AS CalcRP8, IIf([rsr]<=[Tbl_sHJEDdata].[Y9] And [rsr]>=[Tbl_sHJEDdata].[y8],10^(Log([Tbl_sHJEDdata].[x8])/Log(10)+(Log([Tbl_sHJEDdata].[x9])/Log(10)-Log([Tbl_sHJEDdata].[x8])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y8])/([Tbl_sHJEDdata].[y9]-[Tbl_sHJEDdata].[y8])),0) AS CalcRP9, IIf([rsr]<=[Tbl_sHJEDdata].[Y10] And [rsr]>=[Tbl_sHJEDdata].[y9],10^(Log([Tbl_sHJEDdata].[x9])/Log(10)+(Log([Tbl_sHJEDdata].[x10])/Log(10)-Log([Tbl_sHJEDdata].[x9])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y9])/([Tbl_sHJEDdata].[y10]-[Tbl_sHJEDdata].[y9])),0) AS CalcRP10, IIf([rsr]<=[Tbl_sHJEDdata].[Y11] And [rsr]>=[Tbl_sHJEDdata].[y10],10^(Log([Tbl_sHJEDdata].[x10])/Log(10)+(Log([Tbl_sHJEDdata].[x11])/Log(10)-Log([Tbl_sHJEDdata].[x10])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y10])/([Tbl_sHJEDdata].[y11]-[Tbl_sHJEDdata].[y10])),0) AS CalcRP11, IIf([rsr]<=[Tbl_sHJEDdata].[Y12] And [rsr]>=[Tbl_sHJEDdata].[y11],11^(Log([Tbl_sHJEDdata].[x11])/Log(10)+(Log([Tbl_sHJEDdata].[x12])/Log(10)-Log([Tbl_sHJEDdata].[x11])/Log(10))*([rsr]-[Tbl_sHJEDdata].[y11])/([Tbl_sHJEDdata].[y12]-[Tbl_sHJEDdata].[y11])),0) AS CalcRP12, IIf([CalcRP1]+[CalcRP2]+[CalcRP3]+[CalcRP4]+[CalcRP5]+[CalcRP6]+[CalcRP7]+[CalcRP8]+[CalcRP9]+[CalcRP10]+[CalcRP11]=0,3000,0) AS CalcRP13, Tbl_Characteristic_Data.Quarters, Tbl_Characteristic_Data.QuartersCapacity, Tbl_ConsequenceScreening.LS
 FROM Tbl_POFRanges, (((Tbl_Characteristic_Data INNER JOIN Tbl_iSIMSRSR ON Tbl_Characteristic_Data.ID = Tbl_iSIMSRSR.ID) INNER JOIN Tbl_SHJEDdata ON Tbl_Characteristic_Data.ID = Tbl_SHJEDdata.[Complex ID]) INNER JOIN Qry_RSRCalc ON Tbl_Characteristic_Data.ID = Qry_RSRCalc.id) INNER JOIN Tbl_ConsequenceScreening ON Tbl_Characteristic_Data.ID = Tbl_ConsequenceScreening.ID;

I use all of those results to "map" the records in matrices. it works at this level to get where on they fall based on another look up table:
Qry_SH-LS

SELECT DISTINCT Qry_SHPOF.id, Tbl_Inspectioninterval.Iinterval, Tbl_Inspectioninterval.RiskMatrix, IIf([Quarters]=False,1000,[ChevronRiskMatrixScore]) AS chevriskmatrixscore, Qry_SHPOF.Quarters
 FROM Tbl_Inspectioninterval INNER JOIN Qry_SHPOF ON (Tbl_Inspectioninterval.COF = Qry_SHPOF.LS) AND (Tbl_Inspectioninterval.POF = Qry_SHPOF.Likelihood);

But that is where it all stops. this level is where it starts giving me the error:
Qry_SHLSMatrixDetails

SELECT DISTINCT Tbl_Characteristic_Data.ID, Tbl_Characteristic_Data.AreaCode, Tbl_Characteristic_Data.BlockNumber, Tbl_Characteristic_Data.StructureName, Qry_RobustGrade.LOF, Qry_RobustGrade.FinalLOF, Qry_SHPOF.LS, [Qry_SH-LS].RiskMatrix, Qry_SHPOF.RSR
FROM ((Tbl_Characteristic_Data INNER JOIN Qry_RobustGrade ON Tbl_Characteristic_Data.ID = Qry_RobustGrade.id) INNER JOIN [Qry_SH-LS] ON Tbl_Characteristic_Data.ID = [Qry_SH-LS].id) INNER JOIN Qry_SHPOF ON Tbl_Characteristic_Data.ID = Qry_SHPOF.ID;

and here:
Qry_OverallMatrixScore

SELECT [Qry_SH-LS].ChevRiskMatrixScore, [Qry_FPH-Asset].ChevronRiskMatrixScore, [Qry_SH-LS].RiskMatrix, [Qry_FPH-Asset].RiskMatrix, IIf([Qry_SH-LS].ChevRiskMatrixScore=[qry_fph-asset].[ChevronRiskMatrixScore],[Qry_SH-LS].RiskMatrix,IIf([Qry_SH-LS].ChevRiskMatrixScore<[qry_fph-asset].[ChevronRiskMatrixScore],[Qry_SH-LS].RiskMatrix,[qry_fph-asset].RiskMatrix)) AS ChevRiskMatrixScoreOverall, [Qry_SH-LS].Quarters
FROM (Tbl_Characteristic_Data INNER JOIN [Qry_FPH-Asset] ON Tbl_Characteristic_Data.ID = [Qry_FPH-Asset].id) INNER JOIN [Qry_SH-LS] ON Tbl_Characteristic_Data.ID = [Qry_SH-LS].id
GROUP BY [Qry_SH-LS].ChevRiskMatrixScore, [Qry_FPH-Asset].ChevronRiskMatrixScore, [Qry_SH-LS].RiskMatrix, [Qry_FPH-Asset].RiskMatrix, IIf([Qry_SH-LS].ChevRiskMatrixScore=[qry_fph-asset].[ChevronRiskMatrixScore],[Qry_SH-LS].RiskMatrix,IIf([Qry_SH-LS].ChevRiskMatrixScore<[qry_fph-asset].[ChevronRiskMatrixScore],[Qry_SH-LS].RiskMatrix,[qry_fph-asset].RiskMatrix)), [Qry_SH-LS].Quarters;

I know this is a really long post but Im in a bind and any help would be mucho appreciated!!!

[nick404]

Code tags would be helpful for readability.

[seocavaz]

I apologize, I'm a novice, how do I do that?

[nick404]

No problem just easier to read: upper right hand of the toolbar when you post there is a #, click that and post code in between.

code code code

Additionally, what error message(s) are you getting? And what is the goal of each query above?
Edit your original post with the code tags and put name of each query for better referencing.

[seocavaz]

and the error is the one in the subject "query too complex"

[June7]

Queries belong in frontend. How would you link to queries in backend?

Personally, I don't think CODE tags make query statements more readable. However, providing query names above each statement would be.

If you would like to provide db for analysis, follow instructions at bottom of my post.

[seocavaz]

POF Tool.zip

That would be great!!

[June7]

Odd, opening Relationship Tools triggers error 'System resource exceeded.'

You are using compound keys - I avoid them. However, they have not been defined as primary key in tables (Tbl_BraceScoringLong, Tbl_BraceScoringTran) - the autonumber ID is defined as PK but is not the value saved as FK. Tables should be linked on PK/FK fields. Otherwise, can cause issues although don't know if is root of this one.

[seocavaz]

Yeah I get that error throughout.

It all worked fine until I added the Qry_RSRCalc for some reason. though that query works fine. It is the queries in the later steps. I tried reducing the queries as much as possible with no effect. Ill fix the primary keys

[June7]

Consider:
vintoverride: IIf([Grade]<>"A" And [Qry_BaselineScoring].[InstallDate]<23377 And [Tbl_Characteristic_Data].[NumberLegs]<3, Switch([grade]="E","D",[Grade]="D","C",[Grade]="C","B",[Grade]="B","A"), [Grade])

Qry_RSRCalc is linking datasets on primary keys. These datasets have a 1-to-1 relationship? Again, Tbl_POFRanges is not linking its PK to Qry_RobustGrade FK.

Multiple similar name fields can indicate a non-normalized data structure.

[seocavaz]

Would creating temporary tables to run my last few queries help? If so, is there a good tutorial on how to do that?

[June7]

All those CalcRPx calculations using Log() are causing failure of Qry_SHPOF ('system resource exceeded' error). Remove all calculated fields and the query will open. I tested leaving one Log() calculated field and the query works. Apparently, 12 fields calling Log() is too much. Maybe calling a custom function that does the Log() calcs and returns the result to query would work, but I doubt it because likely the same system resources would be exceeded.

Writing records to a 'temp' table (table is permanent, records are temporary) might resolve issue. Somtimes an sql INSERT SELECT action will suffice. Otherwise, often involves VBA looping code. The looping might include reading values from a recordset, doing a calc, and running INSERT sql action. There are many examples of looping a recordset. http://allenbrowne.com/ser-29.html

I have several processes that involve temp tables and all use recordsets.

You would need to loop through recordset based on Qry_SHPOF that does not have the calculated fields, do the calcs in VBA and write values to temp table. Then use that temp table as source for subsequent queries.

[John_G]

I had no problems with the calculation queries or the relationships - the queries were a bit sluggish, but they ran.

The problem I think is in your last query, Qry_SHLSMatrixDetails.

It contains the previous one, Qry_SH-LS, and that is OK. But then it also contains Qry_RobustGrade and Qry_SHPOF, which are already a part of Qry_SH-LS by following the chain back up. In addition to that, Qry_RobustGrade is a part of Qry_SHPOF too, by following the chain up.

I can't really analyze it beyond that, but maybe there is a circular relationship somewhere behind the scenes, and Access cannot sort it out.

[June7]

So John, what is it about your PC that allowed the calcs to run? Math processor on steroids? My PC isn't so old I would expect this performance issue.

[seocavaz]

That's crazy they work on yours. I have a Surface Pro 3. that query I can definitely change up how it pulls. Maybe it will help. the problem still exists with the other queries though that I have to link at certain levels