Exporting query with duplicates

  • Thread starter Thread starter Gray Sadler via AccessMonster.com
  • Start date Start date
G

Gray Sadler via AccessMonster.com

I have a query that I'm trying to transfertext from, and then use that text
file as the source for my mail merge in Word. Here's an example of the output
my query gives:
---------------------------------------
ID Name Code
1 ABC Co. 5555
1 ABC Co. 4444
1 ABC Co. 1111
2 XYZ Inc. 4545
2 XYZ Inc. 6565
---------------------------------------
I then want to create a mail merge document in Word that will create forms
for each 'ID' with a 'subform' of sorts on each form that will list all
'Codes' from my query based on the ID.

Example of Mail Merge Word Doc(based from Query above):
-------------Record 1--------------
Name: ABC Co.
ID: 1

Codes: 5555
4444
1111
----------end of record 1----------

--------------Record 2-------------
Name: XYZ Inc.
ID: 2

Codes: 4545
6565
----------end of record 2----------

I can easily do this in Access by creating a Main form with a subform that
kicks out codes based on the ID of the open form, but is something like this
possible in Word? Any help is greatly appreciated!
 
Gray,

This sounds more like aWord question, really... I'm not an expert on
Word, but I don't think it's doable there, except maybe with some VBA
code to read the tet file line by line, populate the "merge fields" and
print. I have enclosed "merge fields" in quotes because this is clearly
not standard Word mail merge functionality.

Alternatively, you could do it all in Access without a single line of
code! Just set up a report on the query, group on ID or name, force each
group to start on a new page and add the fixed text around the report
data controls... full functionality, full formatting capabilities, no
exports / imports, not a single line of code.

HTH,
Nikos
 
Thanks for the reply!

From my understanding, Word is not capable of doing such a thing. I have been
browsing other topics, and I believe I may have found out what I need to do
by using a concatenate function. In theory, such a function would change a
query's output from:

ID Name Code
1 ABC Co. 5555
1 ABC Co. 4444
1 ABC Co. 1111

to:

ID Name Code
1 ABC Co. 5555 4444 1111

This may possibly work with Word the way I would like it to. As always,
thanks for the help!
 
Hmmm... can't imagine how you could do that with Concatenate() in a
query. Unless I'm wrong about this, you would still need some VBA code
in Access to export a text file of this form.

If you do manage it, I'd be interested to know how!

Good luck,
Nikos
 
This works within limits: namely that the "subform" records can be
presented in a single merge field in Word. Typically one needs to
concatenate with CRLF:

<<ABC Co.>>
<<5555
4444
1111>>

And of course one can create a Word table with two or more merge fields
side by side, each displaying concatenated values from a single field of
the underlying data.
 
Back
Top