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

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?

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

>>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.

:)

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;

Select allOpen in new window

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.

Sorry that may seem confusing but each query has some sort of calculated data.

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

WarCrimes,
 That returns a syntax error in GroupBy clause

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.

Oh, duh.  There is a comma after the last field in the Group By, just remove it.

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.

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?

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.

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;

Select allOpen in new window

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
;

Select allOpen in new window

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.

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