Calculation problem

[Steven Voorhees]

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.

 

[Jeff Boyce]

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.

 

[Guest]

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>

.

 

[Jeff Boyce]

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.