Calculate Average across 4 fields

[Guest]

Hi all

I have a query which calculates total costs in 4 different time frames
producing 4 columns (JanDec04, JulJun05, JanDec05,JulJun06). I want to work
out an average so I can compare the most recent cost to see if it is above or
below the average. Some of the columns contain nulls. Please can someone help
with this?

Thanks in advance for any help.
Sue

 

[ChrisM]

Hi Sue,

I'd create

 

[ChrisM]

Hi Sue,

Assuming the value is is not to be included in the average if it is null.

I'd create a few extra columns in a query which would make life a little
easier:

I've changed your date fields to D1, D2, D3, D4 for clarity.

NonNullDateCount:
IIf(IsNull([D1]),0,1)+IIf(IsNull([D2]),0,1)+IIf(IsNull([D3]),0,1)+IIf(IsNull([D4]),0,1)

D1NullFixed: nz([D1],0)
....
D4NullFixed: IIf(IsNull(D4]),0,D4)

Then your average is simple a case of : (D1NullFixed+ ... + D4NullFixed) /
NonNullDateCount

Hope this helps.

Cheers,

Chris.

 

[Guest]

Hi Chris

Great thanks. Think I've got working. I had an error your way but I
calculate total by

IIf(IsNull([Jan-Dec04]),0,[Jan-Dec04])+IIf(IsNull([Jul-Jun05]),0,[jul-jun05])+IIf(IsNull([Jul-Jun06]),0,[jul-jun06])+IIf(IsNull([Jan-Dec05]),0,[Jan-Dec05])

Then divided this total by NoOfFields using your IsNull count expression and
this seemed to work.

Thanks
Sue

Hi Sue,

Assuming the value is is not to be included in the average if it is null.

I'd create a few extra columns in a query which would make life a little
easier:

I've changed your date fields to D1, D2, D3, D4 for clarity.

NonNullDateCount:
IIf(IsNull([D1]),0,1)+IIf(IsNull([D2]),0,1)+IIf(IsNull([D3]),0,1)+IIf(IsNull([D4]),0,1)

D1NullFixed: nz([D1],0)
....
D4NullFixed: IIf(IsNull(D4]),0,D4)

Then your average is simple a case of : (D1NullFixed+ ... + D4NullFixed) /
NonNullDateCount

Hope this helps.

Cheers,

Chris.

Hi all

I have a query which calculates total costs in 4 different time frames
producing 4 columns (JanDec04, JulJun05, JanDec05,JulJun06). I want to
work
out an average so I can compare the most recent cost to see if it is above
or
below the average. Some of the columns contain nulls. Please can someone
help
with this?

Thanks in advance for any help.
Sue

 

[ChrisM]

Good! I think that's what I meant. The only other thing I was trying (and
probably failing) to show was as an alternative to:
iif(IsNull([Jan-Dec04]),0,[Jan-Dec04])
You could write:
nz([Jan-Dec04],0)

Works the same, just a little bit neater.

Maybe you don't have the 'nz' function? Maybe it depends on the version of
Access you are using (I have Access2000).

Cheers,

Chris.

Hi Chris

Great thanks. Think I've got working. I had an error your way but I
calculate total by

IIf(IsNull([Jan-Dec04]),0,[Jan-Dec04])+IIf(IsNull([Jul-Jun05]),0,[jul-jun05])+IIf(IsNull([Jul-Jun06]),0,[jul-jun06])+IIf(IsNull([Jan-Dec05]),0,[Jan-Dec05])

Then divided this total by NoOfFields using your IsNull count expression
and
this seemed to work.

Thanks
Sue

Hi Sue,

Assuming the value is is not to be included in the average if it is null.

I'd create a few extra columns in a query which would make life a little
easier:

I've changed your date fields to D1, D2, D3, D4 for clarity.

NonNullDateCount:
IIf(IsNull([D1]),0,1)+IIf(IsNull([D2]),0,1)+IIf(IsNull([D3]),0,1)+IIf(IsNull([D4]),0,1)

D1NullFixed: nz([D1],0)
....
D4NullFixed: IIf(IsNull(D4]),0,D4)

Then your average is simple a case of : (D1NullFixed+ ... + D4NullFixed)
/
NonNullDateCount

Hope this helps.

Cheers,

Chris.

Hi all

I have a query which calculates total costs in 4 different time frames
producing 4 columns (JanDec04, JulJun05, JanDec05,JulJun06). I want to
work
out an average so I can compare the most recent cost to see if it is
above
or
below the average. Some of the columns contain nulls. Please can
someone
help
with this?

Thanks in advance for any help.
Sue

 

[Guest]

I am using 2003 and I do have the NZ function but when i tried it this way I
got #error on all totals except where there was only one field populated
(with a value). It didn't seem to add fields together although the syntax
looked correct. I got the isnull method working instead so stuck to this.

Thanks again

Sue

Good! I think that's what I meant. The only other thing I was trying (and
probably failing) to show was as an alternative to:
iif(IsNull([Jan-Dec04]),0,[Jan-Dec04])
You could write:
nz([Jan-Dec04],0)

Works the same, just a little bit neater.

Maybe you don't have the 'nz' function? Maybe it depends on the version of
Access you are using (I have Access2000).

Cheers,

Chris.

Hi Chris

Great thanks. Think I've got working. I had an error your way but I
calculate total by

IIf(IsNull([Jan-Dec04]),0,[Jan-Dec04])+IIf(IsNull([Jul-Jun05]),0,[jul-jun05])+IIf(IsNull([Jul-Jun06]),0,[jul-jun06])+IIf(IsNull([Jan-Dec05]),0,[Jan-Dec05])

Then divided this total by NoOfFields using your IsNull count expression
and
this seemed to work.

Thanks
Sue

Hi Sue,

Assuming the value is is not to be included in the average if it is null.

I'd create a few extra columns in a query which would make life a little
easier:

I've changed your date fields to D1, D2, D3, D4 for clarity.

NonNullDateCount:
IIf(IsNull([D1]),0,1)+IIf(IsNull([D2]),0,1)+IIf(IsNull([D3]),0,1)+IIf(IsNull([D4]),0,1)

D1NullFixed: nz([D1],0)
....
D4NullFixed: IIf(IsNull(D4]),0,D4)

Then your average is simple a case of : (D1NullFixed+ ... + D4NullFixed)
/
NonNullDateCount

Hope this helps.

Cheers,

Chris.


Hi all

I have a query which calculates total costs in 4 different time frames
producing 4 columns (JanDec04, JulJun05, JanDec05,JulJun06). I want to
work
out an average so I can compare the most recent cost to see if it is
above
or
below the average. Some of the columns contain nulls. Please can
someone
help
with this?

Thanks in advance for any help.
Sue

 

[strive4peace]

Hi Sue,

here is the difference:

You can use NZ when you have a record with a null value

If you don't even have a record, you need to use IsNull ...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I am using 2003 and I do have the NZ function but when i tried it this way I
got #error on all totals except where there was only one field populated
(with a value). It didn't seem to add fields together although the syntax
looked correct. I got the isnull method working instead so stuck to this.

Thanks again

Sue

Good! I think that's what I meant. The only other thing I was trying (and
probably failing) to show was as an alternative to:
iif(IsNull([Jan-Dec04]),0,[Jan-Dec04])
You could write:
nz([Jan-Dec04],0)

Works the same, just a little bit neater.

Maybe you don't have the 'nz' function? Maybe it depends on the version of
Access you are using (I have Access2000).

Cheers,

Chris.

Hi Chris

Great thanks. Think I've got working. I had an error your way but I
calculate total by

IIf(IsNull([Jan-Dec04]),0,[Jan-Dec04])+IIf(IsNull([Jul-Jun05]),0,[jul-jun05])+IIf(IsNull([Jul-Jun06]),0,[jul-jun06])+IIf(IsNull([Jan-Dec05]),0,[Jan-Dec05])

Then divided this total by NoOfFields using your IsNull count expression
and
this seemed to work.

Thanks
Sue


:

Hi Sue,

Assuming the value is is not to be included in the average if it is null.

I'd create a few extra columns in a query which would make life a little
easier:

I've changed your date fields to D1, D2, D3, D4 for clarity.

NonNullDateCount:
IIf(IsNull([D1]),0,1)+IIf(IsNull([D2]),0,1)+IIf(IsNull([D3]),0,1)+IIf(IsNull([D4]),0,1)

D1NullFixed: nz([D1],0)
....
D4NullFixed: IIf(IsNull(D4]),0,D4)

Then your average is simple a case of : (D1NullFixed+ ... + D4NullFixed)
/
NonNullDateCount

Hope this helps.

Cheers,

Chris.


Hi all

I have a query which calculates total costs in 4 different time frames
producing 4 columns (JanDec04, JulJun05, JanDec05,JulJun06). I want to
work
out an average so I can compare the most recent cost to see if it is
above
or
below the average. Some of the columns contain nulls. Please can
someone
help
with this?

Thanks in advance for any help.
Sue