Phone call log, multiple fields for different times

P

Philip

Hello,
I'm having trouble coming up with a way to do this...and I think there
should be a way but I've been unable to figure out whether relationships and
multiple tables do what I need. I've read through a couple of tutorials on
the office site and they don't cover something like this.

Ok, onto the problem. I've got some data that is basically a phone call
log for a specific date. Each date has different times that the calls were
received for every half an hour.
I believe I should define the primary key in the table as the date...but
I'm lost after that.

There are 3 additional values that count towards the times. Here's a
sample of the data if it helps:

Time Avg Ans Speed # of Calls Avg talk time
6:00am - 6:30 am :07 5 :11
....
....
10:30pm-11:00pm

The problem is, there are so many time fields, and 3 additional values that
are specifically related to the time for that specific date.
I've already got a way to enter the times in via a scripting program for the
fields, but I don't know how to set up the tables or forms.

Thanks for any help that can be offered.
 
J

Jeff Boyce

Philip

I can't quite visualize your "call log"'s data structure. Are you saying
that you have a table with rows that are specific to a time period (e.g.,
6:00am - 6:30am)? That sounds like a ... spreadsheet!

If you log your calls when they come in (e.g., 4/20/2006 6:13:00 AM) by
using the Now() function, you can always divide them up into half-hour
blocks using queries and reports.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

Jeff,
They are listed in half hour blocks from 6am-11pm. The page I get is
actually a report from the phone system that prints out for every day. I've
been given the responsibility to log this data to a file so that a pivot
table can be used to show which days/times are busiest.
I did manage to make a spreadsheet, with three different tabs-one for each
piece of data that needs to be entered, and with the half hour blocks going
across the top columns. I was not able to previously visuallize a way to get
all the data into a file until reading about access and separating the data
into different tables. Setting up the access tables for so many fields and
creating a nice looking form to put the data in was too much. I've settled
down on the spreadsheet format I think. But if there is a better way to use
access, I would be able to import the data one way or another back into a
database.

Thank you for the response,
Philip
 
P

Philip

The data I receive is formatted like follows:

Time Avg Speed Ans ACD Calls Avg Talk Time
6:00-6:30 AM :07 1 :11
6:30-7:00 AM :05 4 :33
...
10:30-11:00 PM .. .. ..

I only properly format the time values on Avg Talk Time, as the answer time
will never go higher than about 10-15 seconds.
 
J

Jeff Boyce

Philip

A couple observations ...

If you are actually receiving the "6:00-6:30 AM" text string, I see neither
an exact time nor a date. How do you keep track of which date, if this is
important?

I noticed that you get a text string which contains numbers whose labels
appear to indicate that they are averaged values. "Doing the math" on
averages is a little different than doing the math on raw numbers.

And you'll need to convert those text strings to actual numbers before doing
any math ... the string ":33" contains digits, but isn't a number. You'll
need to work out a way to parse out the seconds and minutes (?and hours) and
convert them to the lowest unit of measure you need (I'll guess seconds).
For instance, if you received "1:03", you'd need to parse this to a number
= 63. Once you have the number/value, you can begin considering how you'd
do math (carefully! see above).

If, when you're all done with your math, you want to present a "time" in
hh:nn:ss format, you'll also need to work out the routines you'll use to
parse a number like 2350 (a total number of seconds) into its hh:nn:ss
representation (I believe "39:10", but my math might be off).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

I'm sorry, I forgot to put in the date. This is a unique date for each
set of data (the data I included previously was all one date). I have a
means of converting the numbers into minutes via a scripting application I
use a lot, and can parse through a text document.
I don't understand what you mean about raw numbers and averages. The
numbers I get are already averages for a specific time block (ie 6:00-6:30
AM), but I input each piece of data per date and then average the averages.
Anyways, we're going out on a tangent here. The problem I really needed to
address was how to set up the tables (not necessarily data formats per say)
to allow me to at least enter the data easily -- and worry about the format
after I have the structure down.

Also, I actually enter the time data without the colons, and use periods
off of the number pad. I use the scripting program to parse through the
numbers and replace '.' with ':' where necessary. I can use the program to
convert existing data in the table to correspond to the formats I might need
for Access.

Thank you for all the help.
 
J

Jeff Boyce

Philip

The data example you provided is an example of why you don't "average the
averages".

One of the two had a single data point, the other had 4 (if I'm reading your
data correctly). If you "average the averages", you're giving the same
weight to the sample of size one that you are to the sample of size 4.

To take it to extremes, if you had a single data point and 100 data points,
and an "average" of 20 seconds for the "one" and 100 seconds for the "100",
do you really want to suggest that the average of all 101 datapoints is (20
+ 100)/101?!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

Jeff,
I understand what you mean by the averages now. However, there are
currently dates ranging from 3/22 through 4/25, each with their own set of
data. Won't the averages be closer to the true averages since there is still
a lot of data? I can see that the averages will be off from the true
average, but I don't have raw data, only averages in the half-hour blocks.
My supervisor wants to be able to look at this data, as averages, on the
dates for time blocks, days of the week, etc. I don't know of any other way
to present the data without averaging the numbers that were provided
(averages). I suspect that as more data, the average of the averages will
approach the true average...which is the best that can be done I think. That
is a very keen observation to see from looking at the table :). The talk
times and average answer speed aren't really the critical numbers to worry
about, the ACD Calls (# of calls per half hour) will average just fine. I
suspect that is the most important piece, and it will be accurate as it is
just totals--and not averages.

Do you know how I can set up the database? I haven't yet been able to
figure out a way after many attempts of setting up different tables. I think
maybe I'm over complicating the matter, and will actually need the time
fields on each table (but I also read that duplicate data should be avoided,
and I thought this would have been one example). The times do not change,
and they only contain the data for that specific row (ie Avg Speed Answered
from 6:00am-11:00pm). Setting up fields (I believe it's 32 different fields)
is chaos, but if that's the way I have to do it, I guess I have no choice in
the matter. I also tried to create a "Time" table, with these times in
there..but I cannot figure out how to use them with the other tables.
 
J

Jeff Boyce

Philip

But you DO have (something akin to) the raw data! If the (computed) average
is 33 seconds ...
6:30-7:00 AM :05 4 :33
and there were 4 calls, you know the sum of the call seconds (4 x 33) and
the number of calls 4.

If you add up all THOSE numbers, you have the total call seconds and the
total number of calls. Now you can compute the average.

I'm not sure why you'd switch from having the time (range) as a field to
trying to set up a column for each time range. Why not use the data as you
described it? You could create a query and GroupBy the time range to get
all the records from "6:30-7:00 AM".

Or, if you'd like to avoid repeating that time interval (even though your
incoming data already has that, right?), you could create a "time range"
table and uniquely identify each time range, and use that unique identifier
for each "averages" record. This might be more relationally correct, but if
your incoming data already has the time range value, it isn't clear what
you'd gain.

I may have lost sight of what questions you want answered.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

I don't know how to set it up at all. The only thing I know is how to create
tables and a relationship between them. I don't understand how I can tie the
tables together, or do any of the other things you said. I don't know of an
easy way to set the tables up so I can have the times predefined, and the
fields automatically created based on these times.
The method I have tried thus far has involved separate tables, one for
each field of data (Avg Time, Avg Speed, and #calls)--all of which have the
times hard-coded into the table. That's not the only problem I having
trouble resolving. I don't have a clue as to how to set a PivotTable for the
end result from all three tables. I've successfully created an excel
spreadsheet, but there is a lot of duplicate data (such as times, and
especially the day of the week that the date falls on. Each time range had
to be assigned the date and day of the week in order for the pivot table to
work. I tried to format the date as the day of the week, but it did not work
unfortunately when I went to sort.
 
J

Jeff Boyce

Philip

I've looked back over this thread and it seems to me that you may be
confusing the way data is stored in a spreadsheet ("so many time fields")
and the way data is stored in Access (in a well-normalized, relational
design).

You originally stated that you wanted to use a pivot table. Why? Can you
describe what you want to do without resorting to describing the "how"?

You mentioned that you've already set up your tables, but I don't recall
seeing the structure of your tables. Can you describe your Access table
structure?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

The pivot table was intended just to show a type of report, that can be
changed to show different information.

The tables in access are set up like this:

ACD Calls <- name
With fields:
Date
6:00AM - 6:30AM
6:30AM - 7:00AM
....
10:30PM-11:00PM

The AvgTalkTime and AvgSpeed tables are the exact same setup, with Date
being defined as the primary key. I made relationships between all the
tables with the date. I also made a separate 'Day' table to represent the
day of the week, with a primary key of date and created a relationship to it.
I don't know if I was correct in setting the tables up this way.
I've tried different combinations, and at one time had a separate table
"Times" which had Time and Date fields, and I put the time ranges into the
actual database.
 
J

Jeff Boyce

Philip

Here's another approach to setting up a more-normalized table structure for
the data as I understand it...

tlkpTimeRange
TimeRangeID (an Autonumber field, the Primary Key)
TimeRange (this is the "6:00 - 6:30 AM", "6:30 - 7:00 AM", ... -- one
row per period)

tblCallData
CallDateID (Autonumber Primary Key)
CallDate (don't use the word "Date" as a field name -- this is a
reserved word in Access)
TimeRangeID (a "foreign key" field; shows which time range)
CallVolume (the number of calls in the range
AvgSpeed
AvgTalkTime

Then I'd use queries to retrieve a particular day/timerange combination, or
to calculate a particular timerange's average (see earlier response about
not using average of average).

Note: Your TimeRanges are not mutually exclusive. If a call is recorded
for exactly 6:30 AM, in which group does it belong?<g>

Note2: If you get a call at 11:59 PM on 4/29, and it lasts for 3 minutes
(i.e., into 4/30), in which day is it recorded?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Philip

Thanks, that's certainly a huge push in the right direction.

When I create a form with the fields I need (and one to show the value for
the time), I have to enter a time and date for each record. I tried to think
of a way to create a date table and use it as a main form with buttons to the
other forms...but I was unable to get it to work - or link with the other
tables. I couldn't think of a way to link the dates to the other data.

Thanks for following up on this thread Jeff, I know it's a few pages back by
now.

Philip
 
S

Stryker412

Is there a template available that will allow me to just enter name, ext, and
number dialed (with support for international calls) with support for a an
auto entering date?
 
J

John Vinson

Is there a template available that will allow me to just enter name, ext, and
number dialed (with support for international calls) with support for a an
auto entering date?
Click to expand...

Probably, but you can very very easily do this yourself. If you set
the Default value of a form textbox (bound to a date/time field) to
=Date() it will fill in the date the record was entered; if instead
you use =Now() it will fill in the date and time.

Not sure what you mean by "support for international calls". You may
want to just *leave off* any Input Mask, since different countries'
telephone number conventions are so different.

John W. Vinson[MVP]
 

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

Finding the right style field in VBA 0
Finding the proper style field and the numbered paragraph value within the style 0
Select multiple text values and display in one field 3
Changing Working Times 4
Calendar 2
few problems with phone log database, thx for any help! 1
Sumproduct with date and time? 0
Sumproduct with date and time? 1

Top