Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

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
MattDylan
Asked:
MattDylan
  • 10
  • 6
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
Patrick MatthewsCommented:
>>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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Cory VandenbergSenior Risk ManagerCommented:
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
 
MattDylanAuthor Commented:
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
 
MattDylanAuthor Commented:
Sorry that may seem confusing but each query has some sort of calculated data.
0
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
MattDylanAuthor Commented:
WarCrimes,
 That returns a syntax error in GroupBy clause
0
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
Oh, duh.  There is a comma after the last field in the Group By, just remove it.
0
 
MattDylanAuthor Commented:
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
 
MattDylanAuthor Commented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
MattDylanAuthor Commented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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
 
Cory VandenbergSenior Risk ManagerCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now