QueryHelp

N

Nero

HEllo.... in the below SQL statement, please advise why i'm getting error:
"You TRied to exeute a query that does not include the specified expression
'ScheduledTime/Targets.AHTtarget' as part of an aggregate function."

SELECT [Agent_ Details].AgentId, [Agent_ Details].Supervisor, [Agent_
Details].Region, [Agent_ Details].JobTitle, Format$(ActivityTbl.Date,'mmmm
yyyy') AS [Date By Month], Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls
FROM (([Agent_ Details] INNER JOIN AgentDailySchedTime ON [Agent_
Details].AgentId = AgentDailySchedTime.AgentId) INNER JOIN ActivityTbl ON
[Agent_ Details].AgentId = ActivityTbl.AgentId) INNER JOIN Targets ON [Agent_
Details].JobTitle = Targets.ServiceArea
GROUP BY [Agent_ Details].AgentId, [Agent_ Details].Supervisor, [Agent_
Details].Region, [Agent_ Details].JobTitle, Format$(ActivityTbl.Date,'mmmm
yyyy'), Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1;

Thank you for your time.
 
N

Neil Sunderland

Nero said:
HEllo.... in the below SQL statement, please advise why i'm getting error:
"You TRied to exeute a query that does not include the specified expression
'ScheduledTime/Targets.AHTtarget' as part of an aggregate function."
Click to expand...

It helps if you paste the code into Notepad (or similar), and tidy it
up a bit:

SELECT
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date By Month],
Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls

FROM
(([Agent_ Details]
INNER JOIN
AgentDailySchedTime
ON
[Agent_ Details].AgentId = AgentDailySchedTime.AgentId)
INNER JOIN
ActivityTbl
ON [Agent_ Details].AgentId = ActivityTbl.AgentId)
INNER JOIN Targets ON [Agent_ Details].JobTitle = Targets.ServiceArea

GROUP BY
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy'),
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1;

You need to add the last line in the SELECT clause to the GROUP BY
clause. You may also need to delete the final line in the GROUP BY -
"Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1" - as it
doesn't appear in the SELECT clause.
 
N

Nero

Thanks for your reply Neil.... I tried that, but i still get the same error.

Neil Sunderland said:
Nero said:
HEllo.... in the below SQL statement, please advise why i'm getting error:
"You TRied to exeute a query that does not include the specified expression
'ScheduledTime/Targets.AHTtarget' as part of an aggregate function."
Click to expand...

It helps if you paste the code into Notepad (or similar), and tidy it
up a bit:

SELECT
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date By Month],
Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls

FROM
(([Agent_ Details]
INNER JOIN
AgentDailySchedTime
ON
[Agent_ Details].AgentId = AgentDailySchedTime.AgentId)
INNER JOIN
ActivityTbl
ON [Agent_ Details].AgentId = ActivityTbl.AgentId)
INNER JOIN Targets ON [Agent_ Details].JobTitle = Targets.ServiceArea

GROUP BY
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy'),
Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1;

You need to add the last line in the SELECT clause to the GROUP BY
clause. You may also need to delete the final line in the GROUP BY -
"Year(ActivityTbl.Date)*12+DatePart('m',ActivityTbl.Date)-1" - as it
doesn't appear in the SELECT clause.

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
N

Neil Sunderland

Nero said:
Thanks for your reply Neil.... I tried that, but i still get the same error.
Click to expand...

You didn't just cut and paste the reformatted query, did you? It was
just a tidied-up version of your original, without the corrections to
the GROUP BY clause.

Try pasting this in instead:

SELECT
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date By Month],
Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls

FROM
(([Agent_ Details]
INNER JOIN
AgentDailySchedTime
ON
[Agent_ Details].AgentId = AgentDailySchedTime.AgentId)
INNER JOIN
ActivityTbl
ON [Agent_ Details].AgentId = ActivityTbl.AgentId)
INNER JOIN Targets ON [Agent_ Details].JobTitle = Targets.ServiceArea

GROUP BY
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy'),
ScheduledTime/Targets.AHTTarget

(By the way, is that table /really/ called
Agent|underscore|space|Details? Yuk!)
 
N

Nero

No Neil... I didn't just copy and paste, It doesn't take a genius to see a
tidied up version of the original query. If you're going to respond to
assistance for help, you might want to try and be a little less
condescending, people that come on here want to learn, not be made fools of.
Thanks for your help anyway, which by the way doesnt really help.

Neil Sunderland said:
Nero said:
Thanks for your reply Neil.... I tried that, but i still get the same error.
Click to expand...

You didn't just cut and paste the reformatted query, did you? It was
just a tidied-up version of your original, without the corrections to
the GROUP BY clause.

Try pasting this in instead:

SELECT
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date By Month],
Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls

FROM
(([Agent_ Details]
INNER JOIN
AgentDailySchedTime
ON
[Agent_ Details].AgentId = AgentDailySchedTime.AgentId)
INNER JOIN
ActivityTbl
ON [Agent_ Details].AgentId = ActivityTbl.AgentId)
INNER JOIN Targets ON [Agent_ Details].JobTitle = Targets.ServiceArea

GROUP BY
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy'),
ScheduledTime/Targets.AHTTarget

(By the way, is that table /really/ called
Agent|underscore|space|Details? Yuk!)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
N

Neil Sunderland

Nero said:
No Neil... I didn't just copy and paste, It doesn't take a genius to see a
tidied up version of the original query.
Click to expand...

Yes, well you did say you were getting exactly the same error, so it's
not entirely unreasonable to assume that the initial conditions
haven't actually changed...

As it happens, I think I've now spotted the problem. In the SELECT
clause you have:
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls

You need to either amend the second line to read
AgentDailySchedTime.ScheduledTime/Targets.AHTTarget as TargetCalls
This line would also go into the GROUP BY clause.


However, if you were intending TargetCalls to be this:
Sum(AgentDailySchedTime.ScheduledTime)/Targets.AHTTarget
as TargetCalls
then you can't: it would have to appear in the GROUP BY clause, and
you're not allowed to use an aggregate in a GROUP BY.

In this case, you'll have to split your query into two:

SELECT
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy') AS [Date By Month],
Sum(ActivityTbl.ActualCalls) AS ActualCalls,
Avg(ActivityTbl.ActualAht) AS ActualAht,
Sum(AgentDailySchedTime.ScheduledTime) AS ScheduledTime

FROM
([Agent_ Details]
INNER JOIN
AgentDailySchedTime
ON [Agent_ Details].AgentId = AgentDailySchedTime.AgentId)
INNER JOIN
ActivityTbl
ON [Agent_ Details].AgentId = ActivityTbl.AgentId

GROUP BY
[Agent_ Details].AgentId,
[Agent_ Details].Supervisor,
[Agent_ Details].Region,
[Agent_ Details].JobTitle,
Format$(ActivityTbl.Date,'mmmm yyyy')

Provided that the above query works, your second query would something
like:

SELECT
AgentId, Supervisor, Region, JobTitle, [Date By Month],
ActualCalls, ActualAht, ScheduledTime,
ScheduledTime/Targets.AHTTarget as TargetCalls
FROM
qryAgents -- nb: this needs to be the name of your query!
INNER JOIN
Targets ON qryAgents.JobTitle = Targets.ServiceArea
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QueryHelp 4
Where count = 0 8

Top