Multiple supervisors over a date range in query

[Nicholas Scarpinato]

I have an issue with a query and I don't know quite how to tackle it. I'm
building a metrics database for our customer service department, and my
reports are exactly what they're looking for, except when a CSR switches
supervisors in the middle of a reporting period. When this occurs, their
information for the entire month shows up under the current supervisor. I
built a second changelog table to keep track of when the supervisor changes
occur, but I haven't been able to translate those changes over to my report.
I end up with a CSR showing up under both supervisors with the same data.
Here's my query, it's a Make Table query that builds the table the report's
source query is based on:


SELECT tblAgentDataMain.[Agent Name], Format(Avg(IIf(TimeValue([Agent Talk
Time])=0,Null,TimeValue([Agent Talk Time]))),"hh:nn:ss") AS [Talk Time],
Avg(IIf([Average Answered per Hour]=0,Null,[Average Answered per Hour])) AS
AAPH, Avg(IIf([Adherence Percentage]=0,Null,[Adherence Percentage])) AS AP,
Avg(IIf([QA Percent]=0,Null,[QA Percent])) AS QP INTO tblAverages
FROM ((tblAgentDataMain INNER JOIN tblAgentAdherenceMain ON
(tblAgentDataMain.[Agent Name] = tblAgentAdherenceMain.[Agent Name]) AND
(tblAgentDataMain.[Reporting Date] = tblAgentAdherenceMain.[Reporting Date]))
INNER JOIN tblQAMain ON (tblAgentAdherenceMain.[Agent Name] =
tblQAMain.[Agent Name]) AND (tblAgentAdherenceMain.[Reporting Date] =
tblQAMain.[Reporting Date])) INNER JOIN tblATTDataMain ON (tblQAMain.[Agent
Name] = tblATTDataMain.[Agent Name]) AND (tblQAMain.[Reporting Date] =
tblATTDataMain.[Reporting Date])
WHERE (((tblAgentDataMain.[Reporting Date]) Between
fFirstDayInMonth([Forms].[frmReportSelect].[ReportDate]) And
fLastDayInMonth([Forms].[frmReportSelect].[ReportDate])))
GROUP BY tblAgentDataMain.[Agent Name];


fFirstDayInMonth/fLastDayInMonth are functions to generate the first and
last days of the month, respectively:

Function fFirstDayInMonth(dtmdate As Date) As Date
fFirstDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate), 1)
End Function
Function fLastDayInMonth(dtmdate As Date) As Date
fLastDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate) + 1, 0)
End Function


The table that stores the supervisor information is called
tblSupervisorTeamsChangeLog. This table has three fields, "Agent Name",
"Supervisor", and "Starting Date". Whenever the supervisors change their team
members, an INSERT INTO query runs that adds that agent, the new supervisor,
and the current date into the table. I do not have this table linked to this
make table query yet because I've tried three or four different approaches
and none of them yielded the desired results. What I need to be able to do is
pull a report for each agent for a date range of first date to last date of
the month, and be able to split up that data should their be a change of
supervisors during that month. I think my problem is that my expressions to
generate the averages for each field aren't grouping by the dates, they're
just averaging the entire table... can anyone confirm if this is the problem,
and if not, give me some insight as to what I've done wrong?

 

[Lord Kelvan]

that seems like a nice problem it sounds to me that you are getting
information for each of your agents then grouping them against your
supervisors for yoru report what you could do it use the dated in your
change log table against the dates in the query of when the calls were
taken but to do that you would need not only the to supervisor but the
from supervisor in the change log as well

do that will let you match up times and this will create some nice
complex queries the alternative method would be to modify some tables
where the call data is stored and add the supervisor into it as well
in doing this you can then select the supervisor as well as the agent
and group the information based on both value in your report doing
this will allow you to seperate values against the supervisors whos
CSRs worked for them at that point of time

i would recommend modifying the tables and the insert into queries to
add the supervisor and after a month of work modify the report to then
contain the new grouping method i think the complexity of the queries
needed may be a bit much to use the change log but i cannot think of
how it would work without seeing at least your table structure and a
sudo sample of what you want as a result

hope this helps