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



"Jeff Boyce" wrote:

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


"Jeff Boyce" wrote:

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

"Jeff Boyce" wrote:

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.