Results 1 to 5 of 5
  1. #1
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287

    Browne's ConcatRelated function - System Resources Exceeded

    Hey guys,



    I'm completely stuck.

    Our current system lists the accounts payable GL numbers in a detail table, 1 account per record. Like this:
    Click image for larger version. 

Name:	Screen Shot 2016-01-08 at 4.13.32 PM.png 
Views:	24 
Size:	14.6 KB 
ID:	23338
    The system that we are switching to requires us to import this info but 1 record per invoice, with the accounts in 1 field, separated by commas. Like this:
    Click image for larger version. 

Name:	Screen Shot 2016-01-08 at 2.55.56 PM.png 
Views:	24 
Size:	11.6 KB 
ID:	23339

    That second snippet was created using Browne's function. (Note: I did a totals query to combine the double freight charge and sum the amounts. Its a freight charge and fuel charge, which we just bill as freight anyways.)

    Anyways, I built that test query on our live ODBC table that contains 100k+ records. The query that combines the freight charges is built on that table, and I used this query to limit the data to a single invoice for testing. That query is this:
    Code:
    SELECT [AP_IVC$GL_ACCT].IVC_CD, [AP_IVC$GL_ACCT].CO_CD, [AP_IVC$GL_ACCT].VE_CD, [AP_IVC$GL_ACCT].GL_ACCT_CD, Sum([AP_IVC$GL_ACCT].AMT) AS SumOfAMT, Max([AP_IVC$GL_ACCT].[SEQ#]) AS [MaxOfSEQ#]FROM [AP_IVC$GL_ACCT]
    GROUP BY [AP_IVC$GL_ACCT].IVC_CD, [AP_IVC$GL_ACCT].CO_CD, [AP_IVC$GL_ACCT].VE_CD, [AP_IVC$GL_ACCT].GL_ACCT_CD
    HAVING ((([AP_IVC$GL_ACCT].IVC_CD)="141075-201509"));
    The query that is built on the previous one is the one that contains the concat related function:
    Code:
    SELECT Q_APDetail_grouped.IVC_CD, Q_APDetail_grouped.CO_CD, Q_APDetail_grouped.VE_CD, ConcatRelated("GL_ACCT_CD","Q_APDetail_grouped") AS GLCodes, ConcatRelated("SumOfAMT","Q_APDetail_grouped") AS AMTs
    FROM Q_APDetail_grouped
    GROUP BY Q_APDetail_grouped.IVC_CD, Q_APDetail_grouped.CO_CD, Q_APDetail_grouped.VE_CD, ConcatRelated("GL_ACCT_CD","Q_APDetail_grouped"), ConcatRelated("SumOfAMT","Q_APDetail_grouped");
    As soon as I remove that criteria in the first criteria and try and pull all records with the 2nd query, it hangs for about 5 minutes and then I get a "system resources exceeded" error. I have tried limiting the 1st query to the top 5 and 5% records, it still fails.

    Any helpful tips?

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not to familiar with concatrelated but looks like it is bringing every glcode and amount through - suspect it is missing a criteria of the invoice number

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's the link to Allen Browne's ConcatRelated Function: http://allenbrowne.com/func-concat.html

    Why do you want to Concatenate those sums?

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as I thought - you are missing a criteria

    ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

    also be aware of

    'Bug warning: If the function returns more than 255 characters...'

  5. #5
    dheupel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    1
    I was getting the "Systems resources exceeded" message in Access too. It was on a make-table query that wasn't that large. Apparently, this is an issue with the 64-bit Access version. Here's the solution that worked for me: Go into design-view in the offending query. In the query properties, change the "Use Transaction" field from "Yes" to "No".

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

Similar Threads

  1. System resource exceeded
    By PJ Crittenden in forum Import/Export Data
    Replies: 1
    Last Post: 10-10-2014, 10:28 AM
  2. System resource exceeded
    By fdormoy in forum Access
    Replies: 7
    Last Post: 07-17-2014, 10:50 AM
  3. System resource exceeded
    By ahmed_ae in forum Queries
    Replies: 1
    Last Post: 08-01-2013, 12:53 AM
  4. Replies: 2
    Last Post: 07-12-2013, 06:55 AM
  5. Replies: 12
    Last Post: 12-11-2012, 01:47 PM

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