Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avg without Total... (2000 SR-1)

    Greetings... I will try to lay this out simply.

    My table contains gross adds '[GA]' for store locations '[Store Name]' apended monthly '[Month]'. At any time, should I want to list the [GA] by [Month] I successfully do that using this formula in a query:
    Jan: Sum(IIf(Format([month],"mm-yy")="01-02",[GA],0))

    I now need the "Avg GA's per Month", however, because of other fields in the query, I cannot use a 'where' total in the [month] column and simply AVG the [GA]. I thought I would add three iff statments like the one above and divide by three but am having trouble coming up with an acurate result. I know I can create one formula for each month and then average the three but I want it all in one column.
    I tried this without success:
    Q1: (Sum(IIf(Format([month],"mm-yy")="01-02",[GA],0)))+(Sum(IIf(Format([month],"mm-yy")="02-02",[GA],0)))+(Sum(IIf(Format([month],"mm-yy")="03-02",[GA],0)))/3

    Q: How do I format the statement above to end up with a Jan+Feb+Mar average in one formula???
    There is always a way.

  2. #2
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg without Total... (2000 SR-1)

    Didn't work.
    and I need to add three months together and divide for an average.
    There is always a way.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Avg without Total... (2000 SR-1)

    If your query includes the monthly conditional sums as described above to obtain 3-month averages add expressions like these to query:
    <pre>QTR1 AVG: ([JAN]+[FEB]+[MAR])/3
    QTR2 AVG: ([APR]+[MAY]+[JUN])/3</pre>

    Etc etc. For field properties specify "Fixed" and number of decimal places. The "Totals" line in query design should specify "Expression."

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avg without Total... (2000 SR-1)

    This is off the top of my head, I haven't really tried it:

    Try replacing

    Sum(IIf(Format([month],"mm-yy")="01-02",[GA],0))

    by

    <pre>DSum("GA", "tablename", "Format([month],'mm-yy')='01-02'")</pre>

    (replace tablename by the name of your table)

    Note the use of single quotes within the WHERE part of DSum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •