AVG and IIF function in query or report.

[Kaylen]

I have a table with the following columns: [Date], [1], [2],[3],[4], etc. The
data in the columns [1],[2], [3], [4] and so forth... are either the value
of "4", or "N/A". I want to calculate the average of of the columns 1,2,3,4
in the table when [Date]=#" BUT if all the data in columns 1,2,3,4 are all
"N/A", I want the result of the calculation to show "N/A", not error. I need
help in coming up with a formula for this.

Then I need a grand total formula to show the SUM of the results of the all
averages from the average formulas above. Any help with this problem is very
much appreciated.

 

[Rob Ward]

I'm sorry, you won't like this answer but the reason you're having this
problem is because your data isn't correctly normalised in the first place.
By definition your fields [1], [2] etc contain similar data items (otherwise
there would be no point in you trying to average them) so they should be in
the same column, i.e. your structure should be [Date], [ItemNo],[DataValue],
whereupon the queries that you want would be a breeze.

You can get to this more optimal structure by using a union query to convert
your non-normalised data to normalised, even if only for the purposes of your
reporting query. Assuming your data is held in a table called
'tblNonNormalisedData' It would be something like this:

SELECT tblNonNormalised.ItemDate, "1" AS ItemNumber, tblNonNormalised.[1] as
DataValue
FROM tblNonNormalised

union all

SELECT tblNonNormalised.ItemDate, "2" AS ItemNumber, tblNonNormalised.[2] as
DataValue
FROM tblNonNormalised

union all

SELECT tblNonNormalised.ItemDate, "3" AS ItemNumber, tblNonNormalised.[3] as
DataValue
FROM tblNonNormalised

;

Try it - you'll find your query problems disappear as if by magic!

 

[John Spencer]

This is a tough one to solve.

Your data structure is wrong, you have field names that don't conform to
good naming conventions, and your field types are text on which you want to
perform arithmetic.

If you don't have a LOT of fields, your best bet is a normalizing union
query to fix the data and then an aggregate (totals) query to get the values

SELECT [Date] as TheDate, IIF([1] ="N/A",Null,Val([1])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([2] ="N/A",Null,Val([2])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([3] ="N/A",Null, Val([3])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([4] ="N/A",Val,[4])) as TheValue
FROM [YourTable]

Now you can use that saved union query as the source for a totals query
SELECT TheDate, Avg(TheValue) as TheAverage
FROM [TheSavedUnionQuery]
WHERE TheDate = #2008-01-30#
GROUP BY TheDate

If the field TheAverage is Null (blank) then all the values for the date
were either "N/A" or Null. Of course, you could end up with errors if the
values in the fields [1] were something other than N/A, null, or a number.
If that is the case you might change the expression to
IIF(IsNumeric([1])=False,Null,,Val([1]))
Or
IIF([1] Like "*[!0-9]*",Null,Val([1]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kaylen]

Thank you both for you effort in trying to help me on this. I guess I should
describe what I want to do. I am trying to make a template with a list of
questions. The answers are scores ranging from 0-4, with an option of N/A
(not applicable). The users of the template will be entering the [Date] that
they answer the questions and the answers to the questions (0-4, or N/A).
Here is when the calculations are involved: I want to average the answers at
a given date (Date=#) for questions 1-5 and sum the answers from questions
6-8, and then average the answers from questions 9-12, etc. But if, for
example the answers for questions 9-12 are all "N/A", then I want the average
or sum formulas to show "N/A" instead of error. At the end I want to have a
grand total score, the sum of all the averages and sums above. Because the
questions are so long to be columns headings, that's why I was thinking of
having them as numbers and relabeling them later in the form. Can you give me
any idea how I should create this template database so others can use to
answer questions and run report to see to the scores of a given date? Many
thanks..

This is a tough one to solve.

Your data structure is wrong, you have field names that don't conform to
good naming conventions, and your field types are text on which you want to
perform arithmetic.

If you don't have a LOT of fields, your best bet is a normalizing union
query to fix the data and then an aggregate (totals) query to get the values

SELECT [Date] as TheDate, IIF([1] ="N/A",Null,Val([1])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([2] ="N/A",Null,Val([2])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([3] ="N/A",Null, Val([3])) as TheValue
FROM [YourTable]
UNION ALL
SELECT [Date], IIF([4] ="N/A",Val,[4])) as TheValue
FROM [YourTable]

Now you can use that saved union query as the source for a totals query
SELECT TheDate, Avg(TheValue) as TheAverage
FROM [TheSavedUnionQuery]
WHERE TheDate = #2008-01-30#
GROUP BY TheDate

If the field TheAverage is Null (blank) then all the values for the date
were either "N/A" or Null. Of course, you could end up with errors if the
values in the fields [1] were something other than N/A, null, or a number.
If that is the case you might change the expression to
IIF(IsNumeric([1])=False,Null,,Val([1]))
Or
IIF([1] Like "*[!0-9]*",Null,Val([1]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table with the following columns: [Date], [1], [2],[3],[4], etc.
The
data in the columns [1],[2], [3], [4] and so forth... are either the
value
of "4", or "N/A". I want to calculate the average of of the columns
1,2,3,4
in the table when [Date]=#" BUT if all the data in columns 1,2,3,4 are all
"N/A", I want the result of the calculation to show "N/A", not error. I
need
help in coming up with a formula for this.

Then I need a grand total formula to show the SUM of the results of the
all
averages from the average formulas above. Any help with this problem is
very
much appreciated.