More efficient way to query fields

[teser3]

I would like to know if it is possible to do a pattern match 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:

Here is what I am using:

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(jave_myWord) +
avg(terenv_myWord) +
avg(baewerr_myWord))/11) as avgData
from TableOne

[David F Cox]

Firstly this task would be much easier if the design were normalised to have
another table with fields

desc text (contains "abcd", "other" etc)
myword number

Secondly avg(this) + avg(that) + etc
rarely returns the results that the user is hoping for.
e.g.
this 1 item value 999
that 999 items value 1
do you want to see 1000 as the result?

<te****@hotmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.com...

>I would like to know if it is possible to do a pattern match 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:

Here is what I am using:

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(jave_myWord) +
avg(terenv_myWord) +
avg(baewerr_myWord))/11) as avgData
from TableOne

['69 Camaro]

Hi.

Here is what I am using:

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(jave_myWord) +
avg(terenv_myWord) +
avg(baewerr_myWord))/11) as avgData
from TableOne

First, adding averages and then dividing by the total number of averages is
seldom going to yield a meaningful value. If the table were normalized, this
query would be much easier and more efficient, but until you normalize it, you
can use a UNION ALL query, then average these values. Try:

SELECT abcd_myWord AS MyWord
FROM TableOne
UNION ALL
SELECT other_myWord AS MyWord
FROM TableOne
.. . .
UNION ALL
SELECT baewerr_myWord AS MyWord
FROM TableOne;

Save the query and name it qryUnionWords. Create another query:

SELECT AVG(MyWord) AS AvgWords
FROM qryUnionWords;

If you're ready to normalize this table, please let us know and we'll help you
develop a query that will fix the table. Keep in mind that any queries, forms,
or reports that rely on the present structure will need to be changed to
accommodate the normalized structure. This is why it's important to design a
database correctly in the first place and to fix problems very early on so that
as little rework as possible is required.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
<te****@hotmail.comwrote in message
news:11**********************@t46g2000cwa.googlegr oups.com...

>I would like to know if it is possible to do a pattern match 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:

Here is what I am using:

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(jave_myWord) +
avg(terenv_myWord) +
avg(baewerr_myWord))/11) as avgData
from TableOne