System Resource Exceeded Error

  • Thread starter Thread starter Tim Dolloff
  • Start date Start date
T

Tim Dolloff

When I try to run a query that is pulling data from 12
other queries, I get a message "System Resource Exceeded".

I've got a single table that has about 12,000 records and
contains about 100 columns of sales data for 800 different
stores for each month.

I then have 12 different queries which each pull 30
columns of data for 800 stores for the each of the last 12
months.

I am then trying to add 2 columns of data together and
then sum that calculation for all 12 months so my final
query looks like this:

SELECT Q_Out1.SN, Q_Out1.DN, Q_Out1.Reg,
Q_Out1.D7+Q_Out1.D8 AS m1, Q_Out2.D7+Q_Out2.D8 AS m2,
Q_Out3.D7+Q_Out3.D8 AS m3,
Q_Out1.D7+Q_Out1.D8+Q_Out2.D7+Q_Out2.D8+Q_Out3.D7+Q_Out3.D8
+Q_Out4.D7+Q_Out4.D8+Q_Out5.D7+Q_Out5.D8+Q_Out6.D7+Q_Out6.D
8+Q_Out7.D7+Q_Out7.D8+Q_Out8.D7+Q_Out8.D8+Q_Out9.D7+Q_Out9.
D8+Q_Out10.D7+Q_Out10.D8+Q_Out11.D7+Q_Out11.D8+Q_Out12.D7+Q
_Out12.D8
FROM ((((((((((Q_Out1 INNER JOIN Q_Out2 ON Q_Out1.ID =
Q_Out2.ID) INNER JOIN Q_Out3 ON Q_Out2.ID = Q_Out3.ID)
INNER JOIN Q_Out10 ON Q_Out3.ID = Q_Out10.ID) INNER JOIN
Q_Out11 ON Q_Out10.ID = Q_Out11.ID) INNER JOIN Q_Out12 ON
Q_Out1.ID = Q_Out12.ID) INNER JOIN Q_Out4 ON Q_Out1.ID =
Q_Out4.ID) INNER JOIN Q_Out5 ON Q_Out1.ID = Q_Out5.ID)
INNER JOIN Q_Out7 ON Q_Out1.ID = Q_Out7.ID) INNER JOIN
Q_Out8 ON Q_Out1.ID = Q_Out8.ID) INNER JOIN Q_Out9 ON
Q_Out1.ID = Q_Out9.ID) INNER JOIN Q_Out6 ON Q_Out1.ID =
Q_Out6.ID;

I know that I've written queries that pull from more than
12 tables or queries so I'm not sure why this is
happening. If I run this same query on only 5 of the
queries, it works fine, but as soon as I add the 6th
query, I get that error. Am I doing something wrong or
going about this whole process the wrong way.
 
Maybe you need to break the process into more steps. Perhaps store data in
tables for intermediate steps, so that the query engine doesn't have to work
so hard at the final step.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top