System Resource Exceeded (Access 2000 SR1)

Topic

New Reply

I have a big query that just got too big. When I try to run it, I get an error message: “System Resource Exceeded”.

The query is almost all calculated fields that rely on other calculated fields within the query. So, I’m having a hard time splitting it apart.
I actually tried to take out the largest (and the ‘root’) calculation and run it in another query, then reference that query (subquery), but I get the same error message.

Any ideas?

Reply | Quote

Replies

Post the query and let us take a look at it.
Pat

Reply | Quote

SELECT cudata.CU_Nbr, cudata.CU_Name, cudata.State, cudata.Orig_Live_Date, cudata.Nbr_Members, cudata.Assets_Long, 15000+IIf([Nbr_Members]>7500,IIf([Nbr_Members]>20000,(20000-7500)*2.75+IIf([Nbr_Members]>50000,(50000-20000)*1.75+IIf([Nbr_Members]>100000,(100000-50000)*0.75,([Nbr_Members]-50000)*0.75),([Nbr_Members]-20000)*1.75),([Nbr_Members]-7500)*2.75),0)+15000+IIf([Assets_Long]>25000000,IIf([Assets_Long]>100000000,(100000000-25000000)*0.000475+IIf([Assets_Long]>300000000,(300000000-100000000)*0.00025+IIf([Assets_Long]>750000000,(750000000-300000000)*0.00009,([Assets_Long]-300000000)*0.00009),([Assets_Long]-100000000)*0.00025),([Assets_Long]-25000000)*0.000475),0) AS XP2_Lic_Amt, [XP2_Lic_Amt]*0.05 AS Interlinq_Amt, DateDiff(“m”,[Orig_Live_Date],Date()) AS Mos, IIf((DateDiff(“m”,[Orig_Live_Date],Date()))>60,0,(60-DateDiff(“m”,[Orig_Live_Date],Date()))/60) AS Prorate_Rate, ([XP2_Lic_Amt]+[Interlinq_Amt])*IIf([Mos]>60,0,(60-[Mos])/60) AS PR_Amt, xp2_install.xp_Discount_Rate, IIf([xp_Discount_Rate]>0,([XP2_Lic_Amt]-[PR_Amt])*[xp_Discount_Rate],0) AS Disc_Amt, [XP2_Lic_Amt]*0.25 AS P1234_XP2, [PR_Amt]*0.25 AS P1234_PR, xp2_install.xp_PSA_Signed_PSA_Amt, IIf([xp_PSA_Signed_PSA_Amt]<([P1234_XP2]-[P1234_PR]),[xp_PSA_Signed_PSA_Amt],([P1234_XP2]-[P1234_PR])) AS P1_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr])<([P1234_XP2]+[Interlinq_Amt]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr]),([P1234_XP2]-[P1234_PR])) AS P2_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P3_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P4_PSA_Cr
FROM cudata INNER JOIN xp2_install ON cudata.CU_Nbr = xp2_install.cu_ID
ORDER BY cudata.CU_Name, cudata.State;

Reply | Quote

Whoa, I shouldn’t have asked !!!
What you could try and do is to set the Calculation into a module as a function and reference the function from the query.
There are only 2 arguments for the function, Nbr_Members and Assets_Long.
Pat

Reply | Quote

That’s probably a good idea. I’ve never done that before, though.
I will look in some books, but if you might give me a simple example of the SQL (or query) that would reference the function, and the function itself, that would be helpful.

Thank you…

Reply | Quote

Your SQL could look like this:
SELECT cudata.CU_Nbr, cudata.CU_Name, cudata.State, cudata.Orig_Live_Date, cudata.Nbr_Members, cudata.Assets_Long, CalcLicAmt(Nbr_Members,Assets_Long) AS XP2_Lic_Amt, [XP2_Lic_Amt]*0.05 AS Interlinq_Amt, DateDiff(“m”,[Orig_Live_Date],Date()) AS Mos, IIf((DateDiff(“m”,[Orig_Live_Date],Date()))>60,0,(60-DateDiff(“m”,[Orig_Live_Date],Date()))/60) AS Prorate_Rate, ([XP2_Lic_Amt]+[Interlinq_Amt])*IIf([Mos]>60,0,(60-[Mos])/60) AS PR_Amt, xp2_install.xp_Discount_Rate, IIf([xp_Discount_Rate]>0,([XP2_Lic_Amt]-[PR_Amt])*[xp_Discount_Rate],0) AS Disc_Amt, [XP2_Lic_Amt]*0.25 AS P1234_XP2, [PR_Amt]*0.25 AS P1234_PR, xp2_install.xp_PSA_Signed_PSA_Amt, IIf([xp_PSA_Signed_PSA_Amt]<([P1234_XP2]-[P1234_PR]),[xp_PSA_Signed_PSA_Amt],([P1234_XP2]-[P1234_PR])) AS P1_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr])<([P1234_XP2]+[Interlinq_Amt]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr]),([P1234_XP2]-[P1234_PR])) AS P2_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P3_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P4_PSA_Cr
FROM cudata INNER JOIN xp2_install ON cudata.CU_Nbr = xp2_install.cu_ID
ORDER BY cudata.CU_Name, cudata.State;

Then put the Function CalcLicAmt into a module like:
Public Function CalcLicAmt(NbrofMembers as Variant, AssetsLong as Variant) as Currency
…do all your calculations in here …..
End Function

I don't know if Currency is what is required, or Variant or what, maybe another lounger can help here.

HTH
Pat

Reply | Quote

The Function worked brilliantly!
I was running into some troubles – and was perplexed – until I finally realized that my Assets field was too large for a variable of type Integer or even Long. When I finally set it to Double, it worked.

Thank you!

Reply | Quote

I’m glad it worked.
Did you define the CalLicAmt as Currency?
Pat

Reply | Quote

Yes, the return value is Currency.
Thanks, again.

Reply | Quote