Merge optional data

[hughess7]

Hi all

We have a report that is produced manually in ms word, at the moment there
is a standard template per country (different translations) and our staff
complete the word document by inserting their findings from an audit. There
is a mixture of tables and text currently, the word doc is chopped and shaped
manually and data inserted into relevant places, or deleted if not required
etc. Sometimes different paragraphs of text need inserting depending on the
results of the audit, at the moment both paragraphs exist and the auditor
deletes one of them.

This is obviously very time consuming, open to errors etc. Some of the
results of the audit are actually stored in an Access database but the
process is completely manual and there is no link between them.

What we want is for the report to be totally driven by Access without the ms
word doc ever getting edited directly, and being able to reproduce the report
at any time (by modifying the data in Access if necessary) and pressing the
button to run the report again. It just is not possible to create this type
of report in Access itself as you can not insert the results amonst
paragraphs of text formatted correctly (ie numeric, percentages, currency,
dates etc), all in alignment - nightmare!

So... Using a mail merge facility I have managed to create a datasource in
Access by the means of a query, pressing a button in Access creates the
report from a word template and prints it direct to PDF (without saving as a
word doc so it can not be edited this way). There is one template per country
and Access selects the correct one from the current record (countrycode). So
far so good...

Now the next challenge is how to deal with the 'optional' text - in
particular the paragraph headings, bearing in mind they are in different
languages too. I can create a record and present the user a form in Access to
capture all the extra info that is currently missing, but how would I create
the section headings in the word doc (in the appropriate language) and make
sure the correct text appears below it? Also, some data only needs to appear
in a table in word if it is above a certain threshold ie 100, if below it is
not displayed on the report.

For example we have sections of the report called Issues, Administration,
Reception etc and they may require comments or they will need excluding from
the report if not, depending on the audit findings.

Sorry this is quite complicated and very difficult to explain! Any ideas
most welcome thanks!!

Sue

 

[BruceM]

I'm not sure why you say it is not possible to produce the report directly
in Access. Even if you are trying to format something differently in the
middle of a paragraph (make it bold, for instance), that capability exists
natively in Access 2007 (as I understand), and there are third-party
utilities to accomplish it in pre-2007 versions.

As for the optional data, I don't understand the problem. Why can't the
data just be part of the record in Access? I don't know if there is a way
to have Word somehow perform the data validation, but I doubt it. You will
probably need to be sure the Access record is complete before allowing it to
be part of the mail merge query.

 

[hughess7]

Sorry I wasn't aware of that - we use Office 2003. I will investigate the 3rd
party utilities...

Currently my thinking at the moment is to have all the possibilities
presented as a record to the user with a set of tickboxes and only the ones
the user ticks gets added to the datasource somehow to transfer to word.

Thanks
Sue

 

[BruceM]

Here is a free third-party utility. I have not used it, so I cannot advise
about the details, but another posting will probably turn up people
experienced with this:
http://www.lebans.com/richtext.htm

If you can set up the query to produce only the information you need it
should be possible to import it into Word as a mail merge data source, but I
have very limited experience with using Access as a mail merge data source.
For instance, you could concatenate a number of items together to form a
block of text, but there may be limitations at the Word end of the process
of which I am not aware.

If you are looking to insert something like a bold text phrase into the
middle of a paragraph you cannot do that in Access 2003 except by way of a
third-party tool. However, if formatting applies to an entire paragraph an
Access report should be able to handle that with no problem.

I understand your situation is rather complex, and therefore difficult to
convey in this forum, but I do need to say that right now I can offer only
very general suggestions. If you could provide some dummy text, headings,
etc. along with an indication of their source (table fields, constants,
etc.), and a bit of description of how you wish to format them, perhaps I
can offer some Access-specific suggestions.

 

[hughess7]

Thanks, I have started to see if I can create a form template to collate all
the required data from the user input, having a field for every possible
requirement and tick boxes to gather the optional data. I have created text
boxes which are lookup fields to make sure I obtain the correct translation
of headings and will try coding it so any field that is not null or has been
ticked is added to the recordsource in combination with the pre-existing
query which has all the other relevant calculations etc which merge with
word. I may have to do a union query to join the data in the end, I am not
sure yet until I get to that point.

It is not formatting such as bold etc, but making sure the field is formated
as a date, numeric with correct decimal places etc. Also the report is at
least 10 pages long with a lot of standard text etc and putting this in
Access alone is very time consuming and frustrating trying to align all the
text properly with the correct spacing etc.

I am on holiday now until Wed at least, packing my house up ready for moving
so I will worry about this later. I may come back with further / more
specific questions next week ;-).
Thanks again
Sue