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:
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:
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?