Using AVG in a report

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • bobh

    #1

    Using AVG in a report

    Hi All,
    In an AccessXP report in a Group Footer I have the following in a
    textbox and I don't think its working.
    Question; Is AccessXP smart enough to know to first sum up all the
    values of
    [DaysToReferral] and count how many before it calculates the average
    for only those records
    that met the criteria? or is it just capturing the last value for
    that grouping and calculating it.

    =Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
    frmMainMenu!bxP riorYrDte And [CatType]="DRef",[DaysToReferral],0))

    thanks
    bobh.

  • Marshall Barton

    #2
    Re: Using AVG in a report

    bobh wrote:
    >Hi All,
    >In an AccessXP report in a Group Footer I have the following in a
    >textbox and I don't think its working.
    >Question; Is AccessXP smart enough to know to first sum up all the
    >values of
    >[DaysToReferral] and count how many before it calculates the average
    >for only those records
    >that met the criteria? or is it just capturing the last value for
    >that grouping and calculating it.
    >
    >=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
    >frmMainMenu!bx PriorYrDte And [CatType]="DRef",[DaysToReferral],0))

    Is your problem because you are using 0 for records that
    don't meet your criteria? Normally, you should use Null
    instead of 0.

    --
    Marsh

    Comment

    • bobh

      #3
      Re: Using AVG in a report

      It has made no difference whether I use '0' or 'null' or ""

      and so the question still remains........ .... in this case is Access
      doing the right calc or is it just calc on the last record read into
      the grouping.
      bobh.

      On Jan 30, 12:37 pm, Marshall Barton <marshbar...@wo wway.comwrote:
      bobhwrote:
      Hi All,
      In an AccessXP report in a Group Footer I have the following in a
      textbox and I don't think its working.
      Question; Is AccessXP smart enough to know to first sum up all the
      values of
      [DaysToReferral] and count how many before it calculates the average
      for only those records
      that met the criteria? or is it just capturing the last value for
      that grouping and calculating it.
      >
      =Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
      frmMainMenu!bxP riorYrDte And [CatType]="DRef",[DaysToReferral],0))
      >
      Is your problem because you are using 0 for records that
      don't meet your criteria? Normally, you should use Null
      instead of 0.
      >
      --
      Marsh

      Comment

      • Marshall Barton

        #4
        Re: Using AVG in a report

        If you want it to count only the non-Null values, then you
        need to use Null. If you want to count all the records,
        then use 0. Do not use quotes around either the 0 the Null.

        Using Null worked for my tests. Maybe the [DaysToReferral]
        field does not contain the values you think it does??
        Either that or your report has something going on that I am
        not aware of.
        --
        Marsh


        bobh wrote:
        >It has made no difference whether I use '0' or 'null' or ""
        >
        >and so the question still remains........ .... in this case is Access
        >doing the right calc or is it just calc on the last record read into
        >the grouping.
        >bobh.
        >
        >On Jan 30, 12:37 pm, Marshall Barton <marshbar...@wo wway.comwrote:
        >bobhwrote:
        >Hi All,
        >In an AccessXP report in a Group Footer I have the following in a
        >textbox and I don't think its working.
        >Question; Is AccessXP smart enough to know to first sum up all the
        >values of
        >[DaysToReferral] and count how many before it calculates the average
        >for only those records
        >that met the criteria? or is it just capturing the last value for
        >that grouping and calculating it.
        >>
        >=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
        >frmMainMenu!bx PriorYrDte And [CatType]="DRef",[DaysToReferral],0))
        >>
        >Is your problem because you are using 0 for records that
        >don't meet your criteria? Normally, you should use Null
        >instead of 0.

        Comment

        Working...