averaging multiple field

[Guest]

I am trying to average mulitple fields (52 to be exact) and the query won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:
Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Rick B]

If yo uare adding them and then dividing by 2, that is an average. You
don't need the "AVG" in your formula.

 

[Rick B]

Make that "dividing by 52"

--
Rick B

If yo uare adding them and then dividing by 2, that is an average. You
don't need the "AVG" in your formula.

--
Rick B

I am trying to average mulitple fields (52 to be exact) and the query won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:

Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+

[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Guest]

when I take out the "avg" I get this message:

You tried to execute a query that does not include the specified expression
<Expr1:
(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)>
as part of an aggregate function. (Error 3122)

Make that "dividing by 52"

--
Rick B

If yo uare adding them and then dividing by 2, that is an average. You
don't need the "AVG" in your formula.

--
Rick B

I am trying to average mulitple fields (52 to be exact) and the query won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:

Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+

[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Guest]

You really need to change your database design. Do not use 52 fields but a
date and a value field.

 

[Lachlan Mullen]

Not sure why it's saying that, try clearing out the expression and
running your query without it to be sure it's not a SQL problem, than
try re-entering it as follows:

Average:
([wk1]+[wk2]+[wk3]+[wk4]+[wk5­]+[wk6]+[wk7]+[wk8]+[wk9]+[wk1­0]+[wk11]+[wk12]+[wk13]+[wk14]­+[wk15]+[wk16]+[wk17]+[wk18]+[­wk19]+[wk20]+[wk21]+[wk22]+[wk­23]+[wk24]+[wk25]+[wk26]+[wk27­]+[wk28]+[wk29]+[wk30]+[wk31]+­[wk32]+[wk33]+[wk34]+[wk35]+[w­k36]+[wk37]+[wk38]+[wk39]+[wk4­0]+[wk41]+[wk42]+[wk43]+[wk44]­+[wk45]+[wk46]+[wk47]+[wk48]+[­wk49]+[wk50]+[wk51]+[wk52])/52

Usually that error comes when you are totaling by a certian field, but
then trying to also calculate off the same fields in a seperate
expression. Are you using a "Group By" type of query? If that doesn't
work, reply with a posting of your SQL... that may help us take this to
the next level.

Lach

 

[Michel Walsh]

Hi,


If you use a GROUP BY (per year, maybe), you have to aggregate the SELECTed
expression. MIN, MAX, FIRST or LAST can do, if there is just one record by
group. If you have a NULL value in any [wkXX] , the result is also NULL.
If a NULL means 0, in that case, you can use Nz( wk16, 0 ) instead of
wk16. That would be much easier if you could get a VERTICAL representation
of your data, AS USUAL for DATABASE, instead of 52 fields, horizontally.
Maybe you already got?




SELECT DatePart("'yyyy", myDate) as TheYear,
DatePart('ww', myDate) as TheWeek,
SUM(myAmount) As TotalPerWeek
FROM myTable
GROUP BY DatePart("'yyyy", myDate), DatePart('ww', myDate)



can produce such a result. If that query is saved under the name Q1, then,
the average, by year, is just


SELECT TheYear, AVG(TotalPerWeek)
FROM q1
GROUP BY TheYear




Isn't it that these two queries look cool in comparison to the lengthy
addition you are obliged to modify?



Hoping it may help,
Vanderghast, Access MVP

when I take out the "avg" I get this message:

You tried to execute a query that does not include the specified
expression
<Expr1:
(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)>
as part of an aggregate function. (Error 3122)

Make that "dividing by 52"

--
Rick B

If yo uare adding them and then dividing by 2, that is an average. You
don't need the "AVG" in your formula.

--
Rick B



I am trying to average mulitple fields (52 to be exact) and the query
won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:

Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+
[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Guest]

That's not practical for our purposes...it would take hours to put the date
in for 400 people every week.

You really need to change your database design. Do not use 52 fields but a
date and a value field.

I am trying to average mulitple fields (52 to be exact) and the query won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:
Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Guest]

Okay...I tried your equation and get the same message. I am using a group by
query. When I run the query without the expressions I get the other field
(Ministry) fine.

Let me give a little more background. I have a table called attendance. It
lists 52 fields for 3 different areas. I am trying to set a query that will
average all 52 fields separately for each of the 3 areas. I have 3
expressions set up in the same query that are the same format, with the
fields changed to the appropriate names for the different ares. (ie. wk1,
wk2, wk3, etc. wk1S, wk2S, wk3S, etc. wk1V, wk2V, wk3V, etc.)

Finally, I don't know how to post my SQL...sorry! Thanks for taking teh
time to help me with this.

Michaela

 

[Lachlan Mullen]

Michaela,

Could you break it into seperate databases? You could have one
"Master" database that linked into the other smaller ones, that way the
"Master" one would stay small. You just would want to make sure you're
compacting and repairing the linked databases regularly (like every
week).

To post your SQL, (in your query) go to View> SQL View. Copy and paste
rom there then close that box.

Lach

 

[Guest]

Here's the SQL:

SELECT Attendance.Ministry,
Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)
AS Expr1,
Avg(([wk1V]+[wk2V]+[wk3V]+[wk4V]+[wk5V]+[wk6V]+[wk7V]+[wk8V]+[wk9V]+[wk10V]+[wk11V]+[wk12V]+[wk13V]+[wk14V]+[wk15V]+[wk16V]+[wk17V]+[wk18V]+[wk19V]+[wk20V]+[wk21V]+[wk22V]+[wk23V]+[wk24V]+[wk25V]+[wk26V]+[wk27V]+[wk28V]+[wk29V]+[wk30V]+[wk31V]+[wk32V]+[wk33V]+[wk34V]+[wk35V]+[wk36V]+[wk37V]+[wk38V]+[wk39V]+[wk40V]+[wk41V]+[wk42V]+[wk43V]+[wk44V]+[wk45V]+[wk46V]+[wk47V]+[wk48V]+[wk49V]+[wk50V]+[wk51V]+[wk52V])/52)
AS Expr2,
Avg(([wk1S]+[wk2S]+[wk3S]+[wk4S]+[wk5S]+[wk6S]+[wk7S]+[wk8S]+[wk9S]+[wk10S]+[wk11S]+[wk12S]+[wk13S]+[wk14S]+[wk15S]+[wk16S]+[wk17S]+[wk18S]+[wk19S]+[wk20S]+[wk21S]+[wk22S]+[wk23S]+[wk24S]+[wk25S]+[wk26S]+[wk27S]+[wk28S]+[wk29S]+[wk30S]+[wk31S]+[wk32S]+[wk33S]+[wk34S]+[wk35S]+[wk36S]+[wk37S]+[wk38S]+[wk39S]+[wk40S]+[wk41S]+[wk42S]+[wk43S]+[wk44S]+[wk45S]+[wk46S]+[wk47S]+[wk48S]+[wk49S]+[wk50S]+[wk51S]+[wk52S])/52) AS Expr3
FROM Attendance
GROUP BY Attendance.Ministry;

I don't really see how, separate databases would help calculate the data. I
guess I'm a little clueless and a challenge to work with! )

Michaela

 

[Guest]

By the way, when I entered the expression you gave, and deleted the other two
(just to make sure it worked) it would go to datasheet view, but no
calculations were made. The fields were all empty.

Michaela

 

[Guest]

Set the default for the field in the table to Date()-1 and the current date
minus one (yesterday) is automatically filled in for each new record. It can
be edited if you happen to be late on posting.

That's not practical for our purposes...it would take hours to put the date
in for 400 people every week.

You really need to change your database design. Do not use 52 fields but a
date and a value field.

I am trying to average mulitple fields (52 to be exact) and the query won't
calculate them for me. I am trying to average attendance records for each
year. This is my expression:

Expr1:
Avg(([wk1]+[wk2]+[wk3]+[wk4]+[wk5]+[wk6]+[wk7]+[wk8]+[wk9]+[wk10]+[wk11]+[wk12]+[wk13]+[wk14]+[wk15]+[wk16]+[wk17]+[wk18]+[wk19]+[wk20]+[wk21]+[wk22]+[wk23]+[wk24]+[wk25]+[wk26]+[wk27]+[wk28]+[wk29]+[wk30]+[wk31]+[wk32]+[wk33]+[wk34]+[wk35]+[wk36]+[wk37]+[wk38]+[wk39]+[wk40]+[wk41]+[wk42]+[wk43]+[wk44]+[wk45]+[wk46]+[wk47]+[wk48]+[wk49]+[wk50]+[wk51]+[wk52])/52)

Thanks for any help you can give me,
Michaela

 

[Michel Walsh]

Hi,

You supply it as default value, = Now( ), or = Date()


Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


you HAVE TO use AVG, or similar aggregate function, since you use Group By.


When you say it is not working, what do you mean? an error and no result or
some erroneous result? in the last case, in what are they erroneous?
because there are NULL ? because there is data for only a couple of week
and the result is still being computed on a basis of 52 weeks, rather than
the couple of week? because the week 53, or the week 1, is incorrectly
computed ?


Vanderghast, Access MVP

 

[Guest]

They are blank...there is no data calculated whatsoever. It worked fine when
I set up the expressions, but once I added other data (like whose attendance
it was), they went blank and don't calculate and I don't receive an error
message at all.

Thanks again for all the help you guys have been providing!

Michaela

 

[Michel Walsh]

Hi,


That is probably because ONE of the week value is null (blank). Null values
propagate themselves, through addition. You need to add Nz( ) around each
week field, like : Nz(week34, 0) instead of just week34, making the
whole expression like:


AVG( Nz( week1, 0) + Nz(week2, 0) + ..... )


Sure, test before with use two or three fields, before typing the whole
formula, to see if this is really the problem ... and the solution


Hoping it may help,
Vanderghast, Access MVP