Averaging data in two different tables

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?
 
G

Guest

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]);
 
J

John Vinson

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?

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]
 
C

Chris2

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?

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top