This month, Doug Steele looks at an approach to analyzing telephone logs.

I work at a call center. I have a log table containing information about each call received–when the call started, when it completed, and which agent handled the call. How can I determine the maximum number of agents occupied throughout the day?

This is a classic problem in capacity management: Based on actual usage data, what is the maximum consumption/demand for any resource? In this case, your resource is the calling agents and you want to know the maximum quantity in use at any time. You could substitute maximum number of widgets used or sold in a day/week/month (to determine the maximum quantity that you have to keep in stock), the total bandwidth in use at any one time, the maximum number of users logged into the database, or any other resource/demand. As you’ll see, once you’ve found a way to gather the data, you can determine what your maximum demand is using Access’ built-in functionality.

For the sake of argument, I’ll assume a table named PhoneLog that contains three fields, as shown in Table 1. While you’d probably have some additional fields in the table (such as a Primary Key!), the additional fields aren’t necessary to solve the problem at hand.

Table 1. Description of the PhoneLog table.

In analyzing this sort of problem, I find it easiest to think in terms of when things are changing. If you think about it, it’s pretty obvious that the number of active agents changes any time a call is received or any time a call is completed. That means you can create a query that unions together all of the CallStartDTM and CallEndDTM values in the table. Here’s what that query (which I’ve named qryPhoneLogChanges) would look like:

SELECT CallStartDTM AS CallChange

FROM PhoneLogs

UNION

SELECT CallEndDTM AS CallChange

FROM PhoneLogs

As an aside, I assume you realize that Union queries cannot be created in the graphical query designer. You must go into the SQL View of the query and type the SQL yourself.

Once you know when every change occurred, the next thing to do is determine which calls are active at each time change. A call is active if it started at or before the given time and ended after the given time. A call is not active if it ended at exactly the time in question. Now, qryPhoneLogChanges gives a list of times. By joining that list to the PhoneLogs table, it should be possible to determine how many calls are active at each of the times highlighted in qryPhoneLogChanges. There’s a slight complication, though. When you join tables together based on comparison operators other than =, you’re creating what’s referred to as a “non-equijoin query.” As was the case with the Union query, you cannot create non-equijoin queries in the graphical query designer; you have to go into the SQL View of the query and type the SQL, as shown here (which I’ve named qryActiveCalls):

SELECT qryPhoneLogChanges.CallChange, PhoneLogs.Agent,

  PhoneLogs.CallStartDTM, PhoneLogs.CallEndDTM

FROM PhoneLogs

RIGHT JOIN qryPhoneLogChanges

ON (PhoneLogs.CallStartDTM <=

  qryPhoneLogChanges.CallChange)

AND (PhoneLogs.CallEndDTM >

  qryPhoneLogChanges.CallChange)

The query qryActiveCalls will return one row for each call that’s active at each of the given times in qryPhoneLogChanges. The next step, therefore, is to determine how many calls are active at any point in time. This is a simple Totals query based on qryActiveCalls and using the Count aggregate function:

SELECT CallChange, Count(Agent) AS ActiveAgents

FROM qryActiveCalls

GROUP BY CallChange

This is almost what you want. qryActiveCounts returns a list of times and the number of calls active at each time. To see the maximum number of calls active in a given period, you can create another Totals query, this time based on qryActiveCounts and using the Max aggregate function. You’ll need to restrict the value used for grouping in some way. For example, if you want the maximum number of calls per day, you’ll need to use DateValue([CallChange]) in the query:

SELECT DateValue([CallChange]) AS TimeRange,

   Max(ActiveAgents) AS MaximumActive

FROM qryActiveCounts

GROUP BY DateValue([CallChange])

Alternatively, you can get the maximum number of calls per hour using a query like this:

SELECT Format([CallChange],"yyyy-mm-dd hh")

  AS TimeRange, Max(ActiveAgents) AS MaximumActive

FROM qryActiveCounts

GROUP BY Format([CallChange],"yyyy-mm-dd hh")

It’s essential that you use yyyy-mm-dd hh as the format for the date and hour, in order to get it to sort properly.

Now, I’ve written this solution as four separate queries, basing each query on one that precedes it. This isn’t strictly necessary (well, it is in Access 97, which is what I’ve used for the accompanying downloadable database). It’s possible (in newer versions of Access, or in other DBMSs) to combine all of the SQL into a single query. For example, using Jet 4.0 (Access 2000 and newer), this query is the equivalent of qryMaximumPerHour:

SELECT Format(Q3.CallChange, "yyyy-mm-dd hh")

   AS TimeRange,

 Max(ActiveAgents) AS MaximumActive

FROM

[SELECT Q2.CallChange,

 Count(Q2.Agent) AS ActiveAgents

  FROM

  (SELECT Q1.CallChange,

   T1.Agent, T1.CallStartDTM, T1.CallEndDTM

  FROM PhoneLogs AS T1

  RIGHT JOIN (

    SELECT CallStartDTM AS CallChange

    FROM PhoneLogs

    UNION

    SELECT CallEndDTM AS CallChange

    FROM PhoneLogs

  ) AS Q1

  ON (T1.CallStartDTM<=Q1.CallChange)

  AND (T1.CallEndDTM>Q1.CallChange)) AS Q2

GROUP BY Q2.CallChange]. AS Q3

GROUP BY Format(Q3.CallChange, "yyyy-mm-dd hh")

This means that it’s possible to use SQL like this in a pass-through query. The previous solution, which built queries on top of other queries, wouldn’t work with pass-through queries because you can’t base a pass-through query on another pass-through query. With the “queries-on-queries” approach, only qryPhoneLogChanges could be a pass-through query and you’d end up having most of your computation done locally in Jet.

The second part of this Access Answers column appears here Save Yourself Some Work