Solved

Why am I getting an error message that this query is too complex?

Posted on 2011-02-25
18
264 Views
Last Modified: 2012-05-11
I am trying to fix a database I inherited.  The old developer ran queries off of other queries to do equations.  I keep getting errors that this query is too complex or the text is wrong.  I am trying to get this query to run and it will work fine if I don't do a GROUP BY but I need to sum all the data from everday of the month to a single line for the month.
SELECT qry009a_CSR_Scorecard_Data.Organization, qry009a_CSR_Scorecard_Data.TeamLead, qry009a_CSR_Scorecard_Data.AgentName, qry009a_CSR_Scorecard_Data.ACDID, qry009a_CSR_Scorecard_Data.Period, qry009a_CSR_Scorecard_Data.Calls, qry009a_CSR_Scorecard_Data.PaidHours, qry009a_CSR_Scorecard_Data.[Avg ACW Time], qry009a_CSR_Scorecard_Data.[Avg Handle Time], qry009a_CSR_Scorecard_Data.[Calls Per Paid Hour], qry009a_CSR_Scorecard_Data.QAScores, qry006_Adherence_Report.TotalTimeAdhering
FROM qry009a_CSR_Scorecard_Data INNER JOIN qry006_Adherence_Report ON qry009a_CSR_Scorecard_Data.ACDID = qry006_Adherence_Report.ACDID
GROUP BY qry009a_CSR_Scorecard_Data.Organization, qry009a_CSR_Scorecard_Data.TeamLead, qry009a_CSR_Scorecard_Data.AgentName, qry009a_CSR_Scorecard_Data.ACDID, qry009a_CSR_Scorecard_Data.Period, qry009a_CSR_Scorecard_Data.Calls, qry009a_CSR_Scorecard_Data.PaidHours, qry009a_CSR_Scorecard_Data.[Avg ACW Time], qry009a_CSR_Scorecard_Data.[Avg Handle Time], qry009a_CSR_Scorecard_Data.[Calls Per Paid Hour], qry009a_CSR_Scorecard_Data.QAScores, qry006_Adherence_Report.TotalTimeAdhering
ORDER BY qry009a_CSR_Scorecard_Data.Organization, qry009a_CSR_Scorecard_Data.TeamLead, qry009a_CSR_Scorecard_Data.AgentName;

Open in new window

0
Comment
Question by:MattDylan
  • 10
  • 6
  • 2
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
When you use another query as a source of your query, Access is building up the whole sequence using subqueries in the background.  If the underlying queries are complex, or they themselves are leveraging still more queries as their sources, then that could be your reason.

If you convert qry009a_CSR_Scorecard_Data and qry006_Adherence_Report into make-table queries, and then rewrite the query above to refer to the new tables thus created, does the rewritten query run correctly?
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Matt,

What are the fields that need to be collapsed?  You really shouldn't need to Group By every field in your table.  Check out the following link.

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

Try to identify which Fields actually need grouped and only Group By those fields.

WC
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>You really shouldn't need to Group By every field in your table

Unless the Asker is trying to suppress duplicate rows in the output.  In which case I suppose you could use SELECT DISTINCT, but then the ORDER BY clause would have to include every column in the SELECT clause.

:)
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
You said you wanted to sum things, so I am taking a guess you might be looking for something like this.  Does this still give the too complex error?

WC
SELECT 	qry009a_CSR_Scorecard_Data.Organization, 
				qry009a_CSR_Scorecard_Data.TeamLead, 
				qry009a_CSR_Scorecard_Data.AgentName, 
				qry009a_CSR_Scorecard_Data.ACDID, 
				qry009a_CSR_Scorecard_Data.Period, 
				Sum(qry009a_CSR_Scorecard_Data.Calls) as Month_Calls, 
				Sum(qry009a_CSR_Scorecard_Data.PaidHours) as Month_PaidHours, 
				Sum(qry009a_CSR_Scorecard_Data.[Avg ACW Time]) as Month_Avg_ACW_Time, 
				Sum(qry009a_CSR_Scorecard_Data.[Avg Handle Time]) as Month_Avg_Handle_Time, 
				Sum(qry009a_CSR_Scorecard_Data.[Calls Per Paid Hour]) as Month_Calls_Per_Paid_Hour, 
				Sum(qry009a_CSR_Scorecard_Data.QAScores) as Month_QAScores, 
				Sum(qry006_Adherence_Report.TotalTimeAdhering) as Month_TotalTimeAdhering
FROM qry009a_CSR_Scorecard_Data 
	INNER JOIN 
		qry006_Adherence_Report 
	ON 
		qry009a_CSR_Scorecard_Data.ACDID = qry006_Adherence_Report.ACDID
GROUP BY 	qry009a_CSR_Scorecard_Data.Organization, 
					qry009a_CSR_Scorecard_Data.TeamLead, 
					qry009a_CSR_Scorecard_Data.AgentName, 
					qry009a_CSR_Scorecard_Data.ACDID, 
					qry009a_CSR_Scorecard_Data.Period, 
ORDER BY qry009a_CSR_Scorecard_Data.Organization, qry009a_CSR_Scorecard_Data.TeamLead, qry009a_CSR_Scorecard_Data.AgentName;

Open in new window

0
 

Author Comment

by:MattDylan
Comment Utility
It wont let me even attempt to run the query unless they are all groupby.  Every query in this db is based off another so I have been going back through trying to make each query more versatile.  The Agent Name field is where the GroupBy actually goes so I can Sum them as a single row for the month instead of a single row for each day of the month.  I have another query that will do the calculation perfectly but it is missing all this other data and it does not include a Join.  Once I add the Join to bring in the other query with the calculated data is when the error comes up.  Or if I don't group the days of the month down to one line the query will run but it is not correct.
0
 

Author Comment

by:MattDylan
Comment Utility
Sorry that may seem confusing but each query has some sort of calculated data.
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Lol, I suppose you would want to use an AVG function for the averages, not a SUM.  Anyways, I think you get my point.
0
 

Author Comment

by:MattDylan
Comment Utility
WarCrimes,
 That returns a syntax error in GroupBy clause
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Does it specify where or what or is it just a generic error?

There could be issues with the carraige returns and tabs.  Try removing them.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Oh, duh.  There is a comma after the last field in the Group By, just remove it.
0
 

Author Comment

by:MattDylan
Comment Utility
All the calculations are being done in a different query and all those queries are being joined to make this one.  The problem seems to be coming from the TotalTimeAdhering column that is a percentage calculated from the adherencereport query.
0
 

Author Comment

by:MattDylan
Comment Utility
I have an idea, I originally tried to just add an expression to this query.  Maybe there is another way to get this to work.

Adhering: [Ad]/[TotalTime]
expression

This only returns a value if I make it AVG instead of an Expression.  But this is suppose to be a percentage and as AVG it only returns a 0.0 number.  I need it to atleast ruturn a 00.00.  Would this be an easier fix?
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
If you are doing the calculations in another query, why the need for a Group By?  Shouldn't they already be one row?  If not, then you are doing a calculation in this query, aggregation.

If you simply want to append the TotalTimeAdhering for a month to a single line for each of the other calculations, you should aggregate those down to a single line in a separate query first, then do the Join.

If that is what you are doing here and how the input tables are structured, then there has clearly been a misunderstanding.  It sounded to me like your table was daily records and you needed to aggregate them to monthly ones.  Thus the need for the Group By and aggregate functions.
0
 
LVL 18

Accepted Solution

by:
WarCrimes earned 500 total points
Comment Utility
That is only formatting.  It isn't necessarily what is stored in the database, unless when you calculated it you used a round function to round to 1 decimal place.  If so, then just change that to round to 2 decimals.

If you are saying you need a leading 0 for percentages less than 10% then you could cast the value to a string and pad the value.  Depending on what you are using, the syntax is slightly different for this.
http://www.sqlusa.com/bestpractices2005/padleadingzeros/

WC
0
 

Author Comment

by:MattDylan
Comment Utility
Sorry I am terrible at explaining these issues like this.  I have figured out how to eliminate the errors and run the query but I know there should be 342 records returned but I am only getting 219 returned. Why do you think these other rows are being eliminated?  Some have 0 values I know but not on the entire row just certain fields.
SELECT qry998_Agent_List.Organization, qry998_Agent_List.TeamLead, qry998_Agent_List.AgentName, qry998_Agent_List.ACDID, qry998_Agent_List.Period, qry005_Organization_Report.Calls, qry007_Paid_Hours.PaidHours, qry005_Organization_Report.[Avg ACD Time], qry005_Organization_Report.[Avg ACW Time], qry005_Organization_Report.[Avg Handle Time], Sum([Calls])/Sum([PaidHours]) AS [Calls Per Paid Hour], qry008_QA_Scores.QA, Avg([Ad]/[TotalTime]) AS Adhering
FROM (qry006_Adherence_Report RIGHT JOIN (qry007_Paid_Hours RIGHT JOIN (qry005_Organization_Report RIGHT JOIN qry998_Agent_List ON (qry005_Organization_Report.Period = qry998_Agent_List.Period) AND (qry005_Organization_Report.ACDID = qry998_Agent_List.ACDID)) ON (qry007_Paid_Hours.Period = qry998_Agent_List.Period) AND (qry007_Paid_Hours.ACDID = qry998_Agent_List.ACDID)) ON (qry006_Adherence_Report.ACDID = qry998_Agent_List.ACDID) AND (qry006_Adherence_Report.Period = qry998_Agent_List.Period)) INNER JOIN qry008_QA_Scores ON qry998_Agent_List.ACDID = qry008_QA_Scores.ACDID
GROUP BY qry998_Agent_List.Organization, qry998_Agent_List.TeamLead, qry998_Agent_List.AgentName, qry998_Agent_List.ACDID, qry998_Agent_List.Period, qry005_Organization_Report.Calls, qry007_Paid_Hours.PaidHours, qry005_Organization_Report.[Avg ACD Time], qry005_Organization_Report.[Avg ACW Time], qry005_Organization_Report.[Avg Handle Time], qry008_QA_Scores.QA
HAVING (((qry998_Agent_List.Organization) Like [Forms]![Control]![cmb_Organization] & "*") AND ((qry998_Agent_List.AgentName) Like [Forms]![Control]![cmb_CSRs] & "*"))
ORDER BY qry998_Agent_List.Organization, qry998_Agent_List.TeamLead, qry998_Agent_List.AgentName, qry998_Agent_List.Period;

Open in new window

0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
As your original question stated you need to sum all the data to a single line for a month, I still suggest you do this aggregation first, then do the join with the percentage in another query.

You could make the aggregation a subquery.  If the TotalTimeAdhering is already a monthly total and you simply are trying to append this to the other totals, which need to be aggregated first, try the following.  Change the aggregate functions as you need to.

WC
SELECT MonthlyTotals.*, qry006_Adherence_Report.TotalTimeAdhering
FROM
(SELECT qry009a_CSR_Scorecard_Data.Organization, 
	qry009a_CSR_Scorecard_Data.TeamLead, 
	qry009a_CSR_Scorecard_Data.AgentName, 
	qry009a_CSR_Scorecard_Data.ACDID, 
	qry009a_CSR_Scorecard_Data.Period, 
	Sum(qry009a_CSR_Scorecard_Data.Calls) as Month_Calls, 
	Sum(qry009a_CSR_Scorecard_Data.PaidHours) as Month_PaidHours, 
	Avg(qry009a_CSR_Scorecard_Data.[Avg ACW Time]) as Month_Avg_ACW_Time, 
	Avg(qry009a_CSR_Scorecard_Data.[Avg Handle Time]) as Month_Avg_Handle_Time, 
	Avg(qry009a_CSR_Scorecard_Data.[Calls Per Paid Hour]) as Month_Calls_Per_Paid_Hour, 
	Avg(qry009a_CSR_Scorecard_Data.QAScores) as Month_QAScores, 
FROM qry009a_CSR_Scorecard_Data 
GROUP BY qry009a_CSR_Scorecard_Data.Organization, 
	qry009a_CSR_Scorecard_Data.TeamLead, 
	qry009a_CSR_Scorecard_Data.AgentName, 
	qry009a_CSR_Scorecard_Data.ACDID, 
	qry009a_CSR_Scorecard_Data.Period 
) as MonthlyTotals
	INNER JOIN 
		qry006_Adherence_Report 
	ON 
		qry009a_CSR_Scorecard_Data.ACDID = qry006_Adherence_Report.ACDID
ORDER BY MonthlyTotals.Organization, MonthlyTotals.TeamLead, MonthlyTotals.AgentName
;

Open in new window

0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Ok,

Glad you have it working.  Well the only reason you would be getting a subset is from the this line

HAVING (((qry998_Agent_List.Organization) Like [Forms]![Control]![cmb_Organization] & "*") AND ((qry998_Agent_List.AgentName) Like [Forms]![Control]![cmb_CSRs] & "*"))

I must say I detest RIGHT JOIN.  Reversing the order of the tables you can use a LEFT JOIN.  Something I know you don't want to do.  I'm just saying if I am designing a query, I never use Right Join.  In SAS using PROC SQL it needs a coalesce() function to return the proper results.

I would look at the HAVING clause and see if that is your culprit.
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
Sorry, there is one other possibility

Your structure should keep all records from AgentList and the matching records from the other tables prior to the Inner Join.

Thus at the Inner Join step, you should have the # of records from AgentList on the left side of the Join (A) and the # of records from qry008 on the right side (B).  You're only going to get back the ones that are in both (C).  Make sure that the AgentList IDs are truly a subset of the qry008 if you expect to get them all back.  Otherwise an outer join such as a left join or full join is in order.

WC
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now