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 |
#11
|
|||
|
|||
Need Help with Totals Query
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 SSi308 wrote: 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 . |
#12
|
|||
|
|||
Need Help with Totals Query
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 |
#13
|
|||
|
|||
Need Help with Totals Query
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 SSi308 wrote: 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 |
#14
|
|||
|
|||
Need Help with Totals Query
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 "John Spencer" wrote: 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 SSi308 wrote: 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 . |
#15
|
|||
|
|||
Need Help with Totals Query
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 |
#16
|
|||
|
|||
Need Help with Totals Query
John,
I entered the query and get the error: Syntax error (missing operator) in query expression 'Avg(Total Calls)' "John Spencer" wrote: You can make your current query a bit easier to understand and a bit faster with some changes. First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause instead of in the Having clause. This should increase the efficiency of the query. You use the HAVING clause when you want to filter on the basis of the aggregated value (Count greater then 5, or Sum is negative, etc). You use WHERE clause when you want to filter the records that are going to be aggregated (usually fields that you are going to GROUP BY). Second: Assuming that all directions that start with "IN" are what you want for the In calls (that is you don't have an additional direction "In-XX" that you want to exclude) you can use LIKE "IN*" to filter the direction. SELECT DailyCalls.Department, DailyCalls.Name , Count(DailyCalls.Name) AS [Total Calls], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+], Abs(Sum(CallDirection="OUT")) AS [Out Calls], Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls], Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out], Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+], Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls Out 3+], Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls In 3+] FROM DailyCalls WHERE DailyCalls.Date Between [Start Date] And [End Date] AND DailyCalls.Name"emptoexclude" GROUP BY DailyCalls.Department, DailyCalls.Name Using that query as a basis (I am using Q as an alias for the query name) to shorten the typing. This is a simple thing but it can make life easier. If I've correctly formed the query, the only thing you need to do is replace "TheSavedQuery" with the actual name of your query. SELECT Q.Department, Q.Name , Avg(Total Calls] as AvgCallCount , Avg([Pt Calls Out] as AvgOut , Avg([Pt Calls In] as AvgIn , Avg([[Pt Calls Out 3+]]) as AvgOutOver3 , Avg([Pt Calls In 3+]) as AvgInOver3 FROM [TheSavedQuery] as Q GROUP BY Q.Department, Q.Name John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: The end result should be average number of calls per day by employee for a specific time period. The first query asks for beginning and ending date. The averages should reflect the data meeting the criteria in the first query. Hope that makes sense. Not sure if this will help, but here is the SQL view of the first query: SNIP . |
#17
|
|||
|
|||
Need Help with Totals Query
John,
I added square brackets around [Total Calls], then added closing brackets around ([Pt Calls Out]) and ([Pt Calls In]), also removed extra [] around the [Pt Calls Out 3+]. The query runs, but asks for input for every field. "John Spencer" wrote: You can make your current query a bit easier to understand and a bit faster with some changes. First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause instead of in the Having clause. This should increase the efficiency of the query. You use the HAVING clause when you want to filter on the basis of the aggregated value (Count greater then 5, or Sum is negative, etc). You use WHERE clause when you want to filter the records that are going to be aggregated (usually fields that you are going to GROUP BY). Second: Assuming that all directions that start with "IN" are what you want for the In calls (that is you don't have an additional direction "In-XX" that you want to exclude) you can use LIKE "IN*" to filter the direction. SELECT DailyCalls.Department, DailyCalls.Name , Count(DailyCalls.Name) AS [Total Calls], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+], Abs(Sum(CallDirection="OUT")) AS [Out Calls], Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls], Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out], Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+], Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls Out 3+], Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls In 3+] FROM DailyCalls WHERE DailyCalls.Date Between [Start Date] And [End Date] AND DailyCalls.Name"emptoexclude" GROUP BY DailyCalls.Department, DailyCalls.Name Using that query as a basis (I am using Q as an alias for the query name) to shorten the typing. This is a simple thing but it can make life easier. If I've correctly formed the query, the only thing you need to do is replace "TheSavedQuery" with the actual name of your query. SELECT Q.Department, Q.Name , Avg(Total Calls] as AvgCallCount , Avg([Pt Calls Out] as AvgOut , Avg([Pt Calls In] as AvgIn , Avg([[Pt Calls Out 3+]]) as AvgOutOver3 , Avg([Pt Calls In 3+]) as AvgInOver3 FROM [TheSavedQuery] as Q GROUP BY Q.Department, Q.Name John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: The end result should be average number of calls per day by employee for a specific time period. The first query asks for beginning and ending date. The averages should reflect the data meeting the criteria in the first query. Hope that makes sense. Not sure if this will help, but here is the SQL view of the first query: SNIP . |
#18
|
|||
|
|||
Need Help with Totals Query
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 SSi308 wrote: John, I added square brackets around [Total Calls], then added closing brackets around ([Pt Calls Out]) and ([Pt Calls In]), also removed extra [] around the [Pt Calls Out 3+]. The query runs, but asks for input for every field. "John Spencer" wrote: You can make your current query a bit easier to understand and a bit faster with some changes. First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause instead of in the Having clause. This should increase the efficiency of the query. You use the HAVING clause when you want to filter on the basis of the aggregated value (Count greater then 5, or Sum is negative, etc). You use WHERE clause when you want to filter the records that are going to be aggregated (usually fields that you are going to GROUP BY). Second: Assuming that all directions that start with "IN" are what you want for the In calls (that is you don't have an additional direction "In-XX" that you want to exclude) you can use LIKE "IN*" to filter the direction. SELECT DailyCalls.Department, DailyCalls.Name , Count(DailyCalls.Name) AS [Total Calls], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+], Abs(Sum(CallDirection="OUT")) AS [Out Calls], Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls], Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out], Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+], Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls Out 3+], Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls In 3+] FROM DailyCalls WHERE DailyCalls.Date Between [Start Date] And [End Date] AND DailyCalls.Name"emptoexclude" GROUP BY DailyCalls.Department, DailyCalls.Name Using that query as a basis (I am using Q as an alias for the query name) to shorten the typing. This is a simple thing but it can make life easier. If I've correctly formed the query, the only thing you need to do is replace "TheSavedQuery" with the actual name of your query. SELECT Q.Department, Q.Name , Avg(Total Calls] as AvgCallCount , Avg([Pt Calls Out] as AvgOut , Avg([Pt Calls In] as AvgIn , Avg([[Pt Calls Out 3+]]) as AvgOutOver3 , Avg([Pt Calls In 3+]) as AvgInOver3 FROM [TheSavedQuery] as Q GROUP BY Q.Department, Q.Name John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: The end result should be average number of calls per day by employee for a specific time period. The first query asks for beginning and ending date. The averages should reflect the data meeting the criteria in the first query. Hope that makes sense. Not sure if this will help, but here is the SQL view of the first query: SNIP . |
#19
|
|||
|
|||
Need Help with Totals Query
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" wrote: 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 SSi308 wrote: John, I added square brackets around [Total Calls], then added closing brackets around ([Pt Calls Out]) and ([Pt Calls In]), also removed extra [] around the [Pt Calls Out 3+]. The query runs, but asks for input for every field. "John Spencer" wrote: You can make your current query a bit easier to understand and a bit faster with some changes. First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause instead of in the Having clause. This should increase the efficiency of the query. You use the HAVING clause when you want to filter on the basis of the aggregated value (Count greater then 5, or Sum is negative, etc). You use WHERE clause when you want to filter the records that are going to be aggregated (usually fields that you are going to GROUP BY). Second: Assuming that all directions that start with "IN" are what you want for the In calls (that is you don't have an additional direction "In-XX" that you want to exclude) you can use LIKE "IN*" to filter the direction. SELECT DailyCalls.Department, DailyCalls.Name , Count(DailyCalls.Name) AS [Total Calls], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+], Abs(Sum(CallDirection="OUT")) AS [Out Calls], Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls], Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out], Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+], Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls Out 3+], Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls In 3+] FROM DailyCalls WHERE DailyCalls.Date Between [Start Date] And [End Date] AND DailyCalls.Name"emptoexclude" GROUP BY DailyCalls.Department, DailyCalls.Name Using that query as a basis (I am using Q as an alias for the query name) to shorten the typing. This is a simple thing but it can make life easier. If I've correctly formed the query, the only thing you need to do is replace "TheSavedQuery" with the actual name of your query. SELECT Q.Department, Q.Name , Avg(Total Calls] as AvgCallCount , Avg([Pt Calls Out] as AvgOut , Avg([Pt Calls In] as AvgIn , Avg([[Pt Calls Out 3+]]) as AvgOutOver3 , Avg([Pt Calls In 3+]) as AvgInOver3 FROM [TheSavedQuery] as Q GROUP BY Q.Department, Q.Name John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: The end result should be average number of calls per day by employee for a specific time period. The first query asks for beginning and ending date. The averages should reflect the data meeting the criteria in the first query. Hope that makes sense. Not sure if this will help, but here is the SQL view of the first query: SNIP . . |
#20
|
|||
|
|||
Need Help with Totals Query
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" wrote: 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 SSi308 wrote: John, I added square brackets around [Total Calls], then added closing brackets around ([Pt Calls Out]) and ([Pt Calls In]), also removed extra [] around the [Pt Calls Out 3+]. The query runs, but asks for input for every field. "John Spencer" wrote: You can make your current query a bit easier to understand and a bit faster with some changes. First: Move the (((DailyCalls.Name)"emptoexclude")) into a where clause instead of in the Having clause. This should increase the efficiency of the query. You use the HAVING clause when you want to filter on the basis of the aggregated value (Count greater then 5, or Sum is negative, etc). You use WHERE clause when you want to filter the records that are going to be aggregated (usually fields that you are going to GROUP BY). Second: Assuming that all directions that start with "IN" are what you want for the In calls (that is you don't have an additional direction "In-XX" that you want to exclude) you can use LIKE "IN*" to filter the direction. SELECT DailyCalls.Department, DailyCalls.Name , Count(DailyCalls.Name) AS [Total Calls], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#)) AS [Calls 3+], Abs(Sum(CallDirection="OUT")) AS [Out Calls], Abs(Sum(CallDirection LIKE "IN*")) AS [In Calls], Abs(Sum(CallDirection="OUT"))/Count([Name]) AS [Pt Calls Out], Abs(Sum(CallDirection LIKE "IN*"))/Count([Name]) AS [Pt Calls In], Abs(Sum(LengthOfCall=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls 3+], Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls Out 3+], Abs(Sum(CallDirection LIKE "IN*" And DailyCalls.LengthOfCall)=#12/30/1899 0:3:0#))/Count([Name]) AS [Pt Calls In 3+] FROM DailyCalls WHERE DailyCalls.Date Between [Start Date] And [End Date] AND DailyCalls.Name"emptoexclude" GROUP BY DailyCalls.Department, DailyCalls.Name Using that query as a basis (I am using Q as an alias for the query name) to shorten the typing. This is a simple thing but it can make life easier. If I've correctly formed the query, the only thing you need to do is replace "TheSavedQuery" with the actual name of your query. SELECT Q.Department, Q.Name , Avg(Total Calls] as AvgCallCount , Avg([Pt Calls Out] as AvgOut , Avg([Pt Calls In] as AvgIn , Avg([[Pt Calls Out 3+]]) as AvgOutOver3 , Avg([Pt Calls In 3+]) as AvgInOver3 FROM [TheSavedQuery] as Q GROUP BY Q.Department, Q.Name John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: The end result should be average number of calls per day by employee for a specific time period. The first query asks for beginning and ending date. The averages should reflect the data meeting the criteria in the first query. Hope that makes sense. Not sure if this will help, but here is the SQL view of the first query: SNIP . . |
Thread Tools | |
Display Modes | |
|
|