Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    System Resource Exceeded (Access 2000 SR1)

    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?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

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

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    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;

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    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

  5. #5
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    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...

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    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!

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

    I'm glad it worked.
    Did you define the CalLicAmt as Currency?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: System Resource Exceeded (Access 2000 SR1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •