Need Help with Totals Query

J

John Spencer

My error. You need a slightly different first query. You need to add in the
Date field.

Assumption: The date field includes ONLY the date and has no time component.
If the Date field has a time component then you will need to strip off the
time component to consolidate all the records for each day -
DateValue(DailyCalls.Date)

SELECT DailyCalls.Department, DailyCalls.Name, DailyCalls.Date
, 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, DailyCalls.Date

Now use the revised query as the source for the final 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 [TheNewSavedQuery] as Q
GROUP BY Q.Department, Q.Name

By the way, naming a field "Date" is asking for trouble and confusion. Date
is a function that returns the current date. It is much better to use
something like CallDate or DateOfCall as the field name.


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

:

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...
 
S

SSi308

Okay, now I have messed things up.
First I need to tell you I made changes to several tables based on
suggetions database structure. I had duplicate fields in the employee table
and the DailyCall table.
This is what I did:
1. Revised the Employees table, fields are: EmpID, Name, Initials, Department.
2. Removed the department and name field from DailyCalls Table and added an
EmpID field as foreign field, related to EmpID in Employees table.
3. Edited my queries and replaced DailyCalls.Name with Employees.Name, ditto
for department.
4. In queries also replaced the DailyCalls.Name with DailyCalls.EmpID

This seemed to work fine all queries ran correctly. But I cannot figure out
how to edit my original query to include the DailyCalls.Date

So I need help revising the following query to reflect the changes you
suggest.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name,
Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Name;

I am going to work on this as well and am hoping I have something in place
by next Monday, the target date for finishing this project!

Thanks.

Lori

John Spencer said:
My error. You need a slightly different first query. You need to add in the
Date field.

Assumption: The date field includes ONLY the date and has no time component.
If the Date field has a time component then you will need to strip off the
time component to consolidate all the records for each day -
DateValue(DailyCalls.Date)

SELECT DailyCalls.Department, DailyCalls.Name, DailyCalls.Date
, 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, DailyCalls.Date

Now use the revised query as the source for the final 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 [TheNewSavedQuery] as Q
GROUP BY Q.Department, Q.Name

By the way, naming a field "Date" is asking for trouble and confusion. Date
is a function that returns the current date. It is much better to use
something like CallDate or DateOfCall as the field name.


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

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.

:

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...
 
J

John Spencer

I think all you should need is to add DailyCalls.Date into the query one more
time and do a group by on it.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name
, DailyCalls.Date
, Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID
, Employees.Department
, Employees.Name
, DailyCalls.Date;



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Okay, now I have messed things up.
First I need to tell you I made changes to several tables based on
suggetions database structure. I had duplicate fields in the employee table
and the DailyCall table.
This is what I did:
1. Revised the Employees table, fields are: EmpID, Name, Initials, Department.
2. Removed the department and name field from DailyCalls Table and added an
EmpID field as foreign field, related to EmpID in Employees table.
3. Edited my queries and replaced DailyCalls.Name with Employees.Name, ditto
for department.
4. In queries also replaced the DailyCalls.Name with DailyCalls.EmpID

This seemed to work fine all queries ran correctly. But I cannot figure out
how to edit my original query to include the DailyCalls.Date

So I need help revising the following query to reflect the changes you
suggest.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name,
Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Name;

I am going to work on this as well and am hoping I have something in place
by next Monday, the target date for finishing this project!

Thanks.

Lori

John Spencer said:
My error. You need a slightly different first query. You need to add in the
Date field.

Assumption: The date field includes ONLY the date and has no time component.
If the Date field has a time component then you will need to strip off the
time component to consolidate all the records for each day -
DateValue(DailyCalls.Date)

SELECT DailyCalls.Department, DailyCalls.Name, DailyCalls.Date
, 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, DailyCalls.Date

Now use the revised query as the source for the final 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 [TheNewSavedQuery] as Q
GROUP BY Q.Department, Q.Name

By the way, naming a field "Date" is asking for trouble and confusion. Date
is a function that returns the current date. It is much better to use
something like CallDate or DateOfCall as the field name.


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

:

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.

:

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...
 
S

SSi308

With a few tweaks I was able to get the averages query to work.

But after saving it and trying to run it again, I get the error: "Circular
reference caused by 'AveragesPerDay'. Any ideas on how to correct this?


To complicate things-
The way the query is written it does an average based on the dates where
there are calls, or at least I believe this is the case.
My issue is that an employee may work varying hours on varying days. to be
more precise the manager wants the average calculated on hours worked for a
given period.
In other words I need to be able to take the hours worked for a given
period, divide that by 8 to get the equivilent days worked. Then use that
number in the average calculation.

For example an employee works the following hours:
Mon 8.2
Tue 8.5
Wed 10.0
Thurs 4.9
Fri 6.4

That is a total of 38 hours or 4.75 days (38/8). If they made 100 calls I
need to calculate the average using 100\4.75.

We do have a table that includes hours each employee works for each day.

Lori



John Spencer said:
I think all you should need is to add DailyCalls.Date into the query one more
time and do a group by on it.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name
, DailyCalls.Date
, Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID
, Employees.Department
, Employees.Name
, DailyCalls.Date;



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Okay, now I have messed things up.
First I need to tell you I made changes to several tables based on
suggetions database structure. I had duplicate fields in the employee table
and the DailyCall table.
This is what I did:
1. Revised the Employees table, fields are: EmpID, Name, Initials, Department.
2. Removed the department and name field from DailyCalls Table and added an
EmpID field as foreign field, related to EmpID in Employees table.
3. Edited my queries and replaced DailyCalls.Name with Employees.Name, ditto
for department.
4. In queries also replaced the DailyCalls.Name with DailyCalls.EmpID

This seemed to work fine all queries ran correctly. But I cannot figure out
how to edit my original query to include the DailyCalls.Date

So I need help revising the following query to reflect the changes you
suggest.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name,
Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Name;

I am going to work on this as well and am hoping I have something in place
by next Monday, the target date for finishing this project!

Thanks.

Lori

John Spencer said:
My error. You need a slightly different first query. You need to add in the
Date field.

Assumption: The date field includes ONLY the date and has no time component.
If the Date field has a time component then you will need to strip off the
time component to consolidate all the records for each day -
DateValue(DailyCalls.Date)

SELECT DailyCalls.Department, DailyCalls.Name, DailyCalls.Date
, 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, DailyCalls.Date

Now use the revised query as the source for the final 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 [TheNewSavedQuery] as Q
GROUP BY Q.Department, Q.Name

By the way, naming a field "Date" is asking for trouble and confusion. Date
is a function that returns the current date. It is much better to use
something like CallDate or DateOfCall as the field name.


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

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

:

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.

:

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...
 
J

John Spencer

Sorry, but this just gets more and more complex. I can't devote this much time
to a target that moves every time I propose a solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
With a few tweaks I was able to get the averages query to work.

But after saving it and trying to run it again, I get the error: "Circular
reference caused by 'AveragesPerDay'. Any ideas on how to correct this?


To complicate things-
The way the query is written it does an average based on the dates where
there are calls, or at least I believe this is the case.
My issue is that an employee may work varying hours on varying days. to be
more precise the manager wants the average calculated on hours worked for a
given period.
In other words I need to be able to take the hours worked for a given
period, divide that by 8 to get the equivilent days worked. Then use that
number in the average calculation.

For example an employee works the following hours:
Mon 8.2
Tue 8.5
Wed 10.0
Thurs 4.9
Fri 6.4

That is a total of 38 hours or 4.75 days (38/8). If they made 100 calls I
need to calculate the average using 100\4.75.

We do have a table that includes hours each employee works for each day.

Lori



John Spencer said:
I think all you should need is to add DailyCalls.Date into the query one more
time and do a group by on it.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name
, DailyCalls.Date
, Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID
, Employees.Department
, Employees.Name
, DailyCalls.Date;



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Okay, now I have messed things up.
First I need to tell you I made changes to several tables based on
suggetions database structure. I had duplicate fields in the employee table
and the DailyCall table.
This is what I did:
1. Revised the Employees table, fields are: EmpID, Name, Initials, Department.
2. Removed the department and name field from DailyCalls Table and added an
EmpID field as foreign field, related to EmpID in Employees table.
3. Edited my queries and replaced DailyCalls.Name with Employees.Name, ditto
for department.
4. In queries also replaced the DailyCalls.Name with DailyCalls.EmpID

This seemed to work fine all queries ran correctly. But I cannot figure out
how to edit my original query to include the DailyCalls.Date

So I need help revising the following query to reflect the changes you
suggest.

Query: CallsPerDay
SELECT DailyCalls.EmpID, Employees.Department, Employees.Name,
Count(DailyCalls.EmpID) 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([DailyCalls.EmpID]) AS [Pt Calls Out],
Abs(Sum(CallDirection Like "IN*"))/Count([DailyCalls.EmpID]) AS [Pt Calls
In],
Abs(Sum(LengthOfCall>=#12/30/1899 0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt
Calls 3+],
Abs(Sum(CallDirection="Out" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls Out 3+],
Abs(Sum(CallDirection Like "IN*" And (DailyCalls.LengthOfCall)>=#12/30/1899
0:3:0#))/Count([DailyCalls.EmpID]) AS [Pt Calls In 3+]

FROM Employees INNER JOIN DailyCalls ON Employees.EmpID = DailyCalls.EmpID

WHERE (((DailyCalls.Date) Between [Start Date] And [End Date]))

GROUP BY DailyCalls.EmpID, Employees.Department, Employees.Name;

I am going to work on this as well and am hoping I have something in place
by next Monday, the target date for finishing this project!

Thanks.

Lori

:

My error. You need a slightly different first query. You need to add in the
Date field.

Assumption: The date field includes ONLY the date and has no time component.
If the Date field has a time component then you will need to strip off the
time component to consolidate all the records for each day -
DateValue(DailyCalls.Date)

SELECT DailyCalls.Department, DailyCalls.Name, DailyCalls.Date
, 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, DailyCalls.Date

Now use the revised query as the source for the final 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 [TheNewSavedQuery] as Q
GROUP BY Q.Department, Q.Name

By the way, naming a field "Date" is asking for trouble and confusion. Date
is a function that returns the current date. It is much better to use
something like CallDate or DateOfCall as the field name.


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

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

:

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.

:

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create a Query that Prompts for Date and Time 4
Help with an Expression 2
Crosstab query totals 3
Order Query By TIME HELP? 4
need to show blank values 4
Totals query per day per month 1
Help with update query UDF 1
Data type mismatch with Totals Query 0

Top