Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Math In Merge Field(s)
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 -- Kenny G |
#2
|
|||
|
|||
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" wrote in message news 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 -- Kenny G |
#3
|
|||
|
|||
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 "Peter Jamieson" wrote: 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" wrote in message news 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 -- Kenny G |
#4
|
|||
|
|||
Peter,
Many thanks, it works great. Kenny G "Kenny G" wrote: 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 "Peter Jamieson" wrote: 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" wrote in message news 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 -- Kenny G |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you create a multi-layered merge where certain merge fields a. | Mailmerge | |||
Empty Mail Merge Fields | Microsoft Word Help | |||
Merge Fields in Header? | Mailmerge | |||
Merge Fields, Headers, Etc. | Mailmerge | |||
Using MAILMERGE fields within HYPERLINK fields for Merge to Email | Mailmerge |