mail merge with multiple detail tables

lesperancer

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

Subscribe Reply

5453

Mike Preston

On 23 Nov 2005 11:08:57 -0800, le*********@nat pro.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

lesperancer

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*********@nat pro.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

Mike Preston

On 24 Nov 2005 14:11:14 -0800, le*********@nat pro.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*********@nat pro.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

Dean

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

Similar topics

12664

Creating a mail merge document using C#.

by: campwes | last post by:

Hey, all. I'm trying to develop a C# app that creates Word 2003 mail merge documents with an Oracle 9i database as the datasource. I used the following as an example of how I can start out: http://support.microsoft.com/default.aspx?scid=kb;EN-US;301659 The problem is that the code provided doesn't allow me to add more users to the mail merge data file. When I try to add an additional user with the following code, I get this error...

.NET Framework

3168

Mail merge to Word from ASP

by: Morris | last post by:

Does anyone know whether it is possible to effect a mail merge to MSWord using VBScript in an asp file? I know it is possible to produce a doc file and force a download using Response.ContentType = "application/msword" Response.AddHeader content-disposition","attachment;filename=myfile.doc" but can I interate through a bunch of records and produce, say, a separate invoice for each one by merging? I know how to iterate through the...

ASP / Active Server Pages

2572

Multiple Names In A Mail Merge Field

by: Traci | last post by:

I need to do a mail merge letter from my database. The letter will be addressed to small companies and in the body of the letter I need to list employees of the company. There will be from 1 to 15 employees. My database is all set up to provide the names and addresses of the companies and the list of employees of each company. I need help on how to do the employee merge field when there will be multiple employees to be listed in this field....

Microsoft Access / VBA

4826

Individual records exported to word mail merge?

by: dd_bdlm | last post by:

Hi I wonder if anyone can help? I have looked through prevous posts and cant find any answers that fit what I need to do. For reference I am using Access 97 and Word 97. I want to be able to have a client record open in a form and click a button and for that particular record to merge with a preformatted word doc. I have created a query that gives me all the fields I need (as they are over multiple tables) and I can successfully combine...

Microsoft Access / VBA

11355

command line mail merge from mysql db

by: cdelarte | last post by:

I would like to be able to mail merge records from multiple mysql tables using a simple template, preferably via a command line script. MSWord mail merge via ODBC will not work for me as it only supports a single table. Are there any utilities or simple scripts avaialble that would accomplish this?

MySQL Database

4649

Access mail merge to word, multiple sources

by: crealesmith | last post by:

Firstly, I have no problem with mail merging to Word, VB code for that works perfectly. On one mail merge I need to merge 15 fields of data that are from 3 seperate records. The 3 records are all in the same table. If I use a control source that is selecting the 3 records, all of the data is shown but spread over 3 pages in the mail merge, but needs to be listed together in one paragraph. Is there anyway of looping through the 3...

Visual Basic .NET

1985

Access / Mail Merge

by: mbigelow | last post by:

I'm trying to create an e-mail statement that pulls data from two tables....one table is simply the Student information (e-mail, name, phone etc) and the other table is a Transaction table that can have multiple rows per student. Using Word mail merge I'm able to send a single e-mail to each entry in the Student table, however in the body of the e-mail I'd like to include the Student's transactions. Students - Transactions is a one to many...

Microsoft Access / VBA

7241

Mail merge to Word

by: giladp1 | last post by:

I found Albert Kallal's great "Super easy Word Merge" code in his site at: http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html Thanks Albert so much for sharing this. I am looking for any comments about the use of the docmd.transfertext method instead of the code Albert used for creating the text file. Also, perhaps some ideas for coding the Subject Line of each email so

Microsoft Access / VBA

11497

Mail Merge VB Access to Word to Print Multiple Records Via Button

by: kirkus84 | last post by:

I am currently trying to do a multiple record mail merge through a query via a command button on a form. The query basically displays customers who have said yes to privacy. The user inputs a date into txtDate on frmDate and the clicks "View Queried Privacy Records" Once they click this it opens the query form frmPrivacy which then has a command button cmdMailmerge to perform the mail merge and print the records that result from that query. (I...

Microsoft Access / VBA

9589

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9423

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10222

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10050

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9999

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8876

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7413

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5448

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3967

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp