"Query is too complex" error in Access 2007

[mattsteele8]

I am running a select query in Access 2007 which has relationships with 57 other select queries which sum up demographic and Acorn data. whenever i try to run the query i get the error "Query is too complex" but i have no idea where to start removing / shortening object names??

Any help would be most appreciated.....
Any mocking would not be appreciated but may be well deserved....

SELECT [2007_NSLSP_NF_P_RATES].NSLSP_Location, Sum(qryTotPop_Type1.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 1], Sum(qryTotPop_Type2.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 2], Sum(qryTotPop_Type3.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 3], Sum(qryTotPop_Type4.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 4], Sum(qryTotPop_Type5.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 5], Sum(qryTotPop_Type6.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 6], Sum(qryTotPop_Type7.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 7], Sum(qryTotPop_Type8.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 8], Sum(qryTotPop_Type9.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 9], Sum(qryTotPop_Type10.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 10], Sum(qryTotPop_Type11.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 11], Sum(qryTotPop_Type12.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 12], Sum(qryTotPop_Type13.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 13], Sum(qryTotPop_Type14.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 14], Sum(qryTotPop_Type15.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 15], Sum(qryTotPop_Type16.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 16], Sum(qryTotPop_Type17.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 17], Sum(qryTotPop_Type18.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 18], Sum(qryTotPop_Type19.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 19], Sum(qryTotPop_Type20.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 20], Sum(qryTotPop_Type21.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 21], Sum(qryTotPop_Type22.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 22], Sum(qryTotPop_Type23.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 23], Sum(qryTotPop_Type24.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 24], Sum(qryTotPop_Type25.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 25], Sum(qryTotPop_Type26.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 26], Sum(qryTotPop_Type27.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 27], Sum(qryTotPop_Type28.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 28], Sum(qryTotPop_Type29.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 29], Sum(qryTotPop_Type30.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 30], Sum(qryTotPop_Type31.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 31], Sum(qryTotPop_Type32.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 32], Sum(qryTotPop_Type33.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 33], Sum(qryTotPop_Type34.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 34], Sum(qryTotPop_Type35.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 35], Sum(qryTotPop_Type36.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 36], Sum(qryTotPop_Type37.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 37], Sum(qryTotPop_Type38.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 38], Sum(qryTotPop_Type39.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 39], Sum(qryTotPop_Type40.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 40], Sum(qryTotPop_Type41.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 41], Sum(qryTotPop_Type42.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 42], Sum(qryTotPop_Type43.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 43], Sum(qryTotPop_Type44.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 44], Sum(qryTotPop_Type45.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 45], Sum(qryTotPop_Type46.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 46], Sum(qryTotPop_Type47.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 47], Sum(qryTotPop_Type48.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 48], Sum(qryTotPop_Type49.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 49], Sum(qryTotPop_Type50.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 50], Sum(qryTotPop_Type51.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 51], Sum(qryTotPop_Type52.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 52], Sum(qryTotPop_Type53.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 53], Sum(qryTotPop_Type54.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 54], Sum(qryTotPop_Type55.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 55], Sum(qryTotPop_Type56.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 56], Sum(qryTotPop_Type57.[SumOfSumOfTOTAL POPULATION]) AS [Acorn Type 57] INTO tblAcornTypes
FROM ((((((((((((((((((((((((((((((((((((((((((((((((((((((((2007_NSLSP_NF_P_RATES LEFT JOIN qryTotPop_Type2 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type2.FSP_SECTOR) LEFT JOIN qryTotPop_Type3 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type3.FSP_SECTOR) LEFT JOIN qryTotPop_Type4 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type4.FSP_SECTOR) LEFT JOIN qryTotPop_Type1 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type1.FSP_SECTOR) LEFT JOIN qryTotPop_Type5 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type5.FSP_SECTOR) LEFT JOIN qryTotPop_Type6 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type6.FSP_SECTOR) LEFT JOIN qryTotPop_Type7 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type7.FSP_SECTOR) LEFT JOIN qryTotPop_Type10 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type10.FSP_SECTOR) LEFT JOIN qryTotPop_Type11 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type11.FSP_SECTOR) LEFT JOIN qryTotPop_Type12 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type12.FSP_SECTOR) LEFT JOIN qryTotPop_Type13 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type13.FSP_SECTOR) LEFT JOIN qryTotPop_Type14 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type14.FSP_SECTOR) LEFT JOIN qryTotPop_Type15 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type15.FSP_SECTOR) LEFT JOIN qryTotPop_Type16 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type16.FSP_SECTOR) LEFT JOIN qryTotPop_Type17 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type17.FSP_SECTOR) LEFT JOIN qryTotPop_Type18 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type18.FSP_SECTOR) LEFT JOIN qryTotPop_Type19 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type19.FSP_SECTOR) LEFT JOIN qryTotPop_Type20 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type20.FSP_SECTOR) LEFT JOIN qryTotPop_Type21 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type21.FSP_SECTOR) LEFT JOIN qryTotPop_Type22 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type22.FSP_SECTOR) LEFT JOIN qryTotPop_Type23 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type23.FSP_SECTOR) LEFT JOIN qryTotPop_Type24 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type24.FSP_SECTOR) LEFT JOIN qryTotPop_Type25 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type25.FSP_SECTOR) LEFT JOIN qryTotPop_Type26 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type26.FSP_SECTOR) LEFT JOIN qryTotPop_Type27 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type27.FSP_SECTOR) LEFT JOIN qryTotPop_Type28 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type28.FSP_SECTOR) LEFT JOIN qryTotPop_Type29 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type29.FSP_SECTOR) LEFT JOIN qryTotPop_Type30 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type30.FSP_SECTOR) LEFT JOIN qryTotPop_Type31 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type31.FSP_SECTOR) LEFT JOIN qryTotPop_Type32 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type32.FSP_SECTOR) LEFT JOIN qryTotPop_Type33 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type33.FSP_SECTOR) LEFT JOIN qryTotPop_Type34 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type34.FSP_SECTOR) LEFT JOIN qryTotPop_Type35 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type35.FSP_SECTOR) LEFT JOIN qryTotPop_Type36 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type36.FSP_SECTOR) LEFT JOIN qryTotPop_Type37 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type37.FSP_SECTOR) LEFT JOIN qryTotPop_Type38 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type38.FSP_SECTOR) LEFT JOIN qryTotPop_Type39 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type39.FSP_SECTOR) LEFT JOIN qryTotPop_Type40 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type40.FSP_SECTOR) LEFT JOIN qryTotPop_Type41 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type41.FSP_SECTOR) LEFT JOIN qryTotPop_Type42 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type42.FSP_SECTOR) LEFT JOIN qryTotPop_Type43 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type43.FSP_SECTOR) LEFT JOIN qryTotPop_Type44 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type44.FSP_SECTOR) LEFT JOIN qryTotPop_Type45 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type45.FSP_SECTOR) LEFT JOIN qryTotPop_Type46 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type46.FSP_SECTOR) LEFT JOIN qryTotPop_Type47 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type47.FSP_SECTOR) LEFT JOIN qryTotPop_Type48 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type48.FSP_SECTOR) LEFT JOIN qryTotPop_Type49 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type49.FSP_SECTOR) LEFT JOIN qryTotPop_Type50 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type50.FSP_SECTOR) LEFT JOIN qryTotPop_Type51 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type51.FSP_SECTOR) LEFT JOIN qryTotPop_Type52 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type52.FSP_SECTOR) LEFT JOIN qryTotPop_Type54 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type54.FSP_SECTOR) LEFT JOIN qryTotPop_Type53 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type53.FSP_SECTOR) LEFT JOIN qryTotPop_Type55 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type55.FSP_SECTOR) LEFT JOIN qryTotPop_Type56 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type56.FSP_SECTOR) LEFT JOIN qryTotPop_Type57 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type57.FSP_SECTOR) LEFT JOIN qryTotPop_Type8 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type8.FSP_SECTOR) LEFT JOIN qryTotPop_Type9 ON [2007_NSLSP_NF_P_RATES].Postal_Sector = qryTotPop_Type9.FSP_SECTOR
WHERE ((([2007_NSLSP_NF_P_RATES].[FSP Catchment Definition])=1 Or ([2007_NSLSP_NF_P_RATES].[FSP Catchment Definition])=2 Or ([2007_NSLSP_NF_P_RATES].[FSP Catchment Definition])=3))
GROUP BY [2007_NSLSP_NF_P_RATES].NSLSP_Location
HAVING ((([2007_NSLSP_NF_P_RATES].NSLSP_Location)="london central" Or ([2007_NSLSP_NF_P_RATES].NSLSP_Location)="london central" Or ([2007_NSLSP_NF_P_RATES].NSLSP_Location)="london central"));

 

[MSAccessRookie]

You can start by shortening the main Table name of [2007_NSLSP_NF_P_RATES], and the Query names (ex: qryTotPop_Type54). You really should do this on general principal, even though I doubt this is going to help as much as you need.

Question:

Do you really need 57 Queries that all appear to look exactly alike (except for the Query Index), or can an evaluation of your table design(s) be an option? If so, we may be able to greatly simplify the formula. Please let us know

 

[mattsteele8]

hi and cheers for your quick reply,

the answer is that i have done it this way because i am not that experienced with access and could not think of another way to do it.

each of the 57 queries is a filter and sum on the tblAmendedUKPostcodes table to to find out the total population of a postal sector that are in an acorn type (an acorn type is "wealthy achiever" type 1 to "working class" type 56 etc. etc.)

so i have to show lots of other data (already present) with the acorn types 1 - 57 (57 being unclassified) appended onto the right hand side of that table.

the only way that i could think to do this is sum the population who are acorn type 1 for all postal sectors giving me one query. i then did the same for the other 57 and then ran another query to append this data to the main table.

below is the sql code of one of the smaller queries.

SELECT tblAmendedUKPostcodes.FSP_SECTOR, tblAmendedUKPostcodes.[ACORN TYPE], Sum(tblAmendedUKPostcodes.[SumOfTOTAL POPULATION]) AS [SumOfSumOfTOTAL POPULATION]
FROM tblAmendedUKPostcodes
GROUP BY tblAmendedUKPostcodes.FSP_SECTOR, tblAmendedUKPostcodes.[ACORN TYPE]
HAVING (((tblAmendedUKPostcodes.[ACORN TYPE])=1))
ORDER BY tblAmendedUKPostcodes.FSP_SECTOR;

please let me know if i am being useless at explaining things and i will try harder

many thanks again for your help

 

[LPurvis]

You're limited by definition to 32 tables in a query.
That's way too complex.

Aircode - but try this instead as your whole query.

TRANSFORM Sum(A.[SumOfTOTAL POPULATION]) AS SumPop
SELECT R.NSLSP_Location
FROM [2007_NSLSP_NF_P_RATES] R LEFT JOIN tblAmendedUKPostcodes A ON R.Postal_Sector = A.FSP_SECTOR
WHERE R.[FSP Catchment Definition] In (1,2,3) AND NSLSP_Location = "london central"
GROUP BY R.NSLSP_Location
PIVOT "Acorn Type " & A.[ACORN TYPE]

Cheers.

 

[lcook1974]

I am having the same issues...but with a query based off of 21 other queries (they are counts of different activities per job, then a query to calculate the money amount per count).

This is just one to get count:

SELECT DISTINCT Employee.Fname, Employee.Lname, Employee.EmployeeID, Sum(Abs([Bone])) AS BoneCnt
FROM Employee INNER JOIN PayRollCnts ON Employee.EmployeeID = PayRollCnts.TeamTechs
GROUP BY Employee.Fname, Employee.Lname, Employee.EmployeeID, PayRollCnts.TissueDeferred, PayRollCnts.EyeOnly
HAVING (((Sum(Abs([Bone])))>0) AND ((PayRollCnts.TissueDeferred)=No) AND ((PayRollCnts.EyeOnly)=No));

Then I use that number in an expression like this

TotalBoneCnt: Cdbl(Nz([BoneCnt]*300,0))

Which will show me the total dollar amount.
Once this is done there is a final query that totals (Grand total) of all employees.

It worked fine until I added the last 4 queries, now I can't get it to open so I can take them out.

LPurvis is that a "CrossTab" query you were referring too? Do you think that is the better option to go?

I was thinking that maybe I need to group the queries(ex: Eyes, tissue, TeamLeaders, etc...) into another query then use those queries (total 5 groups)to get the "Summary" totals I am looking for...but then I was thinking that will "Still" have to run all the queries and may very well crash again.

any thoughts?

Larry

 

[LPurvis]

Yes, it was a crosstab suggested.
Obviously that's not a solution to every multi-query query. But it struck me as an obvious alternative here (because the data was grouped in an overly rigid fashion when it was groupable within a query by value).

In your scenario you say
>> "they are counts of different activities per job"
which sounds like the subqueries might be pulling (calculating) different values in each (i.e. each performs a different, dedicated function).

It's impossible to know more without detail.

 

[lcook1974]

Yes, it was a crosstab suggested.
Obviously that's not a solution to every multi-query query. But it struck me as an obvious alternative here (because the data was grouped in an overly rigid fashion when it was groupable within a query by value).

In your scenario you say
>> "they are counts of different activities per job"
which sounds like the subqueries might be pulling (calculating) different values in each (i.e. each performs a different, dedicated function).

It's impossible to know more without detail.

Hmmmm....I think you may be on to something. I'll take a look at it tomorrow. I will reply with my findings tomorrow.

Thanks for the quick reply.

Larry

 

[mattsteele8]

LPurvis thank you so much,

that works almost perfectly. the only thing that isn't right is that when it adds the acorn type columns it adds an 'Acorn Type 0' column. i am not sure where it is getting this value from as there are no columns titled acorn type 0 in the tblAmendedUKPostcodes table.

is there any way that i can get it to miss off the 'Acorn Type 0' column from the query?

Many thanks for your help

 

[LPurvis]

You can limit/coerce the columns returned by a crosstab by including an "In" statement after the PIVOT clause.
However it'll be lengthy for you - since you have 57 of the blighters! (You measuring beans?

Technically, since we've prefixed your column heads with text (which I did just for aesthetic convenience to emulate your original example) your In clause should reflect that.
Were your heads just the ACORN TYPE number value then you could have
...
PIVOT [ACORN TYPE] In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,... etc...,57)

Maintaining the wordy headers though
...
PIVOT "Acorn Type " & A.[ACORN TYPE] In ("Acorn Type 1","Acorn Type 2", "Acorn Type 3", .... etc ... , "Acorn Type 57")

Suddenly the query begins to lose some of it's tidiness. :-s
Bear in mind if you use the Crosstab as the source of another maketable query based upon it - then you're free to choose which columns from it you insert.
But then again - without an In clause - you can't be sure that your data will be such that there is every column from 1 to 57. Any data missing will result in a column missing... Unless you include that In clause.

Cheers.

 

[mattsteele8]

hi leigh,

just thinking out loud now and i will probably have proved myself wrong in the time it takes you to get back to me but can i not use a where clause to exclude the acorn type 0 i.e. WHERE ArotnType <> 'Acorn Type 0'

thanks again for you help

 

[LPurvis]

I thought you were saying earlier that you didn't have any 0 values (you said "columns" but I assume you meant values which would be grouped to become columns in the crosstab).

If there are such values then certainly removing them from the query would be effective.

But if you wanted to be sureyou had columns 1 to 57 then the In clause would be a good idea anyway. (If there were no ACORN TYPE values for, say 33 in that Location then there would be no column 33 returned unless you've forced it in the In clause!)

Cheers.

 

[lcook1974]

Leigh,
I went back through, redesigned the queries and where it was pulling from. Broke the one large query into smaller queries and now it only takes about 8 seconds to run.

Thanks for jump starting my brain yesterday!

Larry

 

[mattsteele8]

leigh,

thank you so much for the help with the massive horrible query that i had created. working perfectly now!!