T

#### Tom

following data in a table:

Index Answer

===============

1.1 2

1.2 3

1.3 1

2.1 5

2.2 5

Now, based on the table information, I run a grouped query with "Sum" on the

"Answer" field.

IndexParent Value Denominator Average

=============================================

1 6 3 2

2 10 2 5

Here's how I calculated the "Average":

- IndexParent "1" has 3 children (Index 1.1, Index 1.2, Index 1.3)... this

gives me the denominator of "3"

- IndexParent "2" has 2 children (Index 2.1, Index 2.2)... this gives me the

denominator of "2"

- the value for IndexParent_1 is "6" (2 + 3 +1 = 6)... which is then divided

by its denominator... so my average = 2

- the value for IndexParent_2 is "10" (5 + 5 = 10)... which is then divided

by its denominator... so my average = 5

so far so good....

Now, I need to "tweak" my SUM query to exclude a value. Let's say I have an

answer of "9" in the table. For instance:

Index Answer

==============

1.1 2

1.2 9

1.3 1

2.1 5

2.2 5

As a result, here's what I get in my SUM query...

IndexParent Value Denominator Average

=============================================

1 12 3 4

2 10 2 5

Although, the query's results are mathematically correct, I need to find a

way to exclude a record when the Answer = 9.

So, my SUM query should show the following instead:

IndexParent Value Denominator Average

==============================================

1 3 2 1.5

2 10 2 5

As you can see, now I'm adding only the answers of 1.1 & 1.3 (2 + 1 =3). At

the same time, I'm subtracting "1" from the denominator field. So, instead

of dividing 3 by 3, I divide 3 by 2... .which is the correct answer.

My questions:

- if I read a certain value (9) in my answer field, how can I subtract it

from the summary total for each IndexParent?

- how can I also subtract the "number of occurrences" (where Answer = 9)

from the Denominator?

- and finally, if all answers in an IndexParent are equal to 9, I need to

show 0 (rather than divisional error that I would get when dividing 0 by 0).

How do I do that?

Thanks in advance,

Tom