Calculate an Average of row entry fields

[Guest]

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...

 

[Jason Lepack]

The Avg() function in Access takes the average of an entire COLUMN.

You want this:
[EmployeePerformanceRating]+[WorkOrderPerformanceRating]+
[ContractPerformanceRating]/3

Cheers,
Jason Lepack

 

[Guest]

Can you show us the query? From the sounds of it your query has another
field called ID (e.g. select ID, avg(field1 + field2+ field3) from table).
If you want to use the ID field then you will need to add a group by ID at
the end of the query. I hope this helps.

 

[Guest]

I tried that but some of my those fields have null values. If I divide by
three, the totals will not be right...
--
Fabien Jolivette

The Avg() function in Access takes the average of an entire COLUMN.

You want this:
[EmployeePerformanceRating]+[WorkOrderPerformanceRating]+
[ContractPerformanceRating]/3

Cheers,
Jason Lepack

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...

 

[Michel Walsh]

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

 

[Guest]

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

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

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...

 

[Michel Walsh]

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

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

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

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...

 

[Guest]

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 spreadsheet 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

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

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

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



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...

 

[Jason Lepack]

Create a query like this: (Call it QueryA)
SELECT ID, ShopArea, Month, Year, EPR as PR
FROM yourTable
UNION ALL
SELECT ID, ShopArea, Month, Year, WOPR as PR
FROM yourTable
UNION ALL
SELECT ID, ShopArea, Month, Year, CPR as PR
FROM yourTable

Then create this query:
SELECT ID, ShopArea, Month, Year, avg(PR)
FROM QueryA
WHERE PR is not null
GROUP BY ID, ShopArea, Month, Year

Cheers,
Jason Lepack

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 spreadsheet 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

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

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

Thanks for the info guys...
Mr. Walsh. When you say normalize, do you mean make a separate tablefor
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
:
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 numberof
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
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...

- Show quoted text -

 

[Michel Walsh]

Then can try the complex expression involving Nz, previously posted.

You probably have a typographic error, right now, with
EmployeePerformanceRating



Just if you are still interested, an alternative design could have been:

The first table, the actual one, would get only:

Id, ShopArea, Date, performance, pc
001 Carpentry 1 June 2007 EPR 85
001 Carpentry 1 June 2007 WOPR 95
001 Carpentry 1 June 2007 CPR 75


If, for a given month, employeeid 002 does not have CPR, just don't add
the record with performance value = "CPR", or use a NULL under the column
pc:

002 Carpentry 1 June 2007 CPR null



and then, it is a matter to use:


SELECT id, date, AVG(pc)
FROM tableName
GROUP BY id, date


Sure, if id 001 is ALWAYS in the same Shop Area, and so on for each and
every id, the ShopArea information can be put back in the employee table,
and removed from here.




Hoping it may help,
Vanderghast, Access MVP

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 spreadsheet 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

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

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


:

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



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...

 

[Guest]

IT WORKED!!! Thanks
--
Fabien Jolivette

Create a query like this: (Call it QueryA)
SELECT ID, ShopArea, Month, Year, EPR as PR
FROM yourTable
UNION ALL
SELECT ID, ShopArea, Month, Year, WOPR as PR
FROM yourTable
UNION ALL
SELECT ID, ShopArea, Month, Year, CPR as PR
FROM yourTable

Then create this query:
SELECT ID, ShopArea, Month, Year, avg(PR)
FROM QueryA
WHERE PR is not null
GROUP BY ID, ShopArea, Month, Year

Cheers,
Jason Lepack

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 spreadsheet 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

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

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

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..

"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

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...

- Show quoted text -

 

[Isa]

Michel

I have written the below code in a similar situation and it works so well,
but I don't know how to deal when one or two of the fields have zeros. Any
help will be greatly appreciated.
--
Isa

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

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...

 

[John Spencer]

Do you mean you want to ignore zeroes in calculating the average?

If so, try

( Nz(f1, 0)+ Nz(f2, 0) + Nz(f3, 0) ) / (3+ (f1 IS NULL or F1 = 0) + (f2 IS
NULL Or F2 = 0 )+ (f3 IS NULL Or f3 = 0))

One problem here is that if all three values are null or zero then you will
end up with a divide by zero error. So now you have to add a test for that
possibility. And things are even more complex - you end up with something
that looks like the following. This might even work if I've gotten all the
left and right parens balanced and in the right places.


IIF((f1 IS NULL or F1 = 0)
AND (f2 IS NULL Or F2 = 0 )
AND (f3 IS NULL Or f3 = 0),Null,
( Nz(f1, 0)+ Nz(f2, 0) + Nz(f3, 0) ) / (3+ (f1 IS NULL or F1 = 0)
+ (f2 IS NULL Or F2 = 0 )
+ (f3 IS NULL Or f3 = 0)))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Isa]

John,

Thank you so much for your help. It works very well.