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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |