Need Help with Totals Query

  • Thread starter Thread starter SSi308
  • Start date Start date
S

SSi308

I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
 
Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


SSi308 said:
I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
 
Thank you for the help Daryl.
When I change the query as you suggested I get the following error:
Cannot have aggregate function in WHERE clause (DailyCalls.Date Between
[Start Date] And [End Date] And (DailyCalls.TimeOfDay>=#12/30/1899 10:0:0#
And DailyCalls.TimeOfDay <=#12/30/1899 12:0:0#) And Format([LengthOfCall],
#nn:ss")>="03:00" And Count([CallDirection])="OUT")

Daryl S said:
Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


SSi308 said:
I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
Click to expand...
 
If you change the WHERE to HAVING, then you should be OK.

--
Daryl S


SSi308 said:
Thank you for the help Daryl.
When I change the query as you suggested I get the following error:
Cannot have aggregate function in WHERE clause (DailyCalls.Date Between
[Start Date] And [End Date] And (DailyCalls.TimeOfDay>=#12/30/1899 10:0:0#
And DailyCalls.TimeOfDay <=#12/30/1899 12:0:0#) And Format([LengthOfCall],
#nn:ss")>="03:00" And Count([CallDirection])="OUT")

Daryl S said:
Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


SSi308 said:
I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
Click to expand...
Click to expand...
 
I tried changing the WHERE to HAVING
Now I get the error: This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression to variables.

Any ideas?

Daryl S said:
If you change the WHERE to HAVING, then you should be OK.

--
Daryl S


SSi308 said:
Thank you for the help Daryl.
When I change the query as you suggested I get the following error:
Cannot have aggregate function in WHERE clause (DailyCalls.Date Between
[Start Date] And [End Date] And (DailyCalls.TimeOfDay>=#12/30/1899 10:0:0#
And DailyCalls.TimeOfDay <=#12/30/1899 12:0:0#) And Format([LengthOfCall],
#nn:ss")>="03:00" And Count([CallDirection])="OUT")

Daryl S said:
Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


:

I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
Click to expand...
Click to expand...
 
Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.

--
Daryl S


SSi308 said:
I tried changing the WHERE to HAVING
Now I get the error: This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression to variables.

Any ideas?

Daryl S said:
If you change the WHERE to HAVING, then you should be OK.

--
Daryl S


SSi308 said:
Thank you for the help Daryl.
When I change the query as you suggested I get the following error:
Cannot have aggregate function in WHERE clause (DailyCalls.Date Between
[Start Date] And [End Date] And (DailyCalls.TimeOfDay>=#12/30/1899 10:0:0#
And DailyCalls.TimeOfDay <=#12/30/1899 12:0:0#) And Format([LengthOfCall],
#nn:ss")>="03:00" And Count([CallDirection])="OUT")

:

Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


:

I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
Click to expand...
Click to expand...
 
Changing the query returned the correct results for Out bound calls, how do I
get the rest of the totals, these should be by user not as a total for the
date?

My ultimate goal is to have a report by User for the date specified:
Total calls
Total calls over 3 minutes
Total outbound calls
Total inbound calls
And
Percentage of calls that are incoming
Percentage of calls that are over 3 minutes
Average daily outgoing calls (when running report for more than one day)
Average daily incoming calls (when running report for more than one day)

Daryl S said:
Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.

--
Daryl S


SSi308 said:
I tried changing the WHERE to HAVING
Now I get the error: This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric expression may contain too
many complicated elements. Try simplifying the expression to variables.

Any ideas?

Daryl S said:
If you change the WHERE to HAVING, then you should be OK.

--
Daryl S


:

Thank you for the help Daryl.
When I change the query as you suggested I get the following error:
Cannot have aggregate function in WHERE clause (DailyCalls.Date Between
[Start Date] And [End Date] And (DailyCalls.TimeOfDay>=#12/30/1899 10:0:0#
And DailyCalls.TimeOfDay <=#12/30/1899 12:0:0#) And Format([LengthOfCall],
#nn:ss")>="03:00" And Count([CallDirection])="OUT")

:

Lori -

You need a different query for totals than you do for details. For a count,
you need to remove all fields in the SELECT list that could be different
between the calls you want counted. For example, if you leave the
DailyCalls.ID field in the SELECT list, then each call will be listed, and
you will get a count of 1 for each call. You may, however want a count by
Department, in which case you would leave the Department in the SELECT list.
You may want several count queries depending on your grouping needs. The
most basic count would be something like this:

SELECT Count(DailyCalls.ID)
FROM DailyCalls
WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
(([CallDirection]="OUT"));

If you wanted a count by department, then it would look like this:

SELECT DailyCalls.Department, Count(DailyCalls.ID)
FROM DailyCalls
GROUP BY DailyCalls.Department
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
([CallDirection]="OUT")));

--
Daryl S


:

I have a table with daily call records. The table includes fields for:
Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed;
Locale; Length of Call.

I created a query that will show calls lasting longer than 3 minutes for a
given time period, for example calls between 10 AM and 12 PM. The query
prompts the user for date(s).

Now I need to add counts for: total calls; out-bound calls; and in-bound
calls by user.

I tried including a totals row in the query and adding a colum to count
outbound calls, but I get the error that "This expression is typed
incorrectly, or it is too complex to be evaluated. For example, a numberic
expression may contain too many complicated elements. Try simplifying the
expression by assigning parts of the expression to variables. I have the
added column as so:
Field - Outbound Calls: ([Call Direction]) Total: Count Criteria: "OUT"

Here is the SQL View:
SELECT DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss") AS Minutes
FROM DailyCalls
GROUP BY DailyCalls.ID, DailyCalls.Department, DailyCalls.Name,
DailyCalls.Date, DailyCalls.TimeOfDay, DailyCalls.CallDirection,
DailyCalls.NumberDialed, DailyCalls.Locale, DailyCalls.LengthOfCall,
Format([LengthOfCall],"nn:ss")
HAVING (((DailyCalls.Date) Between [Start Date] And [End Date]) AND
((DailyCalls.TimeOfDay)>=#12/30/1899 10:0:0# And
(DailyCalls.TimeOfDay)<=#12/30/1899 12:0:0#) AND
((Format([LengthOfCall],"nn:ss"))>="03:00") AND
((Count(([CallDirection])))="OUT"));

How do I get the count to work?
Thanks in advance for any help.

Lori
Click to expand...
Click to expand...
Click to expand...
 
SELECT DailyCalls.Department
, DailyCalls.Name
, Count([Name]) as TotalCalls
, Abs(Sum(LengthOfCall >= #00:03:00#)) as Over3Minutes
, Abs(Sum(CallDirection="OUT")) as OutboundCalls
, Abs(Sum(CallDirection="IN")) as InboundCalls
, Abs(Sum(CallDirection="OUT"))/Count([Name]) as PercentOut
, Abs(Sum(CallDirection="In"))/Count([Name]) as PercentIn
, Abs(Sum(LengthOfCall >= #00:03:00#))/Count([Name]) as PercentOver3
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name


Getting the daily average number of calls would have to be done in a separate
queries.

First query to get a count of calls per day
And then a second to use that to get the average of the count per day
SELECT DailyCalls.Department
, DailyCalls.Name
, DailyCalls.Date
, DailyCalls.CallDirection
, Count([Name]) as TotalCalls
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name, DailyCalls.Date,
DailyCalls.CallDirection

Now using that you get get the average
SELECT Department, [Name], CallDirection,
Average(TotalCalls) as AvgNumCalls
FROM TheSavedQuery
GROUP BY Department, [Name], CallDirection,


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Changing the query returned the correct results for Out bound calls, how do I
get the rest of the totals, these should be by user not as a total for the
date?

My ultimate goal is to have a report by User for the date specified:
Total calls
Total calls over 3 minutes
Total outbound calls
Total inbound calls
And
Percentage of calls that are incoming
Percentage of calls that are over 3 minutes
Average daily outgoing calls (when running report for more than one day)
Average daily incoming calls (when running report for more than one day)

Daryl S said:
Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.
Click to expand...
Click to expand...
 
Thank you for the reply. I am working with the first query and it worked
great.
Is there a way to format the percentage totals to 2 decimal places?

Lori

John Spencer said:
SELECT DailyCalls.Department
, DailyCalls.Name
, Count([Name]) as TotalCalls
, Abs(Sum(LengthOfCall >= #00:03:00#)) as Over3Minutes
, Abs(Sum(CallDirection="OUT")) as OutboundCalls
, Abs(Sum(CallDirection="IN")) as InboundCalls
, Abs(Sum(CallDirection="OUT"))/Count([Name]) as PercentOut
, Abs(Sum(CallDirection="In"))/Count([Name]) as PercentIn
, Abs(Sum(LengthOfCall >= #00:03:00#))/Count([Name]) as PercentOver3
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name


Getting the daily average number of calls would have to be done in a separate
queries.

First query to get a count of calls per day
And then a second to use that to get the average of the count per day
SELECT DailyCalls.Department
, DailyCalls.Name
, DailyCalls.Date
, DailyCalls.CallDirection
, Count([Name]) as TotalCalls
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name, DailyCalls.Date,
DailyCalls.CallDirection

Now using that you get get the average
SELECT Department, [Name], CallDirection,
Average(TotalCalls) as AvgNumCalls
FROM TheSavedQuery
GROUP BY Department, [Name], CallDirection,


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Changing the query returned the correct results for Out bound calls, how do I
get the rest of the totals, these should be by user not as a total for the
date?

My ultimate goal is to have a report by User for the date specified:
Total calls
Total calls over 3 minutes
Total outbound calls
Total inbound calls
And
Percentage of calls that are incoming
Percentage of calls that are over 3 minutes
Average daily outgoing calls (when running report for more than one day)
Average daily incoming calls (when running report for more than one day)

Daryl S said:
Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.
Click to expand...
Click to expand...
.
Click to expand...
 
John,

I figured out the percentage. But still need to calculate the percentage of
Out calls and In calls that are over 3 minutes. Is there a way to accomplish
this in the same query?

John Spencer said:
SELECT DailyCalls.Department
, DailyCalls.Name
, Count([Name]) as TotalCalls
, Abs(Sum(LengthOfCall >= #00:03:00#)) as Over3Minutes
, Abs(Sum(CallDirection="OUT")) as OutboundCalls
, Abs(Sum(CallDirection="IN")) as InboundCalls
, Abs(Sum(CallDirection="OUT"))/Count([Name]) as PercentOut
, Abs(Sum(CallDirection="In"))/Count([Name]) as PercentIn
, Abs(Sum(LengthOfCall >= #00:03:00#))/Count([Name]) as PercentOver3
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name


Getting the daily average number of calls would have to be done in a separate
queries.

First query to get a count of calls per day
And then a second to use that to get the average of the count per day
SELECT DailyCalls.Department
, DailyCalls.Name
, DailyCalls.Date
, DailyCalls.CallDirection
, Count([Name]) as TotalCalls
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name, DailyCalls.Date,
DailyCalls.CallDirection

Now using that you get get the average
SELECT Department, [Name], CallDirection,
Average(TotalCalls) as AvgNumCalls
FROM TheSavedQuery
GROUP BY Department, [Name], CallDirection,


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Changing the query returned the correct results for Out bound calls, how do I
get the rest of the totals, these should be by user not as a total for the
date?

My ultimate goal is to have a report by User for the date specified:
Total calls
Total calls over 3 minutes
Total outbound calls
Total inbound calls
And
Percentage of calls that are incoming
Percentage of calls that are over 3 minutes
Average daily outgoing calls (when running report for more than one day)
Average daily incoming calls (when running report for more than one day)

Daryl S said:
Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.
Click to expand...
Click to expand...
.
Click to expand...
 
Yes. Just make the comparison more complex

Abs(Sum(LengthOfCall >= #00:03:00# AND CallDirection="OUT" )) as OutOver3

Abs(Sum(LengthOfCall >= #00:03:00# AND CallDirection="In" )) as InOver3

Percentage would follow the same pattern

Abs(Sum(LengthOfCall >= #00:03:00# AND CallDirection="In" ))/Count([Name]) as
InOver3Percent

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

I figured out the percentage. But still need to calculate the percentage of
Out calls and In calls that are over 3 minutes. Is there a way to accomplish
this in the same query?

John Spencer said:
SELECT DailyCalls.Department
, DailyCalls.Name
, Count([Name]) as TotalCalls
, Abs(Sum(LengthOfCall >= #00:03:00#)) as Over3Minutes
, Abs(Sum(CallDirection="OUT")) as OutboundCalls
, Abs(Sum(CallDirection="IN")) as InboundCalls
, Abs(Sum(CallDirection="OUT"))/Count([Name]) as PercentOut
, Abs(Sum(CallDirection="In"))/Count([Name]) as PercentIn
, Abs(Sum(LengthOfCall >= #00:03:00#))/Count([Name]) as PercentOver3
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name


Getting the daily average number of calls would have to be done in a separate
queries.

First query to get a count of calls per day
And then a second to use that to get the average of the count per day
SELECT DailyCalls.Department
, DailyCalls.Name
, DailyCalls.Date
, DailyCalls.CallDirection
, Count([Name]) as TotalCalls
FROM DailyCalls
WHERE DailyCalls.Date Between [Start Date] And [End Date]
GROUP BY DailyCalls.Department, DailyCalls.Name, DailyCalls.Date,
DailyCalls.CallDirection

Now using that you get get the average
SELECT Department, [Name], CallDirection,
Average(TotalCalls) as AvgNumCalls
FROM TheSavedQuery
GROUP BY Department, [Name], CallDirection,


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Changing the query returned the correct results for Out bound calls, how do I
get the rest of the totals, these should be by user not as a total for the
date?

My ultimate goal is to have a report by User for the date specified:
Total calls
Total calls over 3 minutes
Total outbound calls
Total inbound calls
And
Percentage of calls that are incoming
Percentage of calls that are over 3 minutes
Average daily outgoing calls (when running report for more than one day)
Average daily incoming calls (when running report for more than one day)

:

Go back to the query I provided (before the last posting). Your error is in
the last phrase of the query:
Count([CallDirection])="OUT")
The Count will return a number, not a value of "OUT", so you need to remove
the Count from this - which is what was in my original query.
Click to expand...
.
Click to expand...
Click to expand...
 
John,

Many thanks for the help you have given. I was able to create and
successfully run the first query dealing with total calls and percents.

I now need to calculate the average calls each person made and received for
a particular time period.

My first Query, CallsPerDay, includes for each employee:
TotalCalls
3+Calls
OutCalls
InCalls
PtCallsOut (percentage of calls outgoing)
PtCallsIn (percentage of calls incoming)
PtCalls3+ (percentage of calls over 3 minutes)
PtCallsOut3+ (percentage of outgoing calls over 3 minutes)
PtCallsIn3+ (percentage of incoming calls over 3 mintues0

Now based on the number of calls per day per employee I need to calculate
the average number of: In; Out, and 3+ calls.

Can you help me out?

Lori
 
It can be done. You need to be more specific it what you want.

Average number of calls by type.

Do you want
== average per day over a period of time by department
== average per day over a period of time by employee
== average for the entire period of time by department
== average for the entire period of time by employee
== something else?


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
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:

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="IN" Or (DailyCalls.CallDirection)="IN-AB" Or
(DailyCalls.CallDirection)="IN-I" Or (DailyCalls.CallDirection)="IN-T" Or
(DailyCalls.CallDirection)="IN-TF")) AS [In Calls],
Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out],
Abs(Sum(CallDirection="IN" Or (DailyCalls.CallDirection)="IN-AB" Or
(DailyCalls.CallDirection)="IN-I" Or (DailyCalls.CallDirection)="IN-T" Or
(DailyCalls.CallDirection)="IN-TF"))/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="IN" Or (DailyCalls.CallDirection)="IN-AB" Or
(DailyCalls.CallDirection)="IN-I" Or (DailyCalls.CallDirection)="IN-T" Or
(DailyCalls.CallDirection)="IN-TF" 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]))
GROUP BY DailyCalls.Department, DailyCalls.Name
HAVING (((DailyCalls.Name)<>"emptoexclude"));

Thanks.

Lori
 
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
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:
Click to expand...
SNIP
 
John,

I entered the query and get the error:
Syntax error (missing operator) in query expression 'Avg(Total Calls)'

John Spencer said:
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
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:
Click to expand...
SNIP
.
Click to expand...
 
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 said:
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
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:
Click to expand...
SNIP
.
Click to expand...
 
Oh my goodness, I must have had too little or too much coffee. LOTS of errors
in that.

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

If the query is asking for those fields, then I would check the first query to
see if the column names are correct

Start a new query
== Select your first query as the source
== Select Department, Name and the other fields you want averages for
== Select View: Totals from the menu
== Change GROUP BY to Avg under the fields you want averages for

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
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:
Click to expand...
SNIP
.
Click to expand...
Click to expand...
 
Thanks John,
All errors are gone, and it is calculating an average.
However, I don't think it is using the correct values in the calculation.

For example the report returns total call count as AvgCallCount for
employee1 as 205 for dates May 3-7. That number is the correct total of calls
made by that employee for the day, but the next calculation, AvgOut, which
should be the average calls per day returns 0.95609756097561 instead of 37.8:
(205/7).

How can I tell the query to calculate the average number of calls per day
based on the number of days the query is run for?

Really a million thanks for all the help you have given so far. I would not
have been able to do this without your help.

Lori

John Spencer said:
Oh my goodness, I must have had too little or too much coffee. LOTS of errors
in that.

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

If the query is asking for those fields, then I would check the first query to
see if the column names are correct

Start a new query
== Select your first query as the source
== Select Department, Name and the other fields you want averages for
== Select View: Totals from the menu
== Change GROUP BY to Avg under the fields you want averages for

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
.
Click to expand...
Click to expand...
.
Click to expand...
 
Oops, I gave you some incorrect information.
The average calls per day for employee1 should be 205/5=41
Employee1 made 205 calls from May 3 to May 7.

A more accurate way of calculating this would be to use day/hours worked. I
have another table that includes the days/hours worked as well as what
contracts went out and came in for a particular day.

For averages I need to know the average number of calls for each employee
for the specified time period. Specifically: the average calls made each day,
the average out calls per day, the average in calls per day, and the average
number of calls over 3 minutes per day. The first variable is number of
calls; the second variable would be days worked during the specified time
period.

It could be that an employee has a day off, so making the calculation on
number of days in the query will skew that employees averages. The actual
days worked would be more accurate.

Thanks

Lori

John Spencer said:
Oh my goodness, I must have had too little or too much coffee. LOTS of errors
in that.

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

If the query is asking for those fields, then I would check the first query to
see if the column names are correct

Start a new query
== Select your first query as the source
== Select Department, Name and the other fields you want averages for
== Select View: Totals from the menu
== Change GROUP BY to Avg under the fields you want averages for

Run the query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
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 said:
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
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top