Query is too complex.

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
TimothyAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oleggoldCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
"Query is too complex"
I'll say...
;-)
To Complex...
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
TimothyAAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TimothyAAuthor Commented:
JeffCoachman,

Thanks so much for your reply.

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
Jeffrey CoachmanMIS LiasonCommented:
<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
Helen FeddemaCommented:
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
TimothyAAuthor Commented:
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?

Thanks again for your comment.

Timothy
0
Helen FeddemaCommented:
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

Open in new window

0
TimothyAAuthor Commented:
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
IrogSintaCommented:
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
TimothyAAuthor Commented:
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
TimothyAAuthor Commented:
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?

Thanks for your help.

Timothy
0
IrogSintaCommented:
If it's null, NZ function used in this calculation makes it 0.
0
IrogSintaCommented:
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
TimothyAAuthor Commented:
IrogSinta , Helen, and all:

Thanks for your past help.

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
IrogSintaCommented:
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
TimothyAAuthor Commented:
IrogSinta,

Thanks for your quick reply.

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.

While waiting for your next reply, I will try your suggestion.

Thanks for your help.

Timothy
0
IrogSintaCommented:
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...

Open in new window

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
TimothyAAuthor Commented:
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
IrogSintaCommented:
Can you give a sample of what this report will look like?
0
TimothyAAuthor Commented:
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.

Thanks for your continued help.

Timothy
PartialReport.pdf
0
IrogSintaCommented:
I'll have a look at it tonight.
0
TimothyAAuthor Commented:
Thanks
0
TimothyAAuthor Commented:
IrogSinta,

Attached find my latest version.

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
IrogSintaCommented:
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
TimothyAAuthor Commented:
IrogSinta,

One page per Master ID is the hope.

Thanks for continuing to work with me.

Timothy
0
IrogSintaCommented:
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
TimothyAAuthor Commented:
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”.

Thanks for your continued help.

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
IrogSintaCommented:
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
TimothyAAuthor Commented:
IrogSinta,

Sorry I was not clearer.  

I will send you a sample calculation, tomorrow.

It is Sunday today.

Have a great day.

Timothy
0
TimothyAAuthor Commented:
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
IrogSintaCommented:
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
TimothyAAuthor Commented:
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
IrogSintaCommented:
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
TimothyAAuthor Commented:
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
IrogSintaCommented:
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
TimothyAAuthor Commented:
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
IrogSintaCommented:
Oops, here you go.
PSAssm-2013-0902-2158.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimothyAAuthor Commented:
Thanks for continuing to work with me.

Great help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.