Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculate Average across 4 fields

Discussion in 'Information Technology' started by hughess7, Jul 28, 2006.

  1. hughess7

    hughess7
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    India Reading Calculated Banis Breaking News Mar 6, 2013
    Sikhi About The Average Sikh Sikh Sikhi Sikhism Feb 18, 2016
    India Corruption rate in India is double of global average Breaking News Jul 9, 2013
    Average Jatt Sikh Male Height 5' 7" Health & Nutrition Aug 7, 2010
    Tech turns average Joes into mini-Spielbergs (USATODAY.com) Interfaith Dialogues Apr 1, 2005

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Hi Sue,

    I'd create

    "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    > 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
     
  4. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.


    "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    > 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
     
  5. hughess7

    hughess7
    Expand Collapse
    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


    "ChrisM" wrote:

    > 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.
    >
    >
    > "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    > news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    > > 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

    >
    >
    >
     
  6. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.

    "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    news:3FA20D7E-FF30-4018-9580-649DAB983DB8@microsoft.com...
    > 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
    >
    >
    > "ChrisM" wrote:
    >
    >> 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.
    >>
    >>
    >> "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    >> news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    >> > 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

    >>
    >>
    >>
     
  7. hughess7

    hughess7
    Expand Collapse
    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


    "ChrisM" wrote:

    > 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.
    >
    > "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    > news:3FA20D7E-FF30-4018-9580-649DAB983DB8@microsoft.com...
    > > 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
    > >
    > >
    > > "ChrisM" wrote:
    > >
    > >> 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.
    > >>
    > >>
    > >> "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    > >> news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    > >> > 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
    > >>
    > >>
    > >>

    >
    >
    >
     
  8. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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
    *



    hughess7 wrote:
    > 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
    >
    >
    > "ChrisM" wrote:
    >
    >> 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.
    >>
    >> "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    >> news:3FA20D7E-FF30-4018-9580-649DAB983DB8@microsoft.com...
    >>> 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
    >>>
    >>>
    >>> "ChrisM" wrote:
    >>>
    >>>> 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.
    >>>>
    >>>>
    >>>> "hughess7" <hughess7@discussions.microsoft.com> wrote in message
    >>>> news:B0DB9511-F56C-4366-9C74-A3FD662EEAA5@microsoft.com...
    >>>>> 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
    >>>>
    >>>>

    >>
    >>
     

Share This Page