John,

I added square brackets around [Total Calls], then added closing brackets
around ([Pt Calls Out]) and ([Pt Calls In]), also removed extra [] around the
[Pt Calls Out 3+].

The query runs, but asks for input for every field.

"John Spencer" wrote:

You can make your current query a bit easier to understand and a bit faster
with some changes.

First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause
instead of in the Having clause. This should increase the efficiency of the
query. You use the HAVING clause when you want to filter on the basis of the
aggregated value (Count greater then 5, or Sum is negative, etc). You use
WHERE clause when you want to filter the records that are going to be
aggregated (usually fields that you are going to GROUP BY).

Second: Assuming that all directions that start with "IN" are what you want
for the In calls (that is you don't have an additional direction "In-XX" that
you want to exclude) you can use LIKE "IN*" to filter the direction.

SELECT DailyCalls.Department, DailyCalls.Name
, Count(DailyCalls.Name) AS [Total Calls],
Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+],
Abs(Sum(CallDirection="OUT")) AS [Out Calls],

Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls],

Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out],

Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In],

Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899
0:3:0#))/Count([Name]) AS [Pt Calls Out 3+],

Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899
0:3:0#))/Count([Name]) AS [Pt Calls In 3+]

FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
AND DailyCalls.Name"emptoexclude"
GROUP BY DailyCalls.Department, DailyCalls.Name

Using that query as a basis (I am using Q as an alias for the query name) to
shorten the typing. This is a simple thing but it can make life easier. If
I've correctly formed the query, the only thing you need to do is replace
"TheSavedQuery" with the actual name of your query.

SELECT Q.Department, Q.Name
, Avg(Total Calls] as AvgCallCount
, Avg([Pt Calls Out] as AvgOut
, Avg([Pt Calls In] as AvgIn
, Avg([[Pt Calls Out 3+]]) as AvgOutOver3
, Avg([Pt Calls In 3+]) as AvgInOver3
FROM [TheSavedQuery] as Q
GROUP BY Q.Department, Q.Name

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SSi308 wrote:
The end result should be average number of calls per day by employee for a
specific time period. The first query asks for beginning and ending date. The
averages should reflect the data meeting the criteria in the first query.
Hope that makes sense. Not sure if this will help, but here is the SQL view
of the first query:

SNIP
.