-----Original Message-----
This is the FIRST query, that performs the initial
calculations:
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)]
FROM associates LEFT JOIN prod_input ON associates.
[Record Number] = prod_input.[Record Number];
Now, this is the query that I was trying to run that
resulted in the error:
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([Talk Time (sec)]) AS
[Talk Time], Avg([Call Work (sec)]) AS [Work Time]
FROM prod_totals;
The error produced is "You tried to execute a query that
does no include the specified expression 'Name' as part
of an aggregate function.
-----Original Message-----
I think that are probably caught between a rock and a hard
place with this. You are probably encountering problems
with Avg where these times are zero or null. If you post
to this thread the SQL that you are currently using and the
error message or description of what is wrong with the
results it produces, I can have a look but cannot offer
any guarantees.
Gerald Stanley MCSD
-----Original Message-----
Maybe it would help a little more if I outlined all of
the data that I have to enter. 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.
-----Original Message-----
Is it possible to normalise this table to only hold one
time per row. This would require an extra column to
indicate which time (1,2,or3) the row related to. If you
could, the average query would be simple. You would also
have the potential to save space if, as in your example,
not all three times are always present.
Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I am creating a database for my work. It is setup that
the raw data will be entered, and then queries do the
calculations for the various reports.
The raw data is primarily 3 different times. Time1,
Time2 and Time3. Since the hours need to be converted
to
minutes, I have set up a Time1(hr), Time1(min), Time2
(hr), etc, then have a query convert the hours to
minutes
and add them to the minutes. I hope that that is clear.
My problem is, I need to get an average for all 3
times.
For example, Time 1 is 60 minutes total, time 2 is 90
minutes, and time3 is 0, I want it to average it out to
what would end up being 75 minutes average. Whatever I
try, either doesn't give the average, or I get a
message "You tried to execute a query that does not
include the specified expression 'Name' as part of an
aggregate function."
Does anyone have any suggestions?
.
.
.
.
.
