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.