Re: Calculate an Average of row entry fields

Ok, I somewhat understand your set-up, but the only thing is that I have more
then one QTY that I want to calculate an average for.

Id, ShopArea, Month, Year, EmployeePerformanceRating, W.O.P.R., C.P.R.
001 Carpentry June 2007 85 95
75

All I want to do is calculate a total average for E.P.R, W.O.P.R, and C.P.R
Which is 85.

Not all of my shop areas will have contract performance ratings at the end
on the month, therefore the field will be left null. I tried using the
select query:

SELECT Production_Summary.ID, Production_Summary.Month,
Avg([Production_Summary].[EmployeePerformanceRating]+[Production_Summary].[WorkOrderPerformanceRating]+[Production_Summary].[ContractServiceRating])
AS TotalAverage
FROM Production_Summary
GROUP BY Production_Summary.ID, Production_Summary.Month;

but get the error message: Enter Parameter Value
Production_Summary.WorkOrderPerformanceRating

I already have this spread*** set-up in Excel with formulas and
everything, but I don't want to continue to use it because the queries in
Access are more helpful when it's time to generate different types of
reports. Please advise...
--
Fabien Jolivette


"Michel Walsh" wrote:

Nope, not a table for each field, but instead of:


PersonID, January, February, March, April ' fields name
1010, 44, 69, null, 16
1011, 65, null, 2, 22 ' data


something like:


PersonID, When, Quantity ' fields name
1010 Jan 44
1010 Feb 69
1010 Apr 16
1011 Jan 65
1011 Mar 2
1011 Apr 22





Ok, it is harder to read, for a human, but TABLE are not to be read, FORMS
are for human interraction, not TABLES. So, what does our table bring? easy
computation. So, you want average by people, simple:

SELECT personID, AVG(qty)
FROM table
GROUP BY personID


That's all. You want average by month, again, simple:

SELECT when, AVG(qty)
FROM table
GROUP BY when


Or you want the maximum, by person, but only among Jan, Feb and Mar values?

SELECT personID, MAX(qty)
FROM table
WHERE when IN("Jan", "Feb", "Mar")
GROUP BY personID



Since everything is vertical, the database 'tool' is at its optimum of
'simplicity'.


In your case, a normalized design, instead of:


id, field1, field2, field3
1010, 10, 11, 12
1011, 20, 21, 22

we can have


id, value
1010, 10
1010, 11
1010, 12
1011, 20
1011, 21
1011, 22


or


id, fromWho, value
1010, f1, 10
1010, f2, 11
1010, f3, 12
1011, f1, 20
1011, f2, 21
1011, f3, 22



and then:


SELECT id, AVG(value)
FROM tablename
GROUP BY id


is all what would be needed.



Hoping it may help,
Vanderghast, Access MVP



"F Jolivette" <FJolivette@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:937BFE8C-64D2-4361-B061-148925D16CEA@xxxxxxxxxxxxxxxx
Thanks for the info guys...

Mr. Walsh. When you say normalize, do you mean make a separate table for
each of the fields previously mentioned? If so, how do I tie it all back
together again to calculate the the total average and will this create
more
of a problem when creating a form that will tie together month, year,
division, shop area, employee productivity rating, work order rating,
contract rating, and comments. It seems as though it would, but I'm not
an
Access guru..
--
Fabien Jolivette


"Michel Walsh" wrote:

SELECT ( Nz(f1, 0)+ Nz(f2, 0) + Nz(f3, 0) ) / (3+ (f1 IS NULL) + (f2 IS
NULL)+ (f3 IS NULL))
FROM ...


Note: it is ugly, yes, because a db is primary used to work vertically,
not
horizontally. That is also called "normalisation" of the design.

Note: with Jet, a BOOLEAN true, as result of a comparison, is -1, and
false is 0. So
(3+ (f1 IS NULL) + (f2 IS NULL)+ (f3 IS NULL)) computes the number of
fields among f1, f2 and f3, that have a not-null value. If that value is
0,
an error will occur (division by zero), so, a more complex, but safer,
solution would be:


SELECT iif( (3+ (f1 IS NULL) + (f2 IS NULL)+ (f3 IS NULL)) =0, Null,
( Nz(f1, 0)+ Nz(f2, 0) + Nz(f3, 0) ) / (3+ (f1 IS NULL) + (f2 IS
NULL)+
(f3 IS NULL)) )
FROM ...


If that is not enough to convince you to normalize your design... well,
that
is your problem, in the end, so why would I complain? :-)



Vanderghast, Access MVP



"F Jolivette" <FJolivette@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:73911200-6B1B-4CDC-AA14-8ADD00BD6E3D@xxxxxxxxxxxxxxxx
I have a query that I want to calculate the average of three different
fields
pulled from a table: [EmployeePerformanceRating],
[WorkOrderPerformanceRating], and [ContractPerformanceRating]. I tried
to
create another query field "TotalPerformanceRating" using the following
experession to calculate the average of the three fields mentioned
above:
Avg([EmployeePerformanceRating] +[WorkOrderPerformanceRating]
+[ContractPerformanceRating]). However, I keep getting the error
message
"
You tried to execute a query that does not include the specified
expression
'ID' as part of the aggregate function.

FYI
The query is set up with the following fields pulled from a table: ID
(automatic number assigned by Access per row entry), Month, Year,
Divsion,
ShopName, EmployeePerformanceRating, etc.

All I need is to calculate the average of those three fields per row
entry.
How do I set-up the query to accomplish this simple task? Please
Help...
--
Fabien Jolivette


.

Loading