Update Query Problem

[Guest]

I am trying to run a simple Update Query:

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
Avg([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]![Week_4]+[tbl_Wages]![Week_5]);

I get the following error.

You tried to execute a query that does not include the specified expression
‘Average_Rate’ as part of an aggregate function.

Could someone tell me what I am doing wrong? Thank You

 

[Guest]

I have to start by saying that there is no point on saving a calculated
field, its just a field that you will have to keep on updating, and it can
cause alot of mistakes.
There error message you getting I assume it because of the avg function the
query looking for a group by query, try this instead

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
(nz([tbl_Wages]![Week_1],0)+nz([tbl_Wages]![Week_2],0)+nz([tbl_Wages]![Week_3],0)+nz([tbl_Wages]![Week_4],0)+nz([tbl_Wages]![Week_5],0))/5

 

[Chaim]

Is this what you really intend? Avg() doesn't work as you are trying to use
it. Presumably, you want to store the average of each row in the
Average_Rate field (more on this later). In which case you probably want
something more along the lines of:

([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]!
[Week_4]+[tbl_Wages]![Week_5])/5;

The way you are trying to do this will set all of the Average_Rate values to
the same value.

It is not good design to store derivable values in a table, except in
situations where it is too painful to calculate the value when you need it.
Additionally, storing weeks of data in a table where each week has its own
field is a problem. What happens when you need to average 6 weeks of data?
Better approach is to have a table that looks like (employee id, week
number, wages) (I am assuming you are doing this for several employees. If
it's only to keep track of a single individuals wages, then you don't really
need an id field of any kind). And calculate the average on the fly.

Good Luck!

 

[Guest]

My table does store this "Average" but only for two weeks, it is then
updated. Hourly wage for the past 4 weeks is held in the table. Before
entering the current wage rate for this pay period I have a VB sequence
update week 5 with week 4, week 4 with week 3 etc. This leaves week 1 empty
or null. Week one hourly wage is then entered and I want the average of the
5 wage values. Yes I will store it for reference for two weeks but then it
will update. I wanted to use the AVG function because it accounts for null
values which may occur. If there is a better way I am open to ideas, I have
tried the avg function as written in a query and got the same error message.

Is this what you really intend? Avg() doesn't work as you are trying to use
it. Presumably, you want to store the average of each row in the
Average_Rate field (more on this later). In which case you probably want
something more along the lines of:

([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]!
[Week_4]+[tbl_Wages]![Week_5])/5;

The way you are trying to do this will set all of the Average_Rate values to
the same value.

It is not good design to store derivable values in a table, except in
situations where it is too painful to calculate the value when you need it.
Additionally, storing weeks of data in a table where each week has its own
field is a problem. What happens when you need to average 6 weeks of data?
Better approach is to have a table that looks like (employee id, week
number, wages) (I am assuming you are doing this for several employees. If
it's only to keep track of a single individuals wages, then you don't really
need an id field of any kind). And calculate the average on the fly.

Good Luck!
--

Chaim

I am trying to run a simple Update Query:

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
Avg([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wage
s]![Week_4]+[tbl_Wages]![Week_5]);

I get the following error.

You tried to execute a query that does not include the specified expression
'Average_Rate' as part of an aggregate function.

Could someone tell me what I am doing wrong? Thank You

 

[Guest]

In the example I gave you I used the NZ function to replace the the null with
zero, but what you need is, incase there are two fields with null the you
want to devide the value by three, then try this

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
(nz([tbl_Wages]![Week_1],0)+nz([tbl_Wages]![Week_2],0)+nz([tbl_Wages]![Week_3],0)+nz([tbl_Wages]![Week_4],0)+nz([tbl_Wages]![Week_5],0))/(iif([tbl_Wages]![Week_1]
is null,0,1)+iif([tbl_Wages]![Week_2] is null,0,1)+iif([tbl_Wages]![Week_3]
is null,0,1)+iif([tbl_Wages]![Week_4] is null,0,1)+iif([tbl_Wages]![Week_5]
is null,0,1))

My table does store this "Average" but only for two weeks, it is then
updated. Hourly wage for the past 4 weeks is held in the table. Before
entering the current wage rate for this pay period I have a VB sequence
update week 5 with week 4, week 4 with week 3 etc. This leaves week 1 empty
or null. Week one hourly wage is then entered and I want the average of the
5 wage values. Yes I will store it for reference for two weeks but then it
will update. I wanted to use the AVG function because it accounts for null
values which may occur. If there is a better way I am open to ideas, I have
tried the avg function as written in a query and got the same error message.

Is this what you really intend? Avg() doesn't work as you are trying to use
it. Presumably, you want to store the average of each row in the
Average_Rate field (more on this later). In which case you probably want
something more along the lines of:

([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]!
[Week_4]+[tbl_Wages]![Week_5])/5;

The way you are trying to do this will set all of the Average_Rate values to
the same value.

It is not good design to store derivable values in a table, except in
situations where it is too painful to calculate the value when you need it.
Additionally, storing weeks of data in a table where each week has its own
field is a problem. What happens when you need to average 6 weeks of data?
Better approach is to have a table that looks like (employee id, week
number, wages) (I am assuming you are doing this for several employees. If
it's only to keep track of a single individuals wages, then you don't really
need an id field of any kind). And calculate the average on the fly.

Good Luck!
--

Chaim

I am trying to run a simple Update Query:

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
Avg([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wage
s]![Week_4]+[tbl_Wages]![Week_5]);

I get the following error.

You tried to execute a query that does not include the specified expression
'Average_Rate' as part of an aggregate function.

Could someone tell me what I am doing wrong? Thank You

 

[Guest]

Ofer: Thank you for the solution . . . it works just fine. Can you or
anyone explain why the AVG function does not work in the query?

I am going to rework what I am doing, I will base the calculations on a
query and store the final averages in a table as a record of the wage rate,
for how long they will be stored, the powers that be will decide. I just
want to understand how the AVG function works and I thought it did take into
account null values.

Thanks for the input and assistance!

In the example I gave you I used the NZ function to replace the the null with
zero, but what you need is, incase there are two fields with null the you
want to devide the value by three, then try this

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
(nz([tbl_Wages]![Week_1],0)+nz([tbl_Wages]![Week_2],0)+nz([tbl_Wages]![Week_3],0)+nz([tbl_Wages]![Week_4],0)+nz([tbl_Wages]![Week_5],0))/(iif([tbl_Wages]![Week_1]
is null,0,1)+iif([tbl_Wages]![Week_2] is null,0,1)+iif([tbl_Wages]![Week_3]
is null,0,1)+iif([tbl_Wages]![Week_4] is null,0,1)+iif([tbl_Wages]![Week_5]
is null,0,1))

My table does store this "Average" but only for two weeks, it is then
updated. Hourly wage for the past 4 weeks is held in the table. Before
entering the current wage rate for this pay period I have a VB sequence
update week 5 with week 4, week 4 with week 3 etc. This leaves week 1 empty
or null. Week one hourly wage is then entered and I want the average of the
5 wage values. Yes I will store it for reference for two weeks but then it
will update. I wanted to use the AVG function because it accounts for null
values which may occur. If there is a better way I am open to ideas, I have
tried the avg function as written in a query and got the same error message.

Is this what you really intend? Avg() doesn't work as you are trying to use
it. Presumably, you want to store the average of each row in the
Average_Rate field (more on this later). In which case you probably want
something more along the lines of:

([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]!
[Week_4]+[tbl_Wages]![Week_5])/5;

The way you are trying to do this will set all of the Average_Rate values to
the same value.

It is not good design to store derivable values in a table, except in
situations where it is too painful to calculate the value when you need it.
Additionally, storing weeks of data in a table where each week has its own
field is a problem. What happens when you need to average 6 weeks of data?
Better approach is to have a table that looks like (employee id, week
number, wages) (I am assuming you are doing this for several employees. If
it's only to keep track of a single individuals wages, then you don't really
need an id field of any kind). And calculate the average on the fly.

Good Luck!
--

Chaim


I am trying to run a simple Update Query:

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =

Avg([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wage
s]![Week_4]+[tbl_Wages]![Week_5]);

I get the following error.

You tried to execute a query that does not include the specified
expression
'Average_Rate' as part of an aggregate function.

Could someone tell me what I am doing wrong? Thank You

 

[Guest]

As I said in my first post the avg or sum are function of a group by query,
and you can't use them in a normal query.

Ofer: Thank you for the solution . . . it works just fine. Can you or
anyone explain why the AVG function does not work in the query?

I am going to rework what I am doing, I will base the calculations on a
query and store the final averages in a table as a record of the wage rate,
for how long they will be stored, the powers that be will decide. I just
want to understand how the AVG function works and I thought it did take into
account null values.

Thanks for the input and assistance!

In the example I gave you I used the NZ function to replace the the null with
zero, but what you need is, incase there are two fields with null the you
want to devide the value by three, then try this

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =
(nz([tbl_Wages]![Week_1],0)+nz([tbl_Wages]![Week_2],0)+nz([tbl_Wages]![Week_3],0)+nz([tbl_Wages]![Week_4],0)+nz([tbl_Wages]![Week_5],0))/(iif([tbl_Wages]![Week_1]
is null,0,1)+iif([tbl_Wages]![Week_2] is null,0,1)+iif([tbl_Wages]![Week_3]
is null,0,1)+iif([tbl_Wages]![Week_4] is null,0,1)+iif([tbl_Wages]![Week_5]
is null,0,1))

My table does store this "Average" but only for two weeks, it is then
updated. Hourly wage for the past 4 weeks is held in the table. Before
entering the current wage rate for this pay period I have a VB sequence
update week 5 with week 4, week 4 with week 3 etc. This leaves week 1 empty
or null. Week one hourly wage is then entered and I want the average of the
5 wage values. Yes I will store it for reference for two weeks but then it
will update. I wanted to use the AVG function because it accounts for null
values which may occur. If there is a better way I am open to ideas, I have
tried the avg function as written in a query and got the same error message.

:

Is this what you really intend? Avg() doesn't work as you are trying to use
it. Presumably, you want to store the average of each row in the
Average_Rate field (more on this later). In which case you probably want
something more along the lines of:

([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wages]!
[Week_4]+[tbl_Wages]![Week_5])/5;

The way you are trying to do this will set all of the Average_Rate values to
the same value.

It is not good design to store derivable values in a table, except in
situations where it is too painful to calculate the value when you need it.
Additionally, storing weeks of data in a table where each week has its own
field is a problem. What happens when you need to average 6 weeks of data?
Better approach is to have a table that looks like (employee id, week
number, wages) (I am assuming you are doing this for several employees. If
it's only to keep track of a single individuals wages, then you don't really
need an id field of any kind). And calculate the average on the fly.

Good Luck!
--

Chaim


I am trying to run a simple Update Query:

UPDATE tbl_Wages SET tbl_Wages.Average_Rate =

Avg([tbl_Wages]![Week_1]+[tbl_Wages]![Week_2]+[tbl_Wages]![Week_3]+[tbl_Wage
s]![Week_4]+[tbl_Wages]![Week_5]);

I get the following error.

You tried to execute a query that does not include the specified
expression
'Average_Rate' as part of an aggregate function.

Could someone tell me what I am doing wrong? Thank You