Query Too Complex or System Resource Exceeded

SELECT tbl_Client.Client_Name, tbl_Client.Client_Code_MPC_Style, tbl_Client.MPC_Code, tbl_Client_Data.Valuation_Date, tbl_Client_Data.PY_Active_Count, tbl_Client_Data.PY_Inactive_Count, tbl_Client_Data.FeeSheet_Funding_Report, tbl_Client_Data.FeeSheet_FASEXP_Report, tbl_Client_Data.FeeSheet_FASDISC_Report, tbl_Client_Data.FeeSheet_Combined_Report, tbl_Client_Data.FeeSheet_NoRateChange, tbl_Client_Data.FeeSheet_OneStopFAS_Report, tbl_Client_Data.FeeSheet_BenStmt, tbl_Client_Data.FeeSheet_CompositeOnly, tbl_Client_Data.FeeSheet_5500_CountsOnly, tbl_Client_Data.FeeSheet_5500_Large, tbl_Client_Data.FeeSheet_5500_Small, tbl_Client_Data.FeeSheet_SchBOnly, tbl_Client_Data.FeeSheet_SSAOnly, tbl_Client_Data.FeeSheet_PBGC_1A, tbl_Client_Data.FeeSheet_PBGC_1ES, tbl_Client_Data.FeeSheet_PBGC_EZ, tbl_Client_Data.FeeSheet_SignSchB, tbl_Client_Data.FeeSheet_SignPBGC, tbl_Client_Data.FeeSheet_ScrubberConv, tbl_Client_Data.FeeSheet_PencostConv, tbl_Client_Data.FeeSheet_AuditorResponse, tbl_Client_Data.FeeSheet_Other, tbl_Client_Data.FeeSheet_Other_Description, tbl_Client_Data.FeeSheet_Other_Amount, tbl_Client_Data.FeeSheet_Adjustor, tbl_Client_Data.FeeSheet_Adjustor_Description, tbl_Client_Data.FeeSheet_Adjustor_Amount, tbl_Client_Data.Current_Valuation_Date, nz(nz([PY_Active_Count],0)+nz([PY_Inactive_Count],0),0) AS Total_Participants, IIf([Total_Participants]<100,[Total_Participants],IIf([Total_Participants]<500,[Total_Participants]-100,IIf([Total_Participants]<1000,[Total_Participants]-500,IIf([Total_Participants]<1500,[Total_Participants]-1000,IIf([Total_Participants]<5000,[Total_Participants]-1500,[Total_Participants]-5000))))) AS Total_Modified_Participants, IIf([FeeSheet_Funding_Report],([FF_All]+IIf([Total_Participants]<100,[PPF_PPT_0],IIf([Total_Participants]<500,[PPF_PPT_100]*[Total_Modified_Participants],IIf([Total_Participants]<1000,[PPF_PPT_500_Fixed]+([Total_Modified_Participants]*[PPF_PPT_500]),IIf([Total_Participants]<1500,[PPF_PPT_1000_Fixed]+([Total_Modified_Participants]*[PPF_PPT_1000]),IIf([Total_Participants]<5000,[PPF_PPT_1500_Fixed]+([Total_Modified_Participants]*[PPF_PPT_1500]),[PPF_PPT_5000_Fixed]+([Total_Modified_Participants]*[PPF_PPT_5000]))))))),0) AS Fee_Funding, IIf([FeeSheet_FASEXP_Report],[FF_FASExp]-IIf([FeeSheet_Combined_Report],[FF_CombinedDiscount],0),0) AS Fee_FASExp, IIf([FeeSheet_FASDISC_Report] And [FeeSheet_OneStopFAS_Report],[FF_FASDISC],IIf([FeeSheet_FASDISC_Report],[FF_FASDISC]-IIf([FeeSheet_NoRateChange],[FF_DiscountRateDiscount],0),0)) AS Fee_FasDISC, IIf([FeeSheet_BenStmt],IIf([PY_Active_Count]<100,IIf([STMF_PPT_0]*[PY_Active_Count]<[STMF_PPT_0_Max],[STMF_PPT_0]*[PY_Active_Count],[STMF_PPT_0_Max])+[STMF_PPT_0_Flat],IIf([PY_Active_Count]<500,IIf([STMF_PPT_100]*[PY_Active_Count]<[STMF_PPT_100_Max],[STMF_PPT_100]*[PY_Active_Count],[STMF_PPT_100_Max])+[STMF_PPT_100_Flat],IIf([PY_Active_Count]<1000,IIf([STMF_PPT_500]*[PY_Active_Count]<[STMF_PPT_500_Max],[STMF_PPT_500]*[PY_Active_Count],[STMF_PPT_500_Max])+[STMF_PPT_500_Flat],IIf([PY_Active_Count]<1500,IIf([STMF_PPT_1000]*[PY_Active_Count]<[STMF_PPT_1000_Max],[STMF_PPT_1000]*[PY_Active_Count],[STMF_PPT_1000_Max])+[STMF_PPT_1000_Flat],IIf([PY_Active_Count]<5000,IIf([STMF_PPT_1500]*[PY_Active_Count]<[STMF_PPT_1500_Max],[STMF_PPT_1500]*[PY_Active_Count],[STMF_PPT_1500_Max])+[STMF_PPT_1500_Flat],[STMF_PPT_5000]*[PY_Active_Count]+[STMF_PPT_5000_Flat]))))),0) AS Fee_BenStmt, IIf([FeeSheet_CompositeOnly],IIf([PY_Active_Count]<100,IIf([STMF_PPT_0]*[PY_Active_Count]<[STMF_PPT_0_Max],[STMF_PPT_0]*[PY_Active_Count],[STMF_PPT_0_Max]/2)+[STMF_PPT_0_Flat],IIf([PY_Active_Count]<500,IIf([STMF_PPT_100]*[PY_Active_Count]<[STMF_PPT_100_Max],[STMF_PPT_100]*[PY_Active_Count]/2,[STMF_PPT_100_Max]/2)+[STMF_PPT_100_Flat],IIf([PY_Active_Count]<1000,IIf([STMF_PPT_500]*[PY_Active_Count]<[STMF_PPT_500_Max],[STMF_PPT_500]*[PY_Active_Count]/2,[STMF_PPT_500_Max]/2)+[STMF_PPT_500_Flat],IIf([PY_Active_Count]<1500,IIf([STMF_PPT_1000]*[PY_Active_Count]<[STMF_PPT_1000_Max],[STMF_PPT_1000]*[PY_Active_Count]/2,[STMF_PPT_1000_Max]/2)+[STMF_PPT_1000_Flat],IIf([PY_Active_Count]<5000,IIf([STMF_PPT_1500]*[PY_Active_Count]<[STMF_PPT_1500_Max],[STMF_PPT_1500]*[PY_Active_Count]/2,[STMF_PPT_1500_Max]/2)+[STMF_PPT_1500_Flat],[STMF_PPT_5000]*[PY_Active_Count]/2+[STMF_PPT_5000_Flat]))))),0) AS Fee_CompositeOnly, IIf([FeeSheet_5500_CountsOnly],[FF_5500Counts],0) AS Fee_5500CountsOnly, IIf([FeeSheet_5500_Large],[FF_5500Lg]+IIf([FeeSheet_SignSchB],[FF_SchBSign],0),0) AS Fee_5500Large, IIf([FeeSheet_5500_Small],[FF_5500Sm]+IIf([FeeSheet_SignSchB],[FF_SchBSign],0),0) AS Fee_5500Small, IIf([FeeSheet_SchBOnly],[FF_SchB]+IIf([FeeSheet_SignSchB],[FF_SchBSign],0),0) AS Fee_SchBOnly, IIf([FeeSheet_PBGC_1A],[FF_PBGC]+IIf([FeeSheet_SignPBGC],[FF_PBGCSign],0),0) AS Fee_PBGCA, IIf([FeeSheet_PBGC_EZ],[FF_PBGCEZ]+IIf([FeeSheet_SignPBGC],[FF_PBGCSign],0),0) AS Fee_PBGCEZ, IIf([FeeSheet_PBGC_1ES],[FF_PBGC1ES],0) AS Fee_PBGCES, IIf([FeeSheet_Other]=-1,nz([FeeSheet_Other_Amount],0),0) AS Fee_Other, IIf([FeeSheet_Adjustor]=-1,nz([FeeSheet_Adjustor_Amount],0),0) AS Fee_Adjustor, [Fee_Funding]+[Fee_FASExp]+[Fee_FasDISC]+[Fee_BenStmt]+[Fee_CompositeOnly]+[Fee_5500CountsOnly]+[Fee_5500Large]+[Fee_5500Small]+[Fee_SchBOnly]+[Fee_PBGCA]+[Fee_PBGCEZ]+[Fee_PBGCES]+[Fee_Other]+[Fee_Adjustor] AS Fee_GrandTotal, tbl_Client_Data.Fiscal_Date, tbl_Lookup_FeeSheets.LoadPercentage, Round(2.5+IIf([Total_Participants]>500,([Total_Participants]-500)*0.001,0),1) AS Hours_Analyst_DataRequest, Round(2+IIf(Not [FeeSheet_Combined_Report] Or [FeeSheet_OneStopFAS_Report],2,0),1) AS Hours_Analyst_Reports, Round((((([Hours_Analyst_Wyval_Temp1]+[Hours_Analyst_Wyval_Temp2]+[Hours_Analyst_Wyval_Temp3])*10)+0.5)/10),1) AS Hours_Analyst_Wyval, Round((((((([Fee_Funding]+[Fee_FASExp]+IIf([FeeSheet_OneStopFAS_Report],[Fee_FasDISC],0)+[Fee_BenStmt]+[Fee_CompositeOnly])*0.55)/([Rate_Analyst]*[LoadPercentage]))*10)+0.5)/10)-([Hours_Analyst_Reports]+[Hours_Analyst_Wyval]+[Hours_Analyst_DataRequest]),1) AS Hours_Analyst_DataAssets, nz([Hours_Analyst_DataRequest],0)+nz([Hours_Analyst_Reports],0)+nz([Hours_Analyst_Wyval],0)+nz([Hours_Analyst_DataAssets],0) AS Hours_Analyst_TotalValAndStmt, IIf(7<(([Fee_Funding]/6)/([Rate_Analyst]*[LoadPercentage])),7,(([Fee_Funding]/6)/([Rate_Analyst]*[LoadPercentage]))) AS Hours_Analyst_Wyval_Temp1, ((IIf([FeeSheet_OneStopFAS_Report],[Fee_FasDISC],0)+[Fee_FASExp])/6/([Rate_Analyst]*[LoadPercentage])) AS Hours_Analyst_Wyval_Temp2, ((([Fee_BenStmt]+[Fee_CompositeOnly])/6)/([Rate_Analyst]*[LoadPercentage])) AS Hours_Analyst_Wyval_Temp3, Round((((((IIf(Not [FeeSheet_OneStopFAS_Report],[Fee_FasDISC]/2,0))/([Rate_Analyst]*[LoadPercentage]))*10)+0.5)/10),1) AS Hours_Analyst_FASDisc, Round((((((([Fee_5500CountsOnly]+[Fee_5500Large]+[Fee_5500Small]+[Fee_SchBOnly]+[Fee_PBGCA]+[Fee_PBGCEZ]-IIf([FeeSheet_SignSchB],[FF_SchBSign],0)-IIf([FeeSheet_SignPBGC],[FF_PBGCSign],0))/2)/([Rate_Analyst]*[LoadPercentage]))*10)+0.5)/10),1) AS Hours_Analyst_GovForms, Round(((((([Fee_PBGCES]/2)/([Rate_Analyst]*[LoadPercentage]))*10)+0.5)/10),1) AS Hours_Analyst_PBGCES, Round(1.5+IIf([Total_Participants]>500,([Total_Participants]-500)*0.0005,0),1) AS Hours_PL_DataRequest, Round(0.45*([Fee_Funding]+[Fee_FASExp]+[Fee_BenStmt]+[Fee_CompositeOnly]+IIf([FeeSheet_OneStopFAS_Report],[Fee_FasDISC],0))/(((((2*[Rate_ProjectLeader]*[LoadPercentage])*10)+0.5)/10))-((([Hours_PL_DataRequest]+[Hours_PL_Reports])/2)),1) AS Hours_PL_DataAssets, Round(0.45*([Fee_Funding]+[Fee_FASExp]+[Fee_BenStmt]+[Fee_CompositeOnly]+IIf([FeeSheet_OneStopFAS_Report],[Fee_FasDISC],0))/(((((2*[Rate_ProjectLeader]*[LoadPercentage])*10)+0.5)/10))-((([Hours_PL_DataRequest]+[Hours_PL_Reports])/2)),1) AS Hours_PL_Wyval, 0.5 AS Hours_PL_Reports, qry_Analyst_Rates.Employee_Hourly_Rate AS Rate_Analyst, qry_ProjectLeader_Rates.Employee_Hourly_Rate AS Rate_ProjectLeader

FROM (((tbl_Client LEFT JOIN tbl_Client_Data ON tbl_Client.Client_Code_MPC_Style = tbl_Client_Data.Client_Code_MPC_Style) LEFT JOIN tbl_Lookup_FeeSheets ON tbl_Client_Data.FeeSheet_Default = tbl_Lookup_FeeSheets.Pkey) LEFT JOIN qry_Analyst_Rates ON (tbl_Client_Data.Client_Code_MPC_Style = qry_Analyst_Rates.Client_Code_MPC_Style) AND (tbl_Client_Data.Valuation_Date = qry_Analyst_Rates.Valuation_Date)) LEFT JOIN qry_ProjectLeader_Rates ON (tbl_Client_Data.Client_Code_MPC_Style = qry_ProjectLeader_Rates.Client_Code_MPC_Style) AND (tbl_Client_Data.Valuation_Date = qry_ProjectLeader_Rates.Valuation_Date)

WHERE (((tbl_Client.Use_For_Tracking)=True))

ORDER BY tbl_Client.Client_Name, tbl_Client_Data.Valuation_Date DESC;

The final query will always have to be under the 255 field limit of the query engine.  This number is arrived at by counting each field from each table being used from the starting query to the final query, assuming a stacked set.  (ie query1 is used as a source by query2 etc.)

I don't think your hitting the string limit, your hitting the field count limit.

regards
Jack

yep that's a monster alright!!
Your resources are being gobbled up by IIF and other calcs in the query.

Have you considered perhaps dumping the source fields into a temporary table and doing the calculation through a VBA routine and then report from the table?

-J-

Can you break the query down to smaller segments, then bring  those queries together?  For example, lets say there is stuff you have to do to each table data, do that in seperate queries first.

1William,

Yes, I have tried that.  It does not solve the problem.

**************************************

Jade,

A lot of these calculations are based on how many participants we have in a certain plan.  What do you think would happen if I wrote a few VBA functions and just passed the total number of participants to the function as a parameter?  The query wouldn't actually be performing the work, but it would have to wait for the function to return a result.  Would that not gobble up as many resources?

Thanks!

Jade,

Thank you for your help.  After thinking about your solution and how much work that would require I really started to get depressed.  I would much rather have Access do the work for me!  However, I realized that I only needed about 4 more fields and this query would be complete!  I was able to take out a couple of "sum" fields.  That is, I had a couple of the fields performing just simple sums on other calculated fields.  Of course, that can easily be done in a calculated control instead.  After removing those fields and adding my needed ones I was able to fit everything!  

For future reference, how would your solution have worked on a form?  Currently, the user fills in the total number of participants in the plan and then clicks on check boxes for which tasks we are to do for that client.  Each piece of work is calculated based on the number of participants in the plan.  So, the user can come in at any time and change the number of participants in the plan and that automatically updates the fees.  So each time either the type of work of the number of participants changes I would have to re-execute my make table query.  Am I thinking correctly on that?  

One last question... I am currently only testing my query on 1 record.  What if I have 400 records?  Or, what if I run this off a LAN instead of my hard drive?  Obviously there will be some performance issues, but should the query still run?

I have accepted your answer.   Thanks so much for the quick response.

Michael

One of the few times a temp table makes sense!  Nicely explained, Jack <Will pats Jack on the back>