Phone call log, multiple fields for different times

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.

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

Philip

It would help if I could see an example of the data as you receive it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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.

"Jeff Boyce" wrote:

Philip

It would help if I could see an example of the data as you receive it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

"Jeff Boyce" wrote:

Philip

It would help if I could see an example of the data as you receive it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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.


"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

"Jeff Boyce" wrote:

Philip

It would help if I could see an example of the data as you receive it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.


"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

"Jeff Boyce" wrote:

Philip

It would help if I could see an example of the data as you receive
it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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.


"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.


"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

"Jeff Boyce" wrote:

Philip

It would help if I could see an example of the data as you receive
it.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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

"Jeff Boyce" wrote:

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.

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
http://mvp.support.microsoft.com/

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

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


"Philip" wrote in message
...
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.