I need to merge records from Access 2003 to Word 2003. The records are in a query that associates a one to many relationship.
- - - - - - - - - -
the database tracks comments as provided in a letter, with the responses to those comments.
the fields for tblCommentResponse are:
<LetterID> <CommentID> <Comment> <Response>
fields for tblContact are (simplified):
<LetterID> <Name> <Address>
fields for qrelContactCommentResponse
<LetterID> <Name> <Address> <CommentID> <Comment> <Response>
sample query result:
1 | John Doe | 100 Street | 1 | I like this program. | Your welcome
1 | John Doe | 100 Street | 2 | The program needs a new menu item. | Your comment will be considered in the next upgrade.
2 | Jane Smith | 200 Main Street | 1 | The app needs work with the interface. | Your comment is under consideration.
etc.
Each letter is given a LetterID (the one), and each comment on that letter is given a CommentID (the many). Each letter can have from 1 to infinite number of comments (highest so far is 104 comments)
I need a way to mail merge a "response letter" that contains the writers' name/address, then a listing of all the comments and the responses for that writer.
could look like this:
<date>
<Name>
<Address>
Subject: our response to your comments
<CommentID> <Comment>
<Response>
<CommentID> <Comment>
<Response> etc <NextRecord>
- - page break - -
<Date>
<Name>
<Address>
etc.
- - - - - - - - -
My initial mail merge was creating a new letter for each comment for each person, so the person with a hundred comments will have 100 response letters.
I'm at a loss as to whether i need to find out how to create a "flat file" data source from a one-to-many query for Word (with an unknown number of comment/response columns); or create an "if-then" merge statement (VBA?) to determine whether to: print the next comment/response or create the next letter. I've tried using a subreport in Access, but it exports to Word messily, creating a lot of reformatting work.
Mike
- - - - - - - - - -
the database tracks comments as provided in a letter, with the responses to those comments.
the fields for tblCommentResponse are:
<LetterID> <CommentID> <Comment> <Response>
fields for tblContact are (simplified):
<LetterID> <Name> <Address>
fields for qrelContactCommentResponse
<LetterID> <Name> <Address> <CommentID> <Comment> <Response>
sample query result:
1 | John Doe | 100 Street | 1 | I like this program. | Your welcome
1 | John Doe | 100 Street | 2 | The program needs a new menu item. | Your comment will be considered in the next upgrade.
2 | Jane Smith | 200 Main Street | 1 | The app needs work with the interface. | Your comment is under consideration.
etc.
Each letter is given a LetterID (the one), and each comment on that letter is given a CommentID (the many). Each letter can have from 1 to infinite number of comments (highest so far is 104 comments)
I need a way to mail merge a "response letter" that contains the writers' name/address, then a listing of all the comments and the responses for that writer.
could look like this:
<date>
<Name>
<Address>
Subject: our response to your comments
<CommentID> <Comment>
<Response>
<CommentID> <Comment>
<Response> etc <NextRecord>
- - page break - -
<Date>
<Name>
<Address>
etc.
- - - - - - - - -
My initial mail merge was creating a new letter for each comment for each person, so the person with a hundred comments will have 100 response letters.
I'm at a loss as to whether i need to find out how to create a "flat file" data source from a one-to-many query for Word (with an unknown number of comment/response columns); or create an "if-then" merge statement (VBA?) to determine whether to: print the next comment/response or create the next letter. I've tried using a subreport in Access, but it exports to Word messily, creating a lot of reformatting work.
Mike
