Averaging data in two different tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I keep track of quality scoring for telephone agents by week in an Access
database but we recently changed the agents' phone ID numbers. So I had to
create a second table which created a second query and report. I am trying to
build an expression in a query that averages the agents' scores but I am
getting an error that tells me I am not including the date as an aggregate
function. All I want to do is average all of the scores on both tables in one
query. Is this possible?
 
Combine the data into a single table. Build a table of Old-New ID's.

Use this query--
SELECT IIf([EMPID]=[oldid],[newid],[empid]) AS EMP, Avg(PhoneRate.Rate) AS
AvgOfRate
FROM PhoneRate, [Old-New]
WHERE (((IIf([EMPID]=[oldid],[newid],[empid]))=[newid] Or
(IIf([EMPID]=[oldid],[newid],[empid]))=[oldid]))
GROUP BY IIf([EMPID]=[oldid],[newid],[empid]);
 
I keep track of quality scoring for telephone agents by week in an Access
database but we recently changed the agents' phone ID numbers. So I had to
create a second table which created a second query and report. I am trying to
build an expression in a query that averages the agents' scores but I am
getting an error that tells me I am not including the date as an aggregate
function. All I want to do is average all of the scores on both tables in one
query. Is this possible?
Click to expand...

Given that you have not shared any information about the structure of
these tables, or how they are related, all I can say is "maybe, I
don't know".

If you care to post a description of your tables, and the SQL view of
the query shich isn't working, someone might be able to help.


John W. Vinson[MVP]
 
trbecker2 said:
I keep track of quality scoring for telephone agents by week in an Access
database but we recently changed the agents' phone ID numbers. So I had to
create a second table which created a second query and report. I am trying to
build an expression in a query that averages the agents' scores but I am
getting an error that tells me I am not including the date as an aggregate
function. All I want to do is average all of the scores on both tables in one
query. Is this possible?
Click to expand...

trbecker2,

The error *sounds* (as in, maybe) like you have a column or expression
on the SELECT clause of the SELECT statement that is either:

1) Is not enclosed in a scalar function (MAX, AVG, etc.).

2) Is not included on the GROUP BY clause.

When using GROUP BY, all columns expressions on the SELECT clause of
the SELECT statement must be enclosed in a scalar function or be on
the GROUP BY clause.


Sincerely,

Chris O.
 
Back
Top