Condense Query

[evergrean100]

I would like to know if it is possible to make a regular expression or better way to condense my below query in Access 2003.
Basically I need to query the average with all fields in TableOne that end with _myWord:

select 
((avg(abcd_myWord) +
avg(other_myWord) +
avg(here_myWord) +
avg(cal_myWord) +
avg(jersey_myWord) +
avg(flo_myWord) +
avg(jklm_myWord) +
avg(jax_myWord) +
avg(baewerr_myWord))/9) as avgData
from TableOne

I have used the LIKE and * for fetching wildcards but was hoping I can do something like that in my above sql for the fieldnames?

 

[rac2]

Not really. The arguments to the Avg() function are column names I suppose. Avg() is intended to look at values from a set of rows, the values are all from one column. Or from one expression which might involve several columns. For example you could write

SELECT ( Avg(abcd_myWord + other_myWord + 
            here_myWord + cal_myWord) + 
            jersey_myWord + flo_myWord +
            jklm_myWord + jax_myWord + baewerr_myWord
            ) / 9 )  as avgData
from TableOne

It seems like you might have several columns that could be redefined as two columns with 9 rows instead of 9 columns with one row. Like so

myWord WordOrigin
    5    abcd
    1    other
    7    here
    3    cal
   23    jersey
   17    flo
    4    jklm
    6    jax
  117    baewerr

With this structure you can use GROUP BY get the averages by WordOrigin like this

SELECT WordOrigin, Avg(myWord) AS avgData
FROM  TableOne
GROUP BY WordOrigin

and for the overall average

SELECT Avg(myWord) AS avgData
FROM  TableOne

Incidentally dividing the 9 averages by 9 does not yield the same value as the average over all columns, unless there are values in every column. Possibly there will always be a value, maybe zeros instead of Nulls. If so the first expression above would yield the average over the 9 columns.

Possibly you expect to add columns such as rac_myWord and divide by 10 in the future. This will lead to re-writing the query. If you change the structure then the same query will work no matter how many types of myWords you add.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nicsin]

you can only reference database fields in sql using their whole name. you could possibly do it in code but it's probably more complex than you need. have a look here anyway thread700-1297490

N

 

[evergrean100]

Incidentally dividing the 9 averages by 9 does not yield the same value as the average over all columns, unless there are values in every column. Possibly there will always be a value, maybe zeros instead of Nulls. If so the first expression above would yield the average over the 9 columns.


Thanks, If I have NO nulls and I have zeros and numbers then the average will work?

 

[rac2]

Yes. Assuming zero is a valid score. The problem comes where there is missing data. If zero is used instead of NULL for missing data then you have a problem ; but if zero is a valid score and there is no missing data then you are OK.

May I encourage you to take a look at the article about relational design on the link posted by PHV.

If there is missing data and it is indicated by NULL then

SELECT ( ( Sum(abcd_myWord)   + Sum(other_myWord) +
           Sum(here_myWord)   + Sum(cal_myWord) +
           Sum(jersey_myWord) + Sum(flo_myWord) +
           Sum(jklm_myWord)   + Sum(jax_myWord) + 
           Sum(baewerr_myWord)
          ) / (
               Count(abcd_myWord)   + Count(other_myWord) +
               Count(here_myWord)   + Count(cal_myWord) +
               Count(jersey_myWord) + Count(flo_myWord) +
               Count(jklm_myWord)   + Count(jax_myWord) + 
               Count(baewerr_myWord)
              ) 
       )  as avgData
from TableOne

The Sum() and Count() functions filter the NULLs and provide the correct grand total and count for the overall average.

 

[evergrean100]

Thanks for the info.

I just found out the values that have NULL should not be considered in my computation.

So from what I learned in this post the following values would have an average of 2:

Id   fieldNameHere
1    2
2    1
[COLOR=red]3[/color]
4    3

Id 3 would be a NULL value as inserted in my sql record insert.
2 + 1 + 3 = 6, which is then divided by 3 since the NULL value changes my dividing number from 4 to 3. Is this correct??