Merging results of an access query into an existing doc

D

DKING

Hello

I am trying to get the results of a Access Query to mail merge into a
exisiting form.

My query brings data based on the following fields
WORK_ORDER_NUMBER
SITE_ADDRESS
DEVELOPER
SCOPE_OF_WORKS
DETAILS
PLOT

Basically, a work order will be associated with a plot and may b
associated with 1 or more plots. Therefore, work order 6798 may b
associated with plot 1,2 and 4. Thus when the query runs, work orde
6798 will show up 3 times

I have managed to get the folloing to work in a new blank document )i
is based on a the formula from KB181730)

{If {MergeSeq} = "1" "{Mergefield developer} {mergefiel
site_address}¶
{mergefield work_order_number}{mergefield scope_of works}{mergfiel
details}¶
" ""}{Set WO1 {Mergefield worl_order_number}}¶
{If {WO1} <> {WO2}"¶
"{Mergefield developer} {mergefield site_address}¶
{mergefield work_order_number}{mergefield scope_of works}{mergfiel
details}¶
{Mergefield plot}"¶
{Mergefield plot}"}{Set WO2 {Mergefield work_order_number}}¶

This merges into a new document with the data like this

My Homes Estate 1, Anytown
45678 LV Breech Joint near plot 76 - to liven plot 81 only
(95-185wf)

81

My Homes Estate 1, Anytown
45679 LV Breech Joint o/s 76 to liven plot 76 only -
76

My Homes Estate 1, Anytown
45680 Double Service Breech 37/82
37
82

My Homes Estate 2, Anytown , Warrington
50372 Double Service Breech Plots 36/37
37
36


What I am having difficulty with is trying to get the data to fit on
two paged document ie, where to place the merge fields and "if" an
"set" statements accordingly. The information re plots needs to go on
page two of the document.

I can e-mail a version of the document if needs be!

Any assistance or guidance would be greatly appreciated!!
 
D

Doug Robbins - Word MVP

I would do it all in Access

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
C

Cindy M -WordMVP-

Hi Dking,
What I am having difficulty with is trying to get the data to fit on a
two paged document ie, where to place the merge fields and "if" and
"set" statements accordingly. The information re plots needs to go on a
page two of the document.
Click to expand...
You definitely need one page break in front of the mergefield (but within
the quotes), here, so that list for the first record begins on a new
page.

{If {MergeSeq} = "1" "{Mergefield developer}; here, I think:

{If {WO1} <> {WO2}"¶
"[Page break]{Mergefield developer}

And then again in the same place in the "if result" part that displays
this first line of text for all the following records
I am trying to get the results of a Access Query to mail merge into an
exisiting form.

My query brings data based on the following fields
WORK_ORDER_NUMBER
SITE_ADDRESS
DEVELOPER
SCOPE_OF_WORKS
DETAILS
PLOT

Basically, a work order will be associated with a plot and may be
associated with 1 or more plots. Therefore, work order 6798 may be
associated with plot 1,2 and 4. Thus when the query runs, work order
6798 will show up 3 times

I have managed to get the folloing to work in a new blank document )it
is based on a the formula from KB181730)

{If {MergeSeq} = "1" "{Mergefield developer} {mergefield
site_address}¶
{mergefield work_order_number}{mergefield scope_of works}{mergfield
details}¶
" ""}{Set WO1 {Mergefield worl_order_number}}¶
{If {WO1} <> {WO2}"¶
"{Mergefield developer} {mergefield site_address}¶
{mergefield work_order_number}{mergefield scope_of works}{mergfield
details}¶
{Mergefield plot}"¶
{Mergefield plot}"}{Set WO2 {Mergefield work_order_number}}¶

This merges into a new document with the data like this

My Homes Estate 1, Anytown
45678 LV Breech Joint near plot 76 - to liven plot 81 only -
(95-185wf)

81

My Homes Estate 1, Anytown
45679 LV Breech Joint o/s 76 to liven plot 76 only -
76

My Homes Estate 1, Anytown
45680 Double Service Breech 37/82
37
82

My Homes Estate 2, Anytown , Warrington
50372 Double Service Breech Plots 36/37
37
36


What I am having difficulty with is trying to get the data to fit on a
two paged document ie, where to place the merge fields and "if" and
"set" statements accordingly. The information re plots needs to go on a
page two of the document.

I can e-mail a version of the document if needs be!
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
D

DKING

Cindy

Thanks for the feedback. I have managed to get the data to forma
correctly, but only into a new blank document.

My problem is that I have a pre-designed word document with tables
that I want to populate with the data from the query.

I am using the document type of catalog, what would I have to do to ge
the same sort of results using "form letter"?

Cheers

De
 
C

Cindy M -WordMVP-

Hi Dking,

I must admit to feeling a bit lost and confused...

We started out with the If..Then and Set field thing to bring
one-to-many lists into a mail merge. The question was, where you should
insert page breaks. You tried my suggestion, and that worked?

And now we're on to something different? Or is the problem that you've
got the "many" coming through, and now you need the "one" side?

It's hard to describe, but basically you need all the text that comes
before the "many" at the very beginning. Then the "many" stuff. And then
you need the top part of the document again in the "Else clause" in the
IF that checks whether the ID you're checking (work_order_number)
changes. (When it doesn't change, you get the next entry for the ID; if
it changes, you want to do the top of the document).

I suggest you keep working with your test file that merges correctly to a
new document. Type in something like START HERE before all that. Then put
it in " " for the Else clause in the IF field, as described above
(probably just after the Page Break we discussed).

Work with that until it looks right.

Then for the material that comes AFTER the "many" stuff: put this in the
"Else" clause just before the Page Break. (Again, test with a bit of
text).

Once you have all that working, try copying/pasting the actual text.

Note: this can get very difficult to maintain. What you might want to try
is putting this Start and Finish text into a separate file, marking it
with bookmarks, and use IncludeText fields to bring it across.
Thanks for the feedback. I have managed to get the data to format
correctly, but only into a new blank document.

My problem is that I have a pre-designed word document with tables,
that I want to populate with the data from the query.

I am using the document type of catalog, what would I have to do to get
the same sort of results using "form letter"?
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :)
 
Top