Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
4charity 4charity is offline
external usenet poster
 
Posts: 18
Default Multiple Records from Access Query

I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Multiple Records from Access Query

Hi 4charity,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how, check
out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
4charity 4charity is offline
external usenet poster
 
Posts: 18
Default Multiple Records from Access Query

Thanks, macropod. Your tutorial is awesome, and I'm sure I am not alone in
thanking you for the time and work you have put into it and making it
available to all. It is great background knowledge, even though it is stated
that you can't mix letter and directory merges in the same document. Here is
my work-around. If I missed something, or you have a better way to do this,
that would be great.

Note - all of my records have identical data in the fields that I do not
want to list in the directory part of the letter (i.e. Name,
ClaimNumber, etc.)

I created a directory using the regular mailmerge wizard, and attached my
data source. Wrote the entire letter, using the fields that are identical in
all records, including the closing fields. Then I put my table at the end,
with the field of the records that I want to merge from multiple records. I
used the NextRecord field at the beginning of each row, as in my previous
example. I copied this field from a mailing labels template, and pasted it
in, and it worked:

Dear «Name»,

Claim Number «ClaimNumber»

We have reviewed the invoices listed at the end of this letter, and are you
sending you a reimbursement check for checkamount:


Thank you for your business.
«ExaminerName»
Please contact us at: «ExaminerPhoneNumber»

Invoice Number Invoice Amount
«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»


So the table was at the end of the letter, with the merged records, and all
of the fields with the same info were in the body of the letter. *However*,
once this was all working well, realized that you could simply move the table
with the merged records into the body of the letter, once the merge had taken
place. So the template is not that way, but once you do the merge, the data
stays, and you can simply cut and paste the table to where you want it. We
are not mass producing these letters, so it is a fine workaround for us. I
realize that if you were doing this in quantity, it may not be a good
solution. I tried writing a macro to automatically move the table, but Word
doesn't seem to like it.

Thanks again, for your help with this.

"macropod" wrote:

Hi 4charity,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how, check
out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.



  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Multiple Records from Access Query

Hi 4charity,

As I understand it, you're using a standard letter merge with «Next Record» fields to simulate a Direcory merge's ability to output
multiple records on the same page. That will work OK provided you've got exactly the right number of «Next Record» fields for the
client concerned - too many or too few and everything will get out of synch. So, unless all client have the same number of records,
you'd have to do the merge one client at a time, adjusting the number of «Next Record» fields for each client.

The Directory merge fields used in the tutorial avoid all this and, provided everything is set up correctly, allows you to have text
before & after the table.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
Thanks, macropod. Your tutorial is awesome, and I'm sure I am not alone in
thanking you for the time and work you have put into it and making it
available to all. It is great background knowledge, even though it is stated
that you can't mix letter and directory merges in the same document. Here is
my work-around. If I missed something, or you have a better way to do this,
that would be great.

Note - all of my records have identical data in the fields that I do not
want to list in the directory part of the letter (i.e. Name,
ClaimNumber, etc.)

I created a directory using the regular mailmerge wizard, and attached my
data source. Wrote the entire letter, using the fields that are identical in
all records, including the closing fields. Then I put my table at the end,
with the field of the records that I want to merge from multiple records. I
used the NextRecord field at the beginning of each row, as in my previous
example. I copied this field from a mailing labels template, and pasted it
in, and it worked:

Dear «Name»,

Claim Number «ClaimNumber»

We have reviewed the invoices listed at the end of this letter, and are you
sending you a reimbursement check for checkamount:


Thank you for your business.
«ExaminerName»
Please contact us at: «ExaminerPhoneNumber»

Invoice Number Invoice Amount
«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»


So the table was at the end of the letter, with the merged records, and all
of the fields with the same info were in the body of the letter. *However*,
once this was all working well, realized that you could simply move the table
with the merged records into the body of the letter, once the merge had taken
place. So the template is not that way, but once you do the merge, the data
stays, and you can simply cut and paste the table to where you want it. We
are not mass producing these letters, so it is a fine workaround for us. I
realize that if you were doing this in quantity, it may not be a good
solution. I tried writing a macro to automatically move the table, but Word
doesn't seem to like it.

Thanks again, for your help with this.

"macropod" wrote:

Hi 4charity,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how,
check
out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
4charity 4charity is offline
external usenet poster
 
Posts: 18
Default Multiple Records from Access Query

Thanks for the reply.
Actually, I'm not sure which type of merge I am really doing. I select
"Directory", so there is only a one-page document produced. Yes, you are
correct about the number of invoice records.... it is within a small range
for each client, so I've added the maximum number of rows (say, 8), in a
table, and if all are not used, the end-user simply deletes the empty rows.

I *would* love to do this the 'correct' way, utilizing your method, as I am
not a fan of fudging things. I understand that I can put text before and
after the merge fields, and doing it with the NextRecord table I can put
actual fields (that contain the same data for all invoices) both before and
after the table. I haven't found a way to put these fields after the multiple
invoice records using the mergefield method outlined in the tutorial. Am I
missing something, or is this a limitation of the method?

Thanks again.

"macropod" wrote:

Hi 4charity,

As I understand it, you're using a standard letter merge with «Next Record» fields to simulate a Direcory merge's ability to output
multiple records on the same page. That will work OK provided you've got exactly the right number of «Next Record» fields for the
client concerned - too many or too few and everything will get out of synch. So, unless all client have the same number of records,
you'd have to do the merge one client at a time, adjusting the number of «Next Record» fields for each client.

The Directory merge fields used in the tutorial avoid all this and, provided everything is set up correctly, allows you to have text
before & after the table.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
Thanks, macropod. Your tutorial is awesome, and I'm sure I am not alone in
thanking you for the time and work you have put into it and making it
available to all. It is great background knowledge, even though it is stated
that you can't mix letter and directory merges in the same document. Here is
my work-around. If I missed something, or you have a better way to do this,
that would be great.

Note - all of my records have identical data in the fields that I do not
want to list in the directory part of the letter (i.e. Name,
ClaimNumber, etc.)

I created a directory using the regular mailmerge wizard, and attached my
data source. Wrote the entire letter, using the fields that are identical in
all records, including the closing fields. Then I put my table at the end,
with the field of the records that I want to merge from multiple records. I
used the NextRecord field at the beginning of each row, as in my previous
example. I copied this field from a mailing labels template, and pasted it
in, and it worked:

Dear «Name»,

Claim Number «ClaimNumber»

We have reviewed the invoices listed at the end of this letter, and are you
sending you a reimbursement check for checkamount:


Thank you for your business.
«ExaminerName»
Please contact us at: «ExaminerPhoneNumber»

Invoice Number Invoice Amount
«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»


So the table was at the end of the letter, with the merged records, and all
of the fields with the same info were in the body of the letter. *However*,
once this was all working well, realized that you could simply move the table
with the merged records into the body of the letter, once the merge had taken
place. So the template is not that way, but once you do the merge, the data
stays, and you can simply cut and paste the table to where you want it. We
are not mass producing these letters, so it is a fine workaround for us. I
realize that if you were doing this in quantity, it may not be a good
solution. I tried writing a macro to automatically move the table, but Word
doesn't seem to like it.

Thanks again, for your help with this.

"macropod" wrote:

Hi 4charity,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how,
check
out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.






  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Multiple Records from Access Query

Hi 4charity,

All the necessary logic for assembling multiple records per client is built into the field coding in the tutorial. Provided you
perform a Directory merge (and tell Word to sort the data if they're not already sorted), the field coding will take care of the
rest. There is no need for any «Next Record» fields with this approach. For what I understand you're after, I suggest studying the
examples under the tutorial's 'Inserting Text After the Repeated Data' heading.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
Thanks for the reply.
Actually, I'm not sure which type of merge I am really doing. I select
"Directory", so there is only a one-page document produced. Yes, you are
correct about the number of invoice records.... it is within a small range
for each client, so I've added the maximum number of rows (say, 8), in a
table, and if all are not used, the end-user simply deletes the empty rows.

I *would* love to do this the 'correct' way, utilizing your method, as I am
not a fan of fudging things. I understand that I can put text before and
after the merge fields, and doing it with the NextRecord table I can put
actual fields (that contain the same data for all invoices) both before and
after the table. I haven't found a way to put these fields after the multiple
invoice records using the mergefield method outlined in the tutorial. Am I
missing something, or is this a limitation of the method?

Thanks again.

"macropod" wrote:

Hi 4charity,

As I understand it, you're using a standard letter merge with «Next Record» fields to simulate a Direcory merge's ability to
output
multiple records on the same page. That will work OK provided you've got exactly the right number of «Next Record» fields for the
client concerned - too many or too few and everything will get out of synch. So, unless all client have the same number of
records,
you'd have to do the merge one client at a time, adjusting the number of «Next Record» fields for each client.

The Directory merge fields used in the tutorial avoid all this and, provided everything is set up correctly, allows you to have
text
before & after the table.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
Thanks, macropod. Your tutorial is awesome, and I'm sure I am not alone in
thanking you for the time and work you have put into it and making it
available to all. It is great background knowledge, even though it is stated
that you can't mix letter and directory merges in the same document. Here is
my work-around. If I missed something, or you have a better way to do this,
that would be great.

Note - all of my records have identical data in the fields that I do not
want to list in the directory part of the letter (i.e. Name,
ClaimNumber, etc.)

I created a directory using the regular mailmerge wizard, and attached my
data source. Wrote the entire letter, using the fields that are identical in
all records, including the closing fields. Then I put my table at the end,
with the field of the records that I want to merge from multiple records. I
used the NextRecord field at the beginning of each row, as in my previous
example. I copied this field from a mailing labels template, and pasted it
in, and it worked:

Dear «Name»,

Claim Number «ClaimNumber»

We have reviewed the invoices listed at the end of this letter, and are you
sending you a reimbursement check for checkamount:


Thank you for your business.
«ExaminerName»
Please contact us at: «ExaminerPhoneNumber»

Invoice Number Invoice Amount
«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»
«Next Record»«InvoiceNumber» «AmountInvoiced»


So the table was at the end of the letter, with the merged records, and all
of the fields with the same info were in the body of the letter. *However*,
once this was all working well, realized that you could simply move the table
with the merged records into the body of the letter, once the merge had taken
place. So the template is not that way, but once you do the merge, the data
stays, and you can simply cut and paste the table to where you want it. We
are not mass producing these letters, so it is a fine workaround for us. I
realize that if you were doing this in quantity, it may not be a good
solution. I tried writing a macro to automatically move the table, but Word
doesn't seem to like it.

Thanks again, for your help with this.

"macropod" wrote:

Hi 4charity,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word version). To see how,
check
out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"4charity" wrote in message ...
I am creating a MailMerge letter using data from a query in Access. I would
like the letter to show the [InvoiceNumber] and I[nvoiceAmount] from several
records in Access for the same [Vendor], all in one letter. I am getting
multiple letters, with one record in each.

This is what I've tried, but it's not working. Any ideas?

(IF(MERGESEQ)="1" "(MERGEFIELD Vendor)" "")
(SET Vendor1(MERGEFIELD Vendor))
(IF (Vendor2)(Vendor1)"
(MERGEFIELD Vendor)

(MERGEFIELD InvoiceNumber)(MERGEFIELD AmountInvoiced)" "(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)""(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced)"(MERGEFIELD
InvoiceNumber)(MERGEFIELD AmountInvoiced))(SET Vendor2(MERGEFIELD Vendor))

Note - the () are really the squiggly brackets.

As an aside: All of the vendors are the same for the records - so I think
there is some extra code in there, but I modified this from somewhere else,
and haven't figured out what to get rid of, but I don't think that matters,
since the [Vendor] info is the same for all records.





Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Records Missing from Access Query pas926 Mailmerge 1 March 27th 08 12:29 AM
mailmerge MS Access multiple records EiEiohh Mailmerge 2 February 20th 08 12:38 PM
How do I include multiple records per label on Access or Word? Rani Mailmerge 1 February 15th 06 04:32 AM
mailmerge from Access parameter query only merges odd records helios Mailmerge 5 December 30th 05 12:24 PM
Word 2003 receives no records from Access 2003 query Tor Michal Kinn Mailmerge 1 April 7th 05 11:26 AM


All times are GMT +1. The time now is 03:09 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"