A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Phone call log, multiple fields for different times



 
 
Thread Tools Display Modes
  #11  
Old April 27th, 2006, 07:43 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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.




  #12  
Old April 28th, 2006, 01:30 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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.





  #13  
Old April 28th, 2006, 07:41 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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.






  #14  
Old April 30th, 2006, 02:24 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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.







  #15  
Old May 1st, 2006, 10:37 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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.








  #16  
Old May 2nd, 2006, 01:55 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

At this point, I'll suggest that you post the forms-related question to one
of the forms newsgroups. By starting over fresh, you're more likely to get
many more "eyes" on your post.

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









  #17  
Old May 2nd, 2006, 07:34 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

Will do, thanks for all of your help.

"Jeff Boyce" wrote:

At this point, I'll suggest that you post the forms-related question to one
of the forms newsgroups. By starting over fresh, you're more likely to get
many more "eyes" on your post.

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

  #18  
Old May 15th, 2006, 09:11 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

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?
  #19  
Old May 16th, 2006, 01:27 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Phone call log, multiple fields for different times

On Mon, 15 May 2006 13:11:02 -0700, Stryker412
wrote:

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?


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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple dates, times, or formulas in a single cell PM-S General Discussion 2 January 12th, 2006 05:19 AM
Relating multiple fields in table to multiple fields in another ta RandallMJ Database Design 3 January 9th, 2006 03:46 PM
Looking up information across multiple fields Someone General Discussion 2 December 18th, 2005 07:13 PM
Using calculated fields in multiple queries Linguist Running & Setting Up Queries 6 May 27th, 2005 02:09 PM
Mail Merge two fields that have multiple lines with new line control code Eric Li Mailmerge 7 May 25th, 2004 06:10 PM


All times are GMT +1. The time now is 09:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.