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" wrote:

> 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

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" wrote:

> 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" wrote:
>
> > 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

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

--
Daryl S


"SSi308" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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

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" wrote:

> If you change the WHERE to HAVING, then you should be OK.
>
> --
> Daryl S
>
>
> "SSi308" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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

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" wrote:

> 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" wrote:
>
> > If you change the WHERE to HAVING, then you should be OK.
> >
> > --
> > Daryl S
> >
> >
> > "SSi308" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > If you change the WHERE to HAVING, then you should be OK.
> > >
> > > --
> > > Daryl S
> > >
> > >
> > > "SSi308" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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

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

SSi308 wrote:
> 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" wrote:
>
>> 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

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" wrote:

> 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
>
> SSi308 wrote:
> > 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" wrote:
> >
> >> 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
> .
>

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" wrote:

> 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
>
> SSi308 wrote:
> > 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" wrote:
> >
> >> 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
> .
>