# Thread: Avg without Total... (2000 SR-1)

1. ## 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???

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

Didn't work.
and I need to add three months together and divide for an average.

3. ## 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. ## 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
•