By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

mail merge with multiple detail tables

P: n/a
I have 3 tables (office97)
tblQuote
quoteNbr

tblDetails ( quote : 1 <-> M: quoteDetails)
quoteNbr
detailLine
product
value

tblComments ( quote : 1 <-> M: quoteComments)
quoteNbr
commentLine
comment

I can use vba to create a details text file for a quote, that is used
as a word mail merge data source

the word document shows
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

and I can create a comments text file to create a word document like
quote : 123
line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

but how do I create a data source and a word template to combine the
two, as in
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

do I add a field to my text file "D"etail or "C"omment and use
IF..THEN.. ELSE ?
does my text file need five lines for this quote (2 detail, + 3
comment) ?

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 23 Nov 2005 11:08:57 -0800, le*********@natpro.com wrote:
I have 3 tables (office97)
tblQuote
quoteNbr

tblDetails ( quote : 1 <-> M: quoteDetails)
quoteNbr
detailLine
product
value

tblComments ( quote : 1 <-> M: quoteComments)
quoteNbr
commentLine
comment

I can use vba to create a details text file for a quote, that is used
as a word mail merge data source

the word document shows
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

and I can create a comments text file to create a word document like
quote : 123
line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

but how do I create a data source and a word template to combine the
two, as in
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

do I add a field to my text file "D"etail or "C"omment and use
IF..THEN.. ELSE ?
does my text file need five lines for this quote (2 detail, + 3
comment) ?


It is probable that a Word newsgroup will have better advice than I
can offer.

You can't really do this in one document in Word, as Word has no
method that I'm aware of to "move to a next detail record" that you
can condition. So, unless your quotes have a specific number of
detail lines, you can't have Word know when to start switching to
comment lines.

What you can do is run a mail merge on one portion of your document
(either the comments or the other) and then run a mail merge on the
other portion, using the already created output as a subdocument for
your second mail merge.

As intimated above, you can do this in one Word document if you always
have a specific number of detail lines.

I guess another way to do it would be to have Access print a line with
the appropriate number of detail lines in it and have selectively use
a subdocument that has the right number of detail lines based on that
input. For example, say that the text file has:

3
DetailLine1
DetailLine2
DetailLine3
6
CommentLine1
CommentLine2
CommentLine3
CommentLine4
CommentLine5
CommentLine6

You could set up a Word document that read the first value (3) and
then selectively inserted a word document that had 3 detail lines to
be merged in. Then it would read the second value (6) and then insert
a word document that had 6 comment lines to be merged in.

This would be a hecka lot of files and indicates to me that there has
to be a better way.

What about using Access to control Word so that you can just insert
the appropriate information directly into your Word document and not
mess with any of this?

mike

mike
Nov 24 '05 #2

P: n/a
thanks for your ideas... it has me thinking more about this
as to having Access create a Word document, I thought of that, but
doesn't that get messy when trying to use bookmarks for text
positioning ?

I wonder if word document A can be a mail merge of the 'comments'
and word document B be can be a mail merge of the 'product detail' + an
imbedded object containing word document A ? that's a good idea you
gave me ... I'll try it out

Mike Preston wrote:
On 23 Nov 2005 11:08:57 -0800, le*********@natpro.com wrote:
I have 3 tables (office97)
tblQuote
quoteNbr

tblDetails ( quote : 1 <-> M: quoteDetails)
quoteNbr
detailLine
product
value

tblComments ( quote : 1 <-> M: quoteComments)
quoteNbr
commentLine
comment

I can use vba to create a details text file for a quote, that is used
as a word mail merge data source

the word document shows
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

and I can create a comments text file to create a word document like
quote : 123
line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

but how do I create a data source and a word template to combine the
two, as in
quote : 123
line product value
1 abc 100.00
2 xyz 200.00

line comment
1 it is nice today
2 it will rain tomorrow
3 it will be nice on Saturday

do I add a field to my text file "D"etail or "C"omment and use
IF..THEN.. ELSE ?
does my text file need five lines for this quote (2 detail, + 3
comment) ?


It is probable that a Word newsgroup will have better advice than I
can offer.

You can't really do this in one document in Word, as Word has no
method that I'm aware of to "move to a next detail record" that you
can condition. So, unless your quotes have a specific number of
detail lines, you can't have Word know when to start switching to
comment lines.

What you can do is run a mail merge on one portion of your document
(either the comments or the other) and then run a mail merge on the
other portion, using the already created output as a subdocument for
your second mail merge.

As intimated above, you can do this in one Word document if you always
have a specific number of detail lines.

I guess another way to do it would be to have Access print a line with
the appropriate number of detail lines in it and have selectively use
a subdocument that has the right number of detail lines based on that
input. For example, say that the text file has:

3
DetailLine1
DetailLine2
DetailLine3
6
CommentLine1
CommentLine2
CommentLine3
CommentLine4
CommentLine5
CommentLine6

You could set up a Word document that read the first value (3) and
then selectively inserted a word document that had 3 detail lines to
be merged in. Then it would read the second value (6) and then insert
a word document that had 6 comment lines to be merged in.

This would be a hecka lot of files and indicates to me that there has
to be a better way.

What about using Access to control Word so that you can just insert
the appropriate information directly into your Word document and not
mess with any of this?

mike

mike


Nov 24 '05 #3

P: n/a
On 24 Nov 2005 14:11:14 -0800, le*********@natpro.com wrote:
thanks for your ideas... it has me thinking more about this
as to having Access create a Word document, I thought of that, but
doesn't that get messy when trying to use bookmarks for text
positioning ?
I think you will find it the easiest approach. Try it. I think you
will like it.
I wonder if word document A can be a mail merge of the 'comments'
and word document B be can be a mail merge of the 'product detail' + an
imbedded object containing word document A ? that's a good idea you
gave me ... I'll try it out
I'd try the above, first.

mike

Mike Preston wrote:
On 23 Nov 2005 11:08:57 -0800, le*********@natpro.com wrote:
>I have 3 tables (office97)
> tblQuote
> quoteNbr
>
> tblDetails ( quote : 1 <-> M: quoteDetails)
> quoteNbr
> detailLine
> product
> value
>
> tblComments ( quote : 1 <-> M: quoteComments)
> quoteNbr
> commentLine
> comment
>
>I can use vba to create a details text file for a quote, that is used
>as a word mail merge data source
>
>the word document shows
>quote : 123
>line product value
> 1 abc 100.00
> 2 xyz 200.00
>
>and I can create a comments text file to create a word document like
>quote : 123
>line comment
> 1 it is nice today
> 2 it will rain tomorrow
> 3 it will be nice on Saturday
>
>but how do I create a data source and a word template to combine the
>two, as in
>quote : 123
>line product value
> 1 abc 100.00
> 2 xyz 200.00
>
>line comment
> 1 it is nice today
> 2 it will rain tomorrow
> 3 it will be nice on Saturday
>
>do I add a field to my text file "D"etail or "C"omment and use
>IF..THEN.. ELSE ?
>does my text file need five lines for this quote (2 detail, + 3
>comment) ?


It is probable that a Word newsgroup will have better advice than I
can offer.

You can't really do this in one document in Word, as Word has no
method that I'm aware of to "move to a next detail record" that you
can condition. So, unless your quotes have a specific number of
detail lines, you can't have Word know when to start switching to
comment lines.

What you can do is run a mail merge on one portion of your document
(either the comments or the other) and then run a mail merge on the
other portion, using the already created output as a subdocument for
your second mail merge.

As intimated above, you can do this in one Word document if you always
have a specific number of detail lines.

I guess another way to do it would be to have Access print a line with
the appropriate number of detail lines in it and have selectively use
a subdocument that has the right number of detail lines based on that
input. For example, say that the text file has:

3
DetailLine1
DetailLine2
DetailLine3
6
CommentLine1
CommentLine2
CommentLine3
CommentLine4
CommentLine5
CommentLine6

You could set up a Word document that read the first value (3) and
then selectively inserted a word document that had 3 detail lines to
be merged in. Then it would read the second value (6) and then insert
a word document that had 6 comment lines to be merged in.

This would be a hecka lot of files and indicates to me that there has
to be a better way.

What about using Access to control Word so that you can just insert
the appropriate information directly into your Word document and not
mess with any of this?

mike

mike


Nov 25 '05 #4

P: n/a
This seems like it should be done on a report itself. Why use Word?
You can use conditioning and so on.

Nov 25 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.