System Resource Exceeded/Query Too Complex

  • Thread starter Thread starter merry_fay
  • Start date Start date
M

merry_fay

Hi,

First of all, I'd like to apologise for posting so much code, but
unfortunately without seeing it, it's probably difficult to work out what's
happening

I'm trying to build a union query which I will later drop into some code,
but I'm having problems with the error message 'system resource exceeded' or
if I tweak a couple of speech marks (toggle text/numbers) 'Query Too Complex'


SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget TPID].[Business
Service], [Budget TPID].TPID, Null AS Project, [Unit Price].Co,
Sum([jan]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 1,
Sum([feb]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 2,
Sum([mar]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 3,
Sum([apr]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 4,
Sum([may]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 5,
Sum([jun]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 6,
Sum([jul]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 7,
Sum([aug]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 8,
Sum([sep]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 9,
Sum([oct]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 10,
Sum([nov]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 11,
Sum([dec]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 12,
[Budget TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, 1.1 AS
[Unique]
FROM [Unit Price] INNER JOIN [Budget TPID] ON ([Unit Price].Version =
[Budget TPID].Version) AND ([Unit Price].Year = [Budget TPID].Year) AND
([Unit Price].TPID = [Budget TPID].TPID) AND ([Unit Price].TSL = [Budget
TPID].TSL) AND ([Unit Price].Application = [Budget TPID].Application) AND
([Unit Price].[Business Service] = [Budget TPID].[Business Service]) AND
([Unit Price].Co = [Budget TPID].Co) AND ([Unit Price].Classification =
[Budget TPID].Classification)
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [Unit Price].Co, [Budget
TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, null
HAVING ((([Unit Price].Year)='2010') AND (([Unit Price].UPVsn)=1.1))

UNION ALL SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co,
Sum([jan]*[charge]), Sum([feb]*[charge]), Sum([mar]*[charge]),
Sum([apr]*[charge]), Sum([may]*[charge]), Sum([jun]*[charge]),
Sum([jul]*[charge]), Sum([aug]*[charge]), Sum([sep]*[charge]),
Sum([oct]*[charge]), Sum([nov]*[charge]), Sum([dec]*[charge]),
[Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
FROM [Budget TPID] INNER JOIN [1- CostingData] ON ([Budget TPID].TSL = [1-
CostingData].TSL) AND ([Budget TPID].Co = [1- CostingData].Co)
WHERE [1- CostingData].TPID Is Null
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co, [Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
HAVING [1- CostingData].Project Is Null AND [Budget TPID].Classification="D"

UNION ALL SELECT TSL, 'xxx', 'xxx', TPID, Project, Co, sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), 'M', 2010, 1.1, 1.1
FROM [1- CostingData]
GROUP BY TSL, TPID, Project, Co
HAVING TPID Is Not Null AND Project Is Null;

Both Budget TPID & Unit Price are tables & 1 -Costing Data is a union query
itself.

The 1st & 3rd, & 1st & 2nd queries work together & all work individually,
but I can't run all 3 parts together or just the 2nd & 3rd.

Any suggestions?
The 1st 2 parts bring back 1378 rows of data & the 3rd part 41 rows!

Thanks
merry_fay
 
On Wed, 20 Jan 2010 04:47:02 -0800, merry_fay

Without looking at your code in detail: you appear to have a bad
database design: the twelve month fields represent a repeating group
which have no place in a relational database design. Experiment with a
correct db design and this problem may go away. Your month buckets
would be created with a crosstab query - it conveniently does the Sum
for you too. Good db design has its rewards :-) At least THEN if you
still get 'Query too complex' you know you have taken all mitigating
steps.
Using DistinctRow sometimes helps.

-Tom.
Microsoft Access MVP

Hi,

First of all, I'd like to apologise for posting so much code, but
unfortunately without seeing it, it's probably difficult to work out what's
happening

I'm trying to build a union query which I will later drop into some code,
but I'm having problems with the error message 'system resource exceeded' or
if I tweak a couple of speech marks (toggle text/numbers) 'Query Too Complex'


SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget TPID].[Business
Service], [Budget TPID].TPID, Null AS Project, [Unit Price].Co,
Sum([jan]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 1,
Sum([feb]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 2,
Sum([mar]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 3,
Sum([apr]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 4,
Sum([may]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 5,
Sum([jun]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 6,
Sum([jul]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 7,
Sum([aug]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 8,
Sum([sep]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 9,
Sum([oct]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 10,
Sum([nov]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 11,
Sum([dec]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 12,
[Budget TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, 1.1 AS
[Unique]
FROM [Unit Price] INNER JOIN [Budget TPID] ON ([Unit Price].Version =
[Budget TPID].Version) AND ([Unit Price].Year = [Budget TPID].Year) AND
([Unit Price].TPID = [Budget TPID].TPID) AND ([Unit Price].TSL = [Budget
TPID].TSL) AND ([Unit Price].Application = [Budget TPID].Application) AND
([Unit Price].[Business Service] = [Budget TPID].[Business Service]) AND
([Unit Price].Co = [Budget TPID].Co) AND ([Unit Price].Classification =
[Budget TPID].Classification)
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [Unit Price].Co, [Budget
TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, null
HAVING ((([Unit Price].Year)='2010') AND (([Unit Price].UPVsn)=1.1))

UNION ALL SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co,
Sum([jan]*[charge]), Sum([feb]*[charge]), Sum([mar]*[charge]),
Sum([apr]*[charge]), Sum([may]*[charge]), Sum([jun]*[charge]),
Sum([jul]*[charge]), Sum([aug]*[charge]), Sum([sep]*[charge]),
Sum([oct]*[charge]), Sum([nov]*[charge]), Sum([dec]*[charge]),
[Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
FROM [Budget TPID] INNER JOIN [1- CostingData] ON ([Budget TPID].TSL = [1-
CostingData].TSL) AND ([Budget TPID].Co = [1- CostingData].Co)
WHERE [1- CostingData].TPID Is Null
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co, [Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
HAVING [1- CostingData].Project Is Null AND [Budget TPID].Classification="D"

UNION ALL SELECT TSL, 'xxx', 'xxx', TPID, Project, Co, sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), 'M', 2010, 1.1, 1.1
FROM [1- CostingData]
GROUP BY TSL, TPID, Project, Co
HAVING TPID Is Not Null AND Project Is Null;

Both Budget TPID & Unit Price are tables & 1 -Costing Data is a union query
itself.

The 1st & 3rd, & 1st & 2nd queries work together & all work individually,
but I can't run all 3 parts together or just the 2nd & 3rd.

Any suggestions?
The 1st 2 parts bring back 1378 rows of data & the 3rd part 41 rows!

Thanks
merry_fay
Click to expand...
 
Thanks for looking, but I think I've solved it myself now -the union query
being used is only capable of being used once in a further union query.

I've changed it so it now runs a make-table query when I need to use it.

Just a small thought -the difference is probably negligible, but does code
run quicker to do a delete * query followed by an append or to just do a
straight make-table query?

Thanks
merry_fay
 
Hi Tom,

I'm not sure how to use the cross-tab query as you suggested with my data.

The 1st & 2nd parts both use a table with 12 columns for the months with
monthly volumes in them which joins with a table with a charge per item that
needs to be multiplied by the volumes to create a monthly charge.

The 3rd part picks up a single column with a full year charge in it which
needs to be divided by 12 to give a monthly charge.

If I try to create the 3rd part in a cross-tab query, I have nothing to use
as my rows. Is there a special way I'm supposed to do it?

Thanks
merry_fay



Tom van Stiphout said:
On Wed, 20 Jan 2010 04:47:02 -0800, merry_fay

Without looking at your code in detail: you appear to have a bad
database design: the twelve month fields represent a repeating group
which have no place in a relational database design. Experiment with a
correct db design and this problem may go away. Your month buckets
would be created with a crosstab query - it conveniently does the Sum
for you too. Good db design has its rewards :-) At least THEN if you
still get 'Query too complex' you know you have taken all mitigating
steps.
Using DistinctRow sometimes helps.

-Tom.
Microsoft Access MVP

Hi,

First of all, I'd like to apologise for posting so much code, but
unfortunately without seeing it, it's probably difficult to work out what's
happening

I'm trying to build a union query which I will later drop into some code,
but I'm having problems with the error message 'system resource exceeded' or
if I tweak a couple of speech marks (toggle text/numbers) 'Query Too Complex'


SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget TPID].[Business
Service], [Budget TPID].TPID, Null AS Project, [Unit Price].Co,
Sum([jan]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 1,
Sum([feb]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 2,
Sum([mar]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 3,
Sum([apr]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 4,
Sum([may]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 5,
Sum([jun]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 6,
Sum([jul]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 7,
Sum([aug]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 8,
Sum([sep]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 9,
Sum([oct]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 10,
Sum([nov]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 11,
Sum([dec]*IIf(Override=0,[Unit Cost/Charge],Override)) AS 12,
[Budget TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, 1.1 AS
[Unique]
FROM [Unit Price] INNER JOIN [Budget TPID] ON ([Unit Price].Version =
[Budget TPID].Version) AND ([Unit Price].Year = [Budget TPID].Year) AND
([Unit Price].TPID = [Budget TPID].TPID) AND ([Unit Price].TSL = [Budget
TPID].TSL) AND ([Unit Price].Application = [Budget TPID].Application) AND
([Unit Price].[Business Service] = [Budget TPID].[Business Service]) AND
([Unit Price].Co = [Budget TPID].Co) AND ([Unit Price].Classification =
[Budget TPID].Classification)
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [Unit Price].Co, [Budget
TPID].Classification, [Unit Price].Year, [Unit Price].UPVsn, null
HAVING ((([Unit Price].Year)='2010') AND (([Unit Price].UPVsn)=1.1))

UNION ALL SELECT [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co,
Sum([jan]*[charge]), Sum([feb]*[charge]), Sum([mar]*[charge]),
Sum([apr]*[charge]), Sum([may]*[charge]), Sum([jun]*[charge]),
Sum([jul]*[charge]), Sum([aug]*[charge]), Sum([sep]*[charge]),
Sum([oct]*[charge]), Sum([nov]*[charge]), Sum([dec]*[charge]),
[Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
FROM [Budget TPID] INNER JOIN [1- CostingData] ON ([Budget TPID].TSL = [1-
CostingData].TSL) AND ([Budget TPID].Co = [1- CostingData].Co)
WHERE [1- CostingData].TPID Is Null
GROUP BY [Budget TPID].TSL, [Budget TPID].Application, [Budget
TPID].[Business Service], [Budget TPID].TPID, [1- CostingData].Project,
[Budget TPID].Co, [Budget TPID].Classification, [Budget TPID].Year, 1.1, 1.1
HAVING [1- CostingData].Project Is Null AND [Budget TPID].Classification="D"

UNION ALL SELECT TSL, 'xxx', 'xxx', TPID, Project, Co, sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), sum([Charge]/12),
sum([Charge]/12), sum([Charge]/12), sum([Charge]/12), 'M', 2010, 1.1, 1.1
FROM [1- CostingData]
GROUP BY TSL, TPID, Project, Co
HAVING TPID Is Not Null AND Project Is Null;

Both Budget TPID & Unit Price are tables & 1 -Costing Data is a union query
itself.

The 1st & 3rd, & 1st & 2nd queries work together & all work individually,
but I can't run all 3 parts together or just the 2nd & 3rd.

Any suggestions?
The 1st 2 parts bring back 1378 rows of data & the 3rd part 41 rows!

Thanks
merry_fay
Click to expand...
.
Click to expand...
 
I prefer to build the table and use delete and insert.

One big reason is that I can add indexes to the already built table which
will speed up queries using the work table. The down side of having indexes
is that it adds some time to populating/clearing the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top