A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Merging results of an access query into an existing doc



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2004, 03:15 PM
DKING
external usenet poster
 
Posts: n/a
Default Merging results of an access query into an existing doc


Hello

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!

Any assistance or guidance would be greatly appreciated!!!


------------------------------------------------
~~ Message posted from http://www.WordForums.com/
  #2  
Old May 1st, 2004, 10:39 AM
Doug Robbins - Word MVP
external usenet poster
 
Posts: n/a
Default Merging results of an access query into an existing doc

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
"DKING" wrote in message
news

Hello

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!

Any assistance or guidance would be greatly appreciated!!!


------------------------------------------------
~~ Message posted from http://www.WordForums.com/

  #3  
Old May 2nd, 2004, 10:40 AM
Cindy M -WordMVP-
external usenet poster
 
Posts: n/a
Default Merging results of an access query into an existing doc

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.

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!


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 :-)

  #4  
Old May 4th, 2004, 02:51 PM
DKING
external usenet poster
 
Posts: n/a
Default Merging results of an access query into an existing doc


Cindy

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"?

Cheers

Des


------------------------------------------------
~~ Message posted from http://www.WordForums.com/
  #5  
Old May 5th, 2004, 08:40 AM
Cindy M -WordMVP-
external usenet poster
 
Posts: n/a
Default Merging results of an access query into an existing doc

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"?


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 :-)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.