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
|
|||
|
|||
Calculation problem
I took on a project at my work, that has me stumped now.
I am trying to get this done, since currently, the raw data is entered into an Excel spreadsheet, that performs calculations. The results from those calculations are then copied and pasted to another spreadsheet(which is different everyday) that performs additional calculations, and then others take the results and calculations from the second spreadsheet and paste into 2 or 3 other spreadsheets. The database is designed to streamline the process, since everyone utilizes the same data, just for different people. This database if for a hotel and ticketing call center. Each agent has 3 separate telephone logons, that they may or may not use daily, but is still used in statistics. Basically, in a query, I am taking IC talk-h(main), IC talk-h(hotel), and IC talk-h(tw) adding them together, multipling by 60 and adding to the total for the minutes. The resulting query field is "Talk Time". In the query, I then try to Average the "talk time" results. If I use AVG([talk time]) or even try averaging all three, I get the aggregate error. If I try using totals, I don't get the error, but it doesn't average. Everything looks and works beautiful in the database, except for this.As for the previous response, it would be nice, but I don't think it is possible. The information that I have to collect for each day is: Agent, date, logon hrs(main), logon min(main), # ans(main), IC talk hrs (main), IC talk min(main), Work min(main), Work sec(main) Break hrs(main), and Break min(main). This is repeated but with main, hotel, and tw in parenthesis. So three possible sets of numbers for each agent, each day. |
#2
|
|||
|
|||
Calculation problem
Steven
(see comments in-line) results. If I use AVG([talk time]) or even try averaging all three, I get the aggregate error. We're not there ... we can't see what you're looking at. What "aggregate error"? If I try using totals, I don't get the error, but it doesn't average. Are you working in a query? If so, please post the SQL statement. Everything looks and works beautiful in the database, except for this.As for the previous response, it would be nice, but I don't think it is possible. The information I don't understand ... what "previous response"? that I have to collect for each day is: Agent, date, logon hrs(main), logon min(main), # ans(main), IC talk hrs (main), IC talk min(main), Work min(main), Work sec(main) Break hrs(main), and Break min(main). This is repeated but with main, hotel, and tw in parenthesis. So three possible sets of numbers for each agent, each day. It sounds like your Access database is structured the same way that Excel is, with repeating columns. This is not a good idea, as Access is a relational database. Without more information, I can only guess that you are having problems with the aggregate functions (e.g., Avg()) because you are trying to use them across columns, rather than within a single column. -- More info, please ... Jeff Boyce Access MVP |
#3
|
|||
|
|||
Calculation problem
There are 2 separate queries that are being used. Here
is the first one, then following will be the second. SELECT associates.Name, associates.Coach, prod_input.Date, associates.[Target Rate], prod_input! [Logon-h (Main)]+prod_input![Logon-h (Hotel)]+prod_input! [Logon-h (TW)] AS [Logon-H], prod_input![Logon-m (Main)] +prod_input![Logon-m (Hotel)]+prod_input![Logon-m (TW)] AS [Logon-M], prod_input![#ANS (Main)]+prod_input![#ANS (Hotel)]+prod_input![#ANS (TW)] AS [#ANS], prod_input![IC talk-H (Main)]+prod_input![IC talk-H (Hotel)]+prod_input! [IC talk-H (TW)] AS [ICTalk-M], prod_input![IC talk-M (Main)]+prod_input![IC talk=M (Hotel)]+prod_input![IC talk=M (TW)] AS [ICTalk-S], prod_input![Work Ave-M (Main)] +prod_input![Work Ave-M (Hotel)]+prod_input![Work Ave-M (TW)] AS [WorkAve-M], prod_input![WorkAve-S (Main)] +prod_input![WorkAve-S (Hotel)]+prod_input![WorkAve-S (TW)] AS [WorkAve-S], prod_input![Break Total-H (Main)] +prod_input![Break Total-H (Hotel)]+prod_input![Break Total-H (TW)] AS [Break-H], prod_input![Break Total-M (Main)]+prod_input![Break Total-M (Hotel)]+prod_input! [Break Total-M (TW)] AS [Break-M], [Logon-H]*60+[Logon-M] AS [Log on Time (Min)], [Break-H]*60+[Break-M] AS [Break (minutes)], [#ANS] AS [# Calls Answered], [ICTalk-M]*60+ [ICTalk-S] AS [Talk Time (sec)], [WorkAve-M]*60+[WorkAve- S] AS [Call Work (sec)], prod_input![IC talk-H (Main)] *60+prod_input![IC talk-M (Main)] AS [ictalk-all], prod_input![IC talk-H (Hotel)]*60+prod_input![IC talk=M (Hotel)] AS [ictalk-hotel], prod_input![IC talk-H (TW)] *60+prod_input![IC talk=M (TW)] AS [ictalk-tw] FROM associates LEFT JOIN prod_input ON associates. [Record Number]=prod_input.[Record Number]; Second Query: SELECT prod_totals.Name, prod_totals.Coach, prod_totals.Date, prod_totals.[Target Rate], prod_totals. [Log on Time (Min)], prod_totals.[Break(minutes)], prod_totals.[# Calls Answered], Avg(prod_totals.[Talk Time (sec)]) AS [Talk Time (sec)], Avg(prod_totals.[Call Work (sec)]) AS [Work Time] FROM prod_totals GROUP BY prod_totals.Name, prod_totals.Coach, prod_totals.Date, prod_totals.[Target Rate], prod_totals. [Log on Time (Min)], prod_totals.[Break(minutes)], prod_totals.[# Calls Answered]; I am just at a complete loss now. -----Original Message----- Steven (see comments in-line) results. If I use AVG([talk time]) or even try averaging all three, I get the aggregate error. We're not there ... we can't see what you're looking at. What "aggregate error"? If I try using totals, I don't get the error, but it doesn't average. Are you working in a query? If so, please post the SQL statement. Everything looks and works beautiful in the database, except for this.As for the previous response, it would be nice, but I don't think it is possible. The information I don't understand ... what "previous response"? that I have to collect for each day is: Agent, date, logon hrs(main), logon min(main), # ans(main), IC talk hrs (main), IC talk min(main), Work min(main), Work sec (main) Break hrs(main), and Break min(main). This is repeated but with main, hotel, and tw in parenthesis. So three possible sets of numbers for each agent, each day. It sounds like your Access database is structured the same way that Excel is, with repeating columns. This is not a good idea, as Access is a relational database. Without more information, I can only guess that you are having problems with the aggregate functions (e.g., Avg()) because you are trying to use them across columns, rather than within a single column. -- More info, please ... Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Calculation problem
Steven
Based on the fieldnames I'm seeing in the first query, my sense of "spreadsheetness" is very strong. Anytime you need to add columns together, Excel is a great tool. But Access' aggregate functions work on a single column, and aren't designed to span columns. Take a look at Access HELP on the topic of "normalization" and see if you get any ideas for restructuring your data. Try the Analyze tool and see what recommendation Access offers for modifying your table structure. A first thought (without knowing more about your actual structure) might be to have a table that holds an amount (length of call), and a category (type of call), and maybe a second (on which line). These categories would be lookup tables in their own right. Not suggesting that it would work for you, but when I get totally confused, I usually start back at the beginning, adding one factor at a time, rather than try to puzzle out the entire collection at once. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|