Flatten DB for Word Merge (Access 2000 Sr-1)

Topic

New Reply

We have a set of Access records representing families; the individuals’ records tied together relationally by a family ID key. We want to print out the family record on a Word form, but because Word only works with one data file, and some “families” are singles, some married and some with several children, I am having trouble trying to get the kids’ data into a format I can export to Word along with the unambiguous parents and addresses. I have codes that ID children so I can identify those records. I have the feeling I could use a report with imbedded datasheet subform, but the people who use this stuff are only familiar with Word and we don’t want to keep designing custom reports. Any ideas?
Thanks!

Reply | Quote

Replies

We’ve done some things similar to this – we actually used a single table with a flag to indicate whether a record was a family, a person, or both. Then we used a linking table to create relationships between families and persons, and indicated the kind of relationship to the family. This lets people belong to multiple families. But I digress . . .

I would suggest you look at creating the custom report and then exporting it to an RTF format – Word just doesn’t handle subreports at all. Access can do that kind of report in a breeze, so you do the formatting in Access and then export it. I should caution you that you can’t make the formatting very complex, or it won’t look very pretty in RTF format. The only other approach I can think of is to use OLE Automation and create a Word document on the fly from Access – it takes a fair bit of programming in VBA for both Access and Word to do that kind of thing. If you want to explore that approach I can point you to some useful references.

Reply | Quote

Wendell, you confirmed my concerns. I’ll take another crack at a report and see if they’ll accept the limited formatting. We really don’t need a programming project just now.
Thanks!
Gloria

Reply | Quote

Hi Gloria
I have had some success mail merging and including a database field with an SQL statement within it.
In this scenario the database field would pick up the children based on the family ID field.

No knowing how many records you are talking about it is a stab in the dark.

Regards
Geof

Reply | Quote

Geof, Thanks for the idea. Our AS/400 is an older model and we never installed SQL because of the cost (church environment). We also have delayed installing MSExchange for the same reason. We usually just dump to a delimited text file to the PC.
By importing into ACCESS we have now successfully created a report tha I think they can live with, though the formatting is simple. In fact, our PC administrator is happier with the idea of an Access report now because of the time it has been taking with some of the larger merges in Word. I hadn’t realized how much support time she spends with users’ difficulty with merges overall. So writing the report was the simpler solution for us.
Again, thank you for your input and taking the time!
Gloria

Reply | Quote