How to use "if" statements in a query or function?

[PeterKappes]

Hey,

I have three columns of numeric data that I need to get an average from.
However, some of the numeric data actually isn't "values" but place holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown value).
Obviously, if I simply average the three columns then I get incorrect
averages wherever there was no data collected or unknown values. I'm not
sure if I can put if statements into my query or how or if I need to call a
function (which I also don't know how to do). How would I be able to get
this information averaged such that 88888 and 99999 "values" are not used in
the calculation, but are entered into the results if that was the only
"value" recorded for the three columns. There is never a combo of 88888 and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[John Spencer]

AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null, [YourField]) as
TheAverage

 

[PeterKappes]

Hi John,

I don't think this will work, since AVG is an aggregate function. This may
be reinventing the wheel, but this is what I came up with. Do you see a way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1, tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or [comu_1]=88888=True,0,1) AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3])) AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null, [YourField]) as
TheAverage

Hey,

I have three columns of numeric data that I need to get an average from.
However, some of the numeric data actually isn't "values" but place
holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown
value).
Obviously, if I simply average the three columns then I get incorrect
averages wherever there was no data collected or unknown values. I'm not
sure if I can put if statements into my query or how or if I need to call
a
function (which I also don't know how to do). How would I be able to get
this information averaged such that 88888 and 99999 "values" are not used
in
the calculation, but are entered into the results if that was the only
"value" recorded for the three columns. There is never a combo of 88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[John Spencer]

Sorry, I misunderstood your structure. I was assuming a normalized table
structure.

Are the values of Comu_1 to 3 ever Null (blank)? Your code assumes that
they are never blank

I would use a custom VBA function to do this. It will make life a lot
easier if you continue with this structure and someone decides you need to
add Comu_4 and Comu_5 to the table. If you can you might consider
redesigning the table structure and moving Comu to a related table.

I've attached a function that you can paste into a module (make sure the
module does NOT have the same name as the function).

You can then call this function to get the average.

fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3)) as
ComuAvg

SELECT tblColonyCounts.ColonyCountID
, tblColonyCounts.Colony,
tblColonyCounts.Area
, tblColonyCounts.COMU_1
, tblColonyCounts.COMU_2
, tblColonyCounts.COMU_3
, fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3)) as
ComuAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

'============ Code Follows =================
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

Hi John,

I don't think this will work, since AVG is an aggregate function. This
may
be reinventing the wheel, but this is what I came up with. Do you see a
way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1, tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or [comu_1]=88888=True,0,1) AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3]))
AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null, [YourField])
as
TheAverage

Hey,

I have three columns of numeric data that I need to get an average
from.
However, some of the numeric data actually isn't "values" but place
holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown
value).
Obviously, if I simply average the three columns then I get incorrect
averages wherever there was no data collected or unknown values. I'm
not
sure if I can put if statements into my query or how or if I need to
call
a
function (which I also don't know how to do). How would I be able to
get
this information averaged such that 88888 and 99999 "values" are not
used
in
the calculation, but are entered into the results if that was the only
"value" recorded for the three columns. There is never a combo of
88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[PeterKappes]

Hi John,

Thank you for the code for the function. This is beyond my
knowledge/ability, but I figured there was a more efficient method for
getting what I needed. Correct me if I'm wrong, but using this function will
allow me to add more comu fields and as long as I insert the correct IIf
statements for the new comu fields (e.g. comu_4, comu_5 etc) then call the
function in my sql statement, they will be treated the same. Therefore, I
eliminate the step where I create new value4, value 5 fields. Is that
correct?

Briefly looking over the VBA code, I'm not sure I understand how the
function is working, but calling it in the query seems to work correctly.
However, how can I set the properties of ComuAvg so that there are no
decimals? FYI, the COMU fields are never null. We had some issues where
people were getting sloppy with data entry and it was a bear to proof raw
data sheets and the database to determine if the data wasn't entered or
wasn't collected.

Cheers,
Peter

Sorry, I misunderstood your structure. I was assuming a normalized table
structure.

Are the values of Comu_1 to 3 ever Null (blank)? Your code assumes that
they are never blank

I would use a custom VBA function to do this. It will make life a lot
easier if you continue with this structure and someone decides you need to
add Comu_4 and Comu_5 to the table. If you can you might consider
redesigning the table structure and moving Comu to a related table.

I've attached a function that you can paste into a module (make sure the
module does NOT have the same name as the function).

You can then call this function to get the average.

fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3)) as
ComuAvg

SELECT tblColonyCounts.ColonyCountID
, tblColonyCounts.Colony,
tblColonyCounts.Area
, tblColonyCounts.COMU_1
, tblColonyCounts.COMU_2
, tblColonyCounts.COMU_3
, fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3)) as
ComuAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

'============ Code Follows =================
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

Hi John,

I don't think this will work, since AVG is an aggregate function. This
may
be reinventing the wheel, but this is what I came up with. Do you see a
way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1, tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or [comu_1]=88888=True,0,1) AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3]))
AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null, [YourField])
as
TheAverage


Hey,

I have three columns of numeric data that I need to get an average
from.
However, some of the numeric data actually isn't "values" but place
holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown
value).
Obviously, if I simply average the three columns then I get incorrect
averages wherever there was no data collected or unknown values. I'm
not
sure if I can put if statements into my query or how or if I need to
call
a
function (which I also don't know how to do). How would I be able to
get
this information averaged such that 88888 and 99999 "values" are not
used
in
the calculation, but are entered into the results if that was the only
"value" recorded for the three columns. There is never a combo of
88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[John Spencer]

Yes, if you stick with your structure, you can simply add another value into
the function.

The best way to do that is after the average is calculated.
CLng(Nz(FGetMeanAverage(...))) that will round to the nearest whole integer

If you just want the integer portion (1.98 will become 1)
Int(fGetMeanAverage(...))

I'm guessing that the ParamArray Values() is causing you the most confusion.
ParamArray accepts multiple arguments and sticks the values into an array
(Values in this case).

Then the code steps through the array and sums all the values that are
numbers and also counts the number of times it finds a number. Simple
arithmetic after that. Final step is to return the result.

Hi John,

Thank you for the code for the function. This is beyond my
knowledge/ability, but I figured there was a more efficient method for
getting what I needed. Correct me if I'm wrong, but using this function
will
allow me to add more comu fields and as long as I insert the correct IIf
statements for the new comu fields (e.g. comu_4, comu_5 etc) then call the
function in my sql statement, they will be treated the same. Therefore, I
eliminate the step where I create new value4, value 5 fields. Is that
correct?

Briefly looking over the VBA code, I'm not sure I understand how the
function is working, but calling it in the query seems to work correctly.
However, how can I set the properties of ComuAvg so that there are no
decimals? FYI, the COMU fields are never null. We had some issues where
people were getting sloppy with data entry and it was a bear to proof raw
data sheets and the database to determine if the data wasn't entered or
wasn't collected.

Cheers,
Peter

Sorry, I misunderstood your structure. I was assuming a normalized table
structure.

Are the values of Comu_1 to 3 ever Null (blank)? Your code assumes that
they are never blank

I would use a custom VBA function to do this. It will make life a lot
easier if you continue with this structure and someone decides you need
to
add Comu_4 and Comu_5 to the table. If you can you might consider
redesigning the table structure and moving Comu to a related table.

I've attached a function that you can paste into a module (make sure the
module does NOT have the same name as the function).

You can then call this function to get the average.

fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3))
as
ComuAvg

SELECT tblColonyCounts.ColonyCountID
, tblColonyCounts.Colony,
tblColonyCounts.Area
, tblColonyCounts.COMU_1
, tblColonyCounts.COMU_2
, tblColonyCounts.COMU_3
, fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3))
as
ComuAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

'============ Code Follows =================
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

Hi John,

I don't think this will work, since AVG is an aggregate function. This
may
be reinventing the wheel, but this is what I came up with. Do you see
a
way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1, tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or [comu_1]=88888=True,0,1)
AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3]))
AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;



:



AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null,
[YourField])
as
TheAverage


Hey,

I have three columns of numeric data that I need to get an average
from.
However, some of the numeric data actually isn't "values" but place
holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown
value).
Obviously, if I simply average the three columns then I get
incorrect
averages wherever there was no data collected or unknown values.
I'm
not
sure if I can put if statements into my query or how or if I need to
call
a
function (which I also don't know how to do). How would I be able
to
get
this information averaged such that 88888 and 99999 "values" are not
used
in
the calculation, but are entered into the results if that was the
only
"value" recorded for the three columns. There is never a combo of
88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[PeterKappes]

Hi John,

Thanks a lot! This works great! I really need to get up to speed with
writing VB code. I'm sure that I'm spending many hours re-inventing the
wheel.

Cheers,
Peter

Yes, if you stick with your structure, you can simply add another value into
the function.

The best way to do that is after the average is calculated.
CLng(Nz(FGetMeanAverage(...))) that will round to the nearest whole integer

If you just want the integer portion (1.98 will become 1)
Int(fGetMeanAverage(...))

I'm guessing that the ParamArray Values() is causing you the most confusion.
ParamArray accepts multiple arguments and sticks the values into an array
(Values in this case).

Then the code steps through the array and sums all the values that are
numbers and also counts the number of times it finds a number. Simple
arithmetic after that. Final step is to return the result.

Hi John,

Thank you for the code for the function. This is beyond my
knowledge/ability, but I figured there was a more efficient method for
getting what I needed. Correct me if I'm wrong, but using this function
will
allow me to add more comu fields and as long as I insert the correct IIf
statements for the new comu fields (e.g. comu_4, comu_5 etc) then call the
function in my sql statement, they will be treated the same. Therefore, I
eliminate the step where I create new value4, value 5 fields. Is that
correct?

Briefly looking over the VBA code, I'm not sure I understand how the
function is working, but calling it in the query seems to work correctly.
However, how can I set the properties of ComuAvg so that there are no
decimals? FYI, the COMU fields are never null. We had some issues where
people were getting sloppy with data entry and it was a bear to proof raw
data sheets and the database to determine if the data wasn't entered or
wasn't collected.

Cheers,
Peter

Sorry, I misunderstood your structure. I was assuming a normalized table
structure.

Are the values of Comu_1 to 3 ever Null (blank)? Your code assumes that
they are never blank

I would use a custom VBA function to do this. It will make life a lot
easier if you continue with this structure and someone decides you need
to
add Comu_4 and Comu_5 to the table. If you can you might consider
redesigning the table structure and moving Comu to a related table.

I've attached a function that you can paste into a module (make sure the
module does NOT have the same name as the function).

You can then call this function to get the average.

fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3))
as
ComuAvg

SELECT tblColonyCounts.ColonyCountID
, tblColonyCounts.Colony,
tblColonyCounts.Area
, tblColonyCounts.COMU_1
, tblColonyCounts.COMU_2
, tblColonyCounts.COMU_3
, fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or Comu_3=99999,Null,Comu_3))
as
ComuAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

'============ Code Follows =================
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function


Hi John,

I don't think this will work, since AVG is an aggregate function. This
may
be reinventing the wheel, but this is what I came up with. Do you see
a
way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1, tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or [comu_1]=88888=True,0,1)
AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3]))
AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;



:



AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null,
[YourField])
as
TheAverage


Hey,

I have three columns of numeric data that I need to get an average
from.
However, some of the numeric data actually isn't "values" but place
holders
for null data. (e.g. 88888 = no data collected and 99999 = unknown
value).
Obviously, if I simply average the three columns then I get
incorrect
averages wherever there was no data collected or unknown values.
I'm
not
sure if I can put if statements into my query or how or if I need to
call
a
function (which I also don't know how to do). How would I be able
to
get
this information averaged such that 88888 and 99999 "values" are not
used
in
the calculation, but are entered into the results if that was the
only
"value" recorded for the three columns. There is never a combo of
88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[John Spencer]

Unsolicited advice.

Take some time to study designing relational databases. The need for
fGetMeanAverage function is because your design is not normalized. As I
hope I said elsewhere, your problem is a design problem.

If you had another table to hold the Comu Values, you could have used a
simple aggregate query.

Table: TblComu
Fields:
-- ColonyCountID (duplicates the id in ColonyCount
-- Comu (holds the value in Comu1, 2, 3, etc
-- Other fields that describe Comu and not Colony

Then it would be a simple matter to link this new table to the ColonyCount
table and build a totals (aggregate) query that did averages, sums, counts,
etc.

Hi John,

Thanks a lot! This works great! I really need to get up to speed with
writing VB code. I'm sure that I'm spending many hours re-inventing the
wheel.

Cheers,
Peter

Yes, if you stick with your structure, you can simply add another value
into
the function.

The best way to do that is after the average is calculated.
CLng(Nz(FGetMeanAverage(...))) that will round to the nearest whole
integer

If you just want the integer portion (1.98 will become 1)
Int(fGetMeanAverage(...))

I'm guessing that the ParamArray Values() is causing you the most
confusion.
ParamArray accepts multiple arguments and sticks the values into an array
(Values in this case).

Then the code steps through the array and sums all the values that are
numbers and also counts the number of times it finds a number. Simple
arithmetic after that. Final step is to return the result.

Hi John,

Thank you for the code for the function. This is beyond my
knowledge/ability, but I figured there was a more efficient method for
getting what I needed. Correct me if I'm wrong, but using this
function
will
allow me to add more comu fields and as long as I insert the correct
IIf
statements for the new comu fields (e.g. comu_4, comu_5 etc) then call
the
function in my sql statement, they will be treated the same.
Therefore, I
eliminate the step where I create new value4, value 5 fields. Is that
correct?

Briefly looking over the VBA code, I'm not sure I understand how the
function is working, but calling it in the query seems to work
correctly.
However, how can I set the properties of ComuAvg so that there are no
decimals? FYI, the COMU fields are never null. We had some issues
where
people were getting sloppy with data entry and it was a bear to proof
raw
data sheets and the database to determine if the data wasn't entered or
wasn't collected.

Cheers,
Peter

:

Sorry, I misunderstood your structure. I was assuming a normalized
table
structure.

Are the values of Comu_1 to 3 ever Null (blank)? Your code assumes
that
they are never blank

I would use a custom VBA function to do this. It will make life a lot
easier if you continue with this structure and someone decides you
need
to
add Comu_4 and Comu_5 to the table. If you can you might consider
redesigning the table structure and moving Comu to a related table.

I've attached a function that you can paste into a module (make sure
the
module does NOT have the same name as the function).

You can then call this function to get the average.

fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or
Comu_3=99999,Null,Comu_3))
as
ComuAvg

SELECT tblColonyCounts.ColonyCountID
, tblColonyCounts.Colony,
tblColonyCounts.Area
, tblColonyCounts.COMU_1
, tblColonyCounts.COMU_2
, tblColonyCounts.COMU_3
, fGetMeanAverage(IIF(Comu_1=88888 or
Comu_1=99999,Null,Comu_1),IIF(Comu_2=88888 or
Comu_2=99999,Null,Comu_2),IIF(Comu_3=88888 or
Comu_3=99999,Null,Comu_3))
as
ComuAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;

'============ Code Follows =================
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed
to
it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0)
' returns 3 (21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric
values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function


Hi John,

I don't think this will work, since AVG is an aggregate function.
This
may
be reinventing the wheel, but this is what I came up with. Do you
see
a
way
I could streamline the SQL statement?

SELECT tblColonyCounts.ColonyCountID, tblColonyCounts.Colony,
tblColonyCounts.Area, tblColonyCounts.COMU_1,
tblColonyCounts.COMU_2,
tblColonyCounts.COMU_3, IIf([comu_1]=99999 Or
[comu_1]=88888=True,0,1)
AS
Value1, IIf([comu_2]=99999 Or [comu_2]=88888=True,0,1) AS Value2,
IIf([comu_3]=99999 Or [comu_3]=88888=True,0,1) AS Value3,
IIf(([value1]+[value2]+[value3])=0=True,[comu_1],(([comu_1]*[value1])+([comu_2]*[value2])+([comu_3]*[value3]))/([value1]+[value2]+[value3]))
AS COMUAvg
FROM tblColonyCounts
ORDER BY tblColonyCounts.ColonyCountID;



:



AVG(IIF([Your Field] = 88888 or [Yourfield] = 99999, Null,
[YourField])
as
TheAverage


message
Hey,

I have three columns of numeric data that I need to get an
average
from.
However, some of the numeric data actually isn't "values" but
place
holders
for null data. (e.g. 88888 = no data collected and 99999 =
unknown
value).
Obviously, if I simply average the three columns then I get
incorrect
averages wherever there was no data collected or unknown values.
I'm
not
sure if I can put if statements into my query or how or if I need
to
call
a
function (which I also don't know how to do). How would I be
able
to
get
this information averaged such that 88888 and 99999 "values" are
not
used
in
the calculation, but are entered into the results if that was the
only
"value" recorded for the three columns. There is never a combo
of
88888
and
99999. Here is an example:

7, 6, 88888 = 6.5
99999, 99999, 99999 = 99999
5, 88888, 88888 = 5
10, 20, 30 = 20

Thanks in advance,
Peter

 

[PeterKappes]

Hi John,

Thanks for the advice. I've been learning Access on the fly and inherited a
database that was essentially a glorified Excel file. Unfortunately, the
PI's are stuggling to get out of the mindset of "all data in one table" and
since they can't afford a full-time DB Admin. and they aren't up to speed on
the basics of a relational DB they are wary of letting me break down the
sturcture of how the data is stored to make normalized tables and a more
efficient database. I think they fear that if it gets beyond their
understanding they could find themselves with a DB they don't understand and
can't operate. I've been working on them, but its been a slow process and
I've spent lots of time doing similar troubleshooting. I appreciate your
unsolicited advice b/c the more I work with this database, the more I realize
I need to know and I find that I end up incorporating just about everything I
get from this forum. Thanks again,
Peter