If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Need Help with Totals Query
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 |
#2
|
|||
|
|||
Need Help with Totals Query
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 |
#3
|
|||
|
|||
Need Help with Totals Query
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 |
#4
|
|||
|
|||
Need Help with Totals Query
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 |
#5
|
|||
|
|||
Need Help with Totals Query
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 |
#6
|
|||
|
|||
Need Help with Totals Query
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 |
Thread Tools | |
Display Modes | |
|
|