averaging across more than one field

[Mimi]

Hello!

I'd like to calculate the average of three or more fields using the AVG
function. Ideally the AVG function should be able to figure out what number
it needs to divide by, right? In other words I don't want to have to say
(A+B+C)/3, otherwise that is not using the function--that is using a specific
formula.

Is there an AVG function that takes into consideration the null cells and
adjusts its divisor according to the number of values available?

Any help would be greatly appreciated.

Thanks,
Mimi

 

[Duane Hookom]

Avg() is designed to be used by normalized table structures where the values
to average are across records, not across fields.

Averaging across fields suggests and un-normalized table. If you can't
normalize your table, you can either create a union query to normalize or
kludge together an expression like you have suggested.

 

[Mimi]

To Duane and Jamie:

Thanks for responding. It's a relief to know that I can give up that
particular quest and move to the next step (as I had spent an undue amount of
time trying to make it work). In the social sciences we mostly use SPSS and
SAS to analyze data and the convention is to have rows represent cases
(subjects, patients, people) and columns, the variables we are interested in,
such as age, sex, IQ, etc. And we most definitely can get an average across
columns by executing a function or a formula.

Maybe one day, ACCESS will have some pull down option that will allow one to
perform operations on an "un-normalized" table.

Thanks again,
Mimi

 

[KARL DEWEY]

have rows represent cases (subjects, patients, people) and columns, the
variables we are interested in, such as age, sex, IQ, etc. And we most
definitely can get an average across columns by executing a function or a
formula.

You say your data looks like this --
Smith, Jonn 55 M 186
Jones, Bill 44 M 210

Tell me how do you average the fields for age, sex, IQ for one subjects,
patients, people that is in one row?

 

[Mimi]

Karl,

In the example provided, we take the mean of age or IQ for all the subjects
in the group because that is what we are interested in--characterizing the
group. In this case the function looks pretty obvious, something like
AVG(age). But when we take a questionnaire that has say 20 questions, our
tables are designed so that each column represents an item (variable) on the
questionnaire and the rows represent the people. When we need to take the
mean of all, or a subset of the items, we average across columns. An example
might be if you have a questionnaire that taps depression. Well, say items
1,5,8, and 10 (when averaged together) tap severe depression--we would need
the average of those items to get our "severe" depression score. The scored
item becomes another column variable.

Now, we can continue doing things this way in SPSS or SAS or other related
program, but what I was trying to accomplish this time was to setup the
questionnaire in a user-friendly format in ACCESS and have the patient answer
the questionnaire on the computer, and when the patient is finished, the
score would be computed behind the scenes.

I hope that answers your question.

Thanks for the interest,
Mimi

 

[Amy Blankenship]

Karl,

In the example provided, we take the mean of age or IQ for all the
subjects
in the group because that is what we are interested in--characterizing the
group. In this case the function looks pretty obvious, something like
AVG(age). But when we take a questionnaire that has say 20 questions, our
tables are designed so that each column represents an item (variable) on
the
questionnaire and the rows represent the people. When we need to take the
mean of all, or a subset of the items, we average across columns.

This has nothing to do with the example you stated of having different
properties of *one* person. This is multiple *different* questions. If you
choose poor table structure, it's kind of your problem. If you want a
spreadsheet, you should be using Excel, not Access.

An example
might be if you have a questionnaire that taps depression. Well, say
items
1,5,8, and 10 (when averaged together) tap severe depression--we would
need
the average of those items to get our "severe" depression score. The
scored
item becomes another column variable.

In a good table design, each question would have a type, which would tell
you how to query and average the different records and average the ones for
each type.

Now, we can continue doing things this way in SPSS or SAS or other related
program, but what I was trying to accomplish this time was to setup the
questionnaire in a user-friendly format in ACCESS and have the patient
answer
the questionnaire on the computer, and when the patient is finished, the
score would be computed behind the scenes.

It is very possible to have a normalized table structure and a user-friendly
interface. That is down to your skill and creativity, not the tool's. One
thing Access does not do well that you're going to need is to be able to
pull an empty recordset of the different questions to enable the user to
fill them out. The secret to that is to create a left join of the questions
with the user's responses to them as the data source to the form. In other
words, write a query that pulls all of the questions, but shows null
responses to them if the question has not been answered.

I hope that answers your question.

A good table structure for something like what you want would be this

QuestionTypes
==========
QuestionTypeID
QuestionTypeDesc

Question
========
QuestionID
QuestionStem

Answers
========
AnswerID
QuestionID
AnswerText
AnswerScore

Users
======
UserID
FirstName
LastName
DOB
IQ
etc.

UserAnswers
==========
UserID
AnswerID

Then you can easily query UserAnswer joined to Question, Answers and
QuestionType to get the average AnswerScore for a given QuestionType.

HTH;

Amy

 

[Duane Hookom]

There is a working sample survey "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Mimi]

Thank you Amy and Duane for your feedback.

I have designed several successful ACCESS-based automated surveys without a
problem. I use forms for the user-friendly interface and export the data to
SPSS to perform data analysis.

Duane,
I look forward to taking a peek at your sample survey.

Thanks,
Mimi