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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#3
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#4
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#5
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#6
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#7
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#8
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#9
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
#10
|
|||
|
|||
Phone call log, multiple fields for different times
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. |
|
Thread Tools | |
Display Modes | |
|
|
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 04:19 AM |
Relating multiple fields in table to multiple fields in another ta | RandallMJ | Database Design | 3 | January 9th, 2006 02:46 PM |
Looking up information across multiple fields | Someone | General Discussion | 2 | December 18th, 2005 06: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 |