Math In Merge Field(s)

[Kenny G]

Hi,

I have a question using math in merge fields. First time user in this area.

We use Access 2002 and Word 2002

Beside the regular merged data I have a field inside the Word document that
is not exactly a merged field. This field would be the results of a series
of questions in the Access query that the Word document derives its data from.

There are a series of questions (total of 16) that provide the answer for
this field.
The number of yes responses need to be counted and divided by the 16 fields
and a percentage be assigned to this field based on the number of yes
responses.

ie 12(yes)/16(totalfields) = 75%

Based on what I have seen on Graham Mayor's web site I gather something like
the following needs to be done:

{=({XXXXXXXXXX}*100\#"0%"}

Hope its not as clear as mud. I need to try to fill in the XXXXX portion of
the above.
How do I go about entering the math to get my answer?

Thanks for your assistance.

Kenny G

 

[Peter Jamieson]

If all the answers are actually in your data source, it will probably be
easier to do some or all of this calculation in an Access query which also
returns all the columns you need, and use that query as the data source for
the merge.

For the examples, let's assume there are only 3 questions.
If the data is in your data source, you will need to use { MERGEFIELD }
fields to get it, e.g. if the fields are called response1, response2, and
response3 you will need to use

{ MERGEFIELD response1 }, { MERGEFIELD response2 } and { MERGEFIELD
response3 }so on. If the fields are bookmark fields set using ASK fields
during the merge, you need to use REF fields, e.g. { REF response1 } or just
{ response1 } and so on instead of the MERGEFIELD fields

If the data is already encoded numerically, e.g. with yes encoded as 1 and
no as 0, then you can use

{ =(({ MERGEFIELD response1 }+{ MERGEFIELD response2}+{ MERGEFIELD
response3 })/3)*100 \#0% }

If the data is encoded as "yes" and some other value (e.g. "no", "", etc.)
then it gets more complicated. You can try

{ =(({ IF { MERGEFIELD response1 } = "yes" "1" "0" }
+{ IF { MERGEFIELD response2 } = "yes" "1" "0" }
+{ IF { MERGEFIELD response3 } = "yes" "1" "0" })/3)*100 \#0% }

or

{ =(({ COMPARE { MERGEFIELD response1 } = "yes" }
+{ COMPARE { MERGEFIELD response2 } = "yes" }
+{ COMPARE { MERGEFIELD response3 } = "yes" })/3)*100 \#0% }

However, if the "yes" value could be any of "YES", "yes", "Yes" etc. I would
use something more like

{ =(({ COMPARE { MERGEFIELD response1 \*upper } = "YES" }
+{ COMPARE { MERGEFIELD response2 \*upper } = "YES" }
+{ COMPARE { MERGEFIELD response3 \*upper } = "YES" })/3)*100 \#0% }

Peter Jamieson

 

[Kenny G]

Peter,

I just wanted to take a moment to say thanks for your assistance. I will
bring this work home on the weekend to finish it. I'll post my findings.

Kenny G

 

[Kenny G]

Peter,

Many thanks, it works great.

Kenny G

Peter,

I just wanted to take a moment to say thanks for your assistance. I will
bring this work home on the weekend to finish it. I'll post my findings.

Kenny G