Solved

# Query is too complex.

Posted on 2013-08-27
Medium Priority
464 Views
To IrogSinta and All,

Here I go again.

I have received wonderful help form you.  Thanks again.

New challenge:

The attached database contains Query "Qry_Count_Sum_Total" .  The first step in this query is to pull sums from 11 tables.  Each sum is the result of multiple calculations.  The query gives me "Query is too Complex."   Once I have “Qry_Count_Sum_Total” working I will need to use this result in an additional calculation specific to each of the “??_???_Count_Sum” queries.  This calculation will be something like BS_Score:   [Qry_01_BS_Count_Sum].[BS_Cat_S/Count*W]/[Qry_Count_Sum_Total] for each of the eleven queries.

I look forward to your help.

Timothy
PSAssm-2013-0827-1257.accdb
0
Question by:TimothyA
• 20
• 14
• 2
• +2

LVL 21

Expert Comment

ID: 39443035
what you can do with this error is to build several simple queries and make a query that polls those simple queries on top
0

LVL 74

Expert Comment

ID: 39443232
"Query is too complex"
I'll say...
;-)

When you have queries based on queries, this can happen.

Another option is to use subqueries...

But in your case you seem to need values from a large number of tables...
You also seem to be using a lot of "Cartesian" queries, so I am not quite sure I understand what you are after...

Lets see if an Expert more versed in SQL will chime in...

JeffCoachman
0

Author Comment

ID: 39443235
Oleggold,

Thanks for your quick reply.  I definitely need to get this behind me.

I split the "Qry_Count_S_Total" in half and each half works fine.  However, the minute I add New "Qry_Count_S_Total_06_11" to a new query "Qry_Cat_Total" with "Qry_Count_S_Total_01_05" the dreaded "Query is too complex." demon returns.

See attached database.

Any more ideas?

Timothy
PSAssm-2013-0827-1430.accdb
0

Author Comment

ID: 39443260
JeffCoachman,

I am at the 23.5 hour of needing this database complete.  I am open to a solution.

Is their anything you would suggest I try?

I look forward to hearing from an "Expert".

Timothy
0

LVL 74

Expert Comment

ID: 39443355
<I am at the 23.5 hour of needing this database complete.  I am open to a solution.>
I suggest that you link this Q to the SQL Syntax zone.
Then spill your guts on what it is exactly that you are trying to do here...
(why so many tables are involved, why so many Cartesian joins are there, ...ect)

Perhaps someone in the SQL  topic area can help streamline this whole thing...

;-)

JeffCoachman
0

LVL 31

Expert Comment

ID: 39443612
When I get this message, I take a subset of the queries, create a make-table query based on them, and then use the resulting table with the remaining queries.  You can run the make-table query in code, if needed.
0

Author Comment

ID: 39443654
Helen,

Thanks for your input.  It is greatly appreciated.

I in way over my head.

I have yet to used code except as in Access simple: select, from, where.  Is it complicated to automate update/make table for use by the post query each time the post query is run?

Timothy
0

LVL 31

Expert Comment

ID: 39443755
No, it is quite simple.  You could even do it with a macro, if you prefer.  If you have a button on a menu to open a report based on the final query, for example, this code would run a make-table query before opening the report:

``````DoCmd.OpenQuery "qmakMonthlyResults"
DoCmd.OpenReport "rptMonthlyResults", acPreview
``````
0

Author Comment

ID: 39443811
Jeff,

I will do my best to spill my guts on the calculation requirements.

01,   each PS has 11 independent categories, not to mention visit date and site location.
02,   each categories has approximate 10 numeric score subcategories.
03,   all PSs are not equal.
04,   some PSs do not have all 11 categories and many  do not have some of the subcategories.
05,   categories are weighted by "a may change weight".
06,   the numerical score for each PS each category equals (average of used subcategories*weight/(the sum of all subcategories used in all categories)
07,   each PS final score = sum of the category scores.
08,   there are internal independent subcategory calculations that have not been any issue.
09,   I must provide a PS by PS report with category scores and final score.

I started out with 11 tables originally, since their was no duplication of data, I brought them together.  This worked fine until I tried to get the queries to work.  I then separated the data back into 11 tables.  I spit the data back into 11 tables after current data entry  period was complete.  At this time I have both database structures with current data.

Any suggestions will be greatly appreciated.
0

LVL 29

Expert Comment

ID: 39444298
First off, you forgot to revise the 3rd field in your queries 2 to 11.  They should have just been Category_Weight rather than the SELECT statement you had in there.  I took care of that.

Secondly, I revised Qry_01_BS_Count_Sum and took out all the unnecessary calculated fields you had.  You need to go through queries 2 to 11 and do likewise following the Count and Sum formulas I used.

Fixing these queries will fix your problem.
PSAssm-2013-0827-1257.accdb
0

Author Comment

ID: 39445568
IrogSinta and Helen:

Thanks so much for your replies.  You help is greatly appreciated.

I will begin by implementing your suggestions, IrogSinta, and then go from their.

Helen, I hope I do not have need of your suggestion after making these modifications.

Thanks to both of you again.

Timothy
0

Author Comment

ID: 39445948
IrogSinta,

Please look into "Qry_01_BS_Count_Sum" .  The third from last field "BS_Cat_Sum:" does not include a total for records that have any null or empty values.

What is the least calculation around this issue?

Timothy
0

LVL 29

Expert Comment

ID: 39445975
If it's null, NZ function used in this calculation makes it 0.
0

LVL 29

Expert Comment

ID: 39446111
We're you getting different numbers for that field between my formula and yours?  It looked fine when I compared a few record last night.
0

Author Comment

ID: 39450446
IrogSinta , Helen, and all:

Attached find my latest version of the of PS database.   I have
01,  Reworked all the data so their are now not null or empty fields that will be used in calculations.
02,  I have simplified the calculation permitted by only having one type field.
03,  "Qry_Count_S_Total_01_05" and "Qry_Count_S_Total_06_11" work quickly and fine.
04,  The "Query is too complex." troll still shows up under the bridge of success any time I try to bring the data under on query.
05,  I am correct in understanding that the query that is too complexed is the 11 table Inner Join?
06,  What do you see as the best cross this bridge?
07,  Is their other options besides making a table from the queries?

I hope to hear  from you shortly, as I hope produce the final report tomorrow.

Thanks again for all your help.

Timothy
PSAssm-2013-0829-16051.accdb
0

LVL 29

Expert Comment

ID: 39450565
If your intention is just to use this total in a form, an alternative would be to use DLookup in your code like so:

Me.txtTotal = DLookup("[BS_Cat_Sum]", "Qry_01_BS_Count_Sum") + _
DLookup("[WS_Cat_Sum]", "Qry_02_WWS_Count_Sum") + _
DLookup("[WP_Cat_Sum]", "Qry_03_WP_Count_Sum") + _
DLookup("[Pipe_Cat_Sum]", "Qry_04_Pipe_Count_Sum") + _
DLookup("[C_Cat_Sum]", "Qry_05_C_Count_Sum")
0

Author Comment

ID: 39452277
IrogSinta,

I just need to the  "11 query sums"' someplace, total these query sums, use the "total query sum" against each  of the "11 query sum"s, and generate a report showing calculation results.

Timothy
0

LVL 29

Expert Comment

ID: 39452569
Sounds like you should do this in code.  Something similar to this:
``````Dim intTotal as Integer
Dim sumBsCat as Integer
Dim sumWsCat as Integer
Dim sumWpCat As Integer
etc...

sumBsCat = DLookup("[BS_Cat_Sum]", "Qry_01_BS_Count_Sum")
sumWsCat = DLookup("[WS_Cat_Sum]", "Qry_02_WWS_Count_Sum")
sumWpCat = DLookup("[WP_Cat_Sum]", "Qry_03_WP_Count_Sum")
etc...

intTotal = sumBsCat + sumWsCat + sumWpCat + etc...

resultName1 = sumBsCat / intTotal
resultName2 = sumWsCat / intTotal
etc...
``````
I don't know what your report will look like but you may need to append these variables to a temporary table that will then be used as the RecordSource for your report
0

Author Comment

ID: 39452776
IrogSinta,

To show my complete ignorance, I do not even know the right place to enter this code.

I am definitely willing to learn.  Your patience is appreciated and guidance is welcomed.

Timothy
0

LVL 29

Expert Comment

ID: 39452806
Can you give a sample of what this report will look like?
0

Author Comment

ID: 39452960
IrogSinta,

I do not yet have a sample.  I attached a file that shows the heart of the report.  I hope will help you understand the requirement.

Timothy
PartialReport.pdf
0

LVL 29

Expert Comment

ID: 39453001
I'll have a look at it tonight.
0

Author Comment

ID: 39453329
Thanks
0

Author Comment

ID: 39453943
IrogSinta,

I have been able to obtain the results I need from a query generated from two separate intermediate queries to tables and then querying these new tables.  I have no idea if this was the best way.  It is certainly fraught with issues (The first two to show are 1. update sequence will have to be automated (I have no idea how to begin) and 2. I have not been able to find out how to sort the "finalscore" without running into an overflow block.)

I imagine I will be able to generate the final report by PS over the weekend.

Thanks so much for your help thus far.

I am still very much open to a better way.

Timothy
PSAssm-2013-0830-1650.accdb
0

LVL 29

Expert Comment

ID: 39454423
I don't follow your pdf.  Are you supposed to have 421 pages, one for each of the Master ID's in the tblPS_Master table?
0

Author Comment

ID: 39454864
IrogSinta,

One page per Master ID is the hope.

Thanks for continuing to work with me.

Timothy
0

LVL 29

Expert Comment

ID: 39454945
Right now you have 421 Master IDs in the table. I just want to be sure that this is what you want. Having over 400 pages print out seems rather unorthodox.
0

Author Comment

ID: 39455192
IrogSinta,

I whole hardly agree 400 plus pages is unorthodox.  However, that is just the beginning, once the access report is generated in “finaScore:” it will be printed to a pdf file, then following each PS-Master page 2 to 10 photos will be added.  I have never worked with a file with this many images, but that is the current goal.

You can see why it is important to sort by “FinalScore”.

PS:  if there is room on each PS_Master page other table information will be added, but I did not see that as a problem.  No more calculation will be required.

Timothy
0

LVL 29

Expert Comment

ID: 39455841
I really don't understand the formulas in your pdf.  How exactly are PS_Master Final Score and 01_BS Final Score calculated.  Can you post something clearer that uses the current nomenclature you used in your queries?
0

Author Comment

ID: 39456292
IrogSinta,

Sorry I was not clearer.

I will send you a sample calculation, tomorrow.

It is Sunday today.

Have a great day.

Timothy
0

Author Comment

ID: 39458497
IrogSinta,

Ok here goes:
1.      Comments revere to PSAssm_20130830_1650.accdbl in particular.
2.      PS_Master FinalScore refer to each PS_ID.
3.      “01_BS_Final Score”(I do not recall this term exactly) would = ((BS_Cat_S/Count)*W)/CatTotal
a.      Where
i.      BS_Cat_S/Count = BS_Category_Sum/Category count for each PS
ii.      BS_Cat_S = sum of all values in BS category perPS_ID
iii.      Count = Count of >0 values for each BS category per PS_ID
iv.      W = Category Weight from tblWeight table.
v.      CatTotal = Category total =Sum of all Category totals for each PS.  That is Sum of each 01 through 11 categories for each PS.  This value is the trouble make.  It has to sum up all 11 Category_Sums before the rest of the calculations can be completed.
4.      PS_Master Final Score is simply the sum of all 11 Category Final Scores.

I hope this helps.  I could send you an early version of the excel spread sheet, if you think that would help.

As always you help is greatly appreciated.  Have a great week.

Timothy
0

LVL 29

Expert Comment

ID: 39458735
I'll have a look again tonight. Maybe the spreadsheet would help too.  You can upload it unless you think it would add to the confusion.
0

Author Comment

ID: 39458960
IrogSinta,

Thanks for you continued review of my issues.

I have attached an excel sheet called "CalForm.xlsx".  I left only two PS_Master_IDs in it.  I hope otherwise it is not too  hard to follow.  This form was generated an earlier prototype  that is not in current.  That is why int does not follow the database more closely.

Thanks again.

Timothy
CalForm.xlsx
0

LVL 29

Expert Comment

ID: 39459525
Do you use queries 1 to 11 as a recordsource or rowsource for any form or control?  Or within another query that you definitely intend to use? I just want to know if these could be changed into Append/Update queries.  My idea is to create a temp table that would hold all the [Category_Sum/Category_count] fields from your queries along with a column that sums up all the category totals.  This temp table will then be used as the recordsource for your report.
0

Author Comment

ID: 39460426
IrogSinta,

I do use queries 1 - 11 in the main query that populates the query for the frmTotal.  This is the location of entry of all information.

I have attached the most recent edition of the database I have been working on.  I have created two queries:  "Qry_Count_S_Total_01_05"  and "Qry_Count_S_Total_06_11".

I have used versions of these two queries to generate two tables: "tblSum_01_05" and "tblSum_01_05".  I then have generated a "QryCalTotal" from these tables.

I am working now to generate a main report with sub-reports.  You will see the
beginning of this in Reports folder.

I still have not figured:
1. - If this is the most advantageous way to proceed.
2. - How to automate when more PS_Master_IDs are added.
3. - New issue:  I have disabled date tracking because it has introduced multiple PS_Master_IDs into queries.  I have not yet developed the system to handle the issue.

I am having to begin work on a new database for other Assets.  These Assets will have different Categories from existing 01 - 11.  So I reinforce my openness to design a more stream line system.

Thanks for your continued help and suggestions.

Timoty
PSAssm-2013-0902-2158.accdb
0

LVL 29

Expert Comment

ID: 39466105
I revised the reports here.  Take a look at the RecordSources for SubRpt_01BS and SubRpt_02WWS.  I believe you only need the tables themselves as the recordsources.

Then in the report RptQryCom, note the change in the Master/Child fields of your subreport controls.

And lastly, I changed the RecordSource of the main report to those tables/queries that are referenced by the bound controls.  I left the table tblPS_Master in the recordsource but since it doesn't have any criteria and you don't have any controls in your report that need it, you can remove that as well.

To automate when you add more PS_Master_IDs, you would just change Qry_Count_S_Total_01_05 and Qry_Count_S_Total_06_11 into Append queries, and add a a bit of code to your command button that opens the report.  The code should empty your temp tables, run your append queries, then open the report.  If you need help with this part, just open up another question since this topic is different from your original question.
PSAssm-2013-0830-1650.accdb
0

Author Comment

ID: 39466892
IrogSinta,

Thank you so much for continuing to look at this for me.

Unfortunately, you it seems the file attached  is not the file you worked on.  I believe you must have worked on "PSAssm_2013_0902_2158" sent on Sept 3rd.

Have a great day.

I look forward to hearing back from you.

Timothy
0

LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 39467098
Oops, here you go.
PSAssm-2013-0902-2158.accdb
0

Author Closing Comment

ID: 39467620
Thanks for continuing to work with me.

Great help.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.