Mail Merge For Reports - The Data Source
Mail Merge is useful for creating reports
that are issued on a periodic basis. For users unfamiliar with Access reports or
for users who need more control over formatting and page layout, a pre-made
query in Access informing a Mail Merge document can be a great tool.
If you'd like to know more about Access, come
join us at UCSC-Extension for Access Beginning, Intermediate and
Advanced.
The first thing to do is create the query in
Access (or find someone else to do it for you—email
us here ;-{) ). When you create this query, remember that it is
informing your Word document. You can only use one query as a source so be sure
you get everything you need. You may not get the most elegant SQL but you’re
writing to a different specification: not quality in this case, but quantity!
To download the files used in this example, right click on the links below
and choose Save Target As...
Access Database
Word Document
In this example we have a corporate goal to sell 30%
new product (less than 6 months old) both by unit sales and by revenue each
month. We issue a report with revenue, sales volume, percent of new product
sales and of course, whether or not we hit our target. When you are not be able to
craft one query that returns all the data you need, create supporting
sub-queries and one master query.
The first thing we’ll do is create queries
that find the percent of new product sales last month and the percent of older
product sales last month. The key is to base the queries on the system date so
that we write them once and can reuse them without further modification. It may
be that your query will be based on an entire population of lab or test results and will
have no time dependence at all—so much the better!
In this picture you can see part of the
query LastMonthNewProducts. The columns shown capture last month’s data and make sure
that the products summed are less than
six months old using the DateDiff function. As you may have guessed, DateDiff
returns the difference between two dates in years, months, weeks or days. It is
available in Excel as DateDif but is not well documented.
The date sensitive column in the query that captures the older product sales(LastMonthOldProducts)
looks very similar but the criteria in the right column is >=6 months.
When we’ve written these two queries, we’ll
write one more to pull the information together and evaluate it. This one will
be the source of our monthly sales report. The master query is called Percent New Products.
It calculates new
product sales percentages in terms of volume and revenue and supplies Word not
with numbers, but with text. Note that in the image below, the Format function
returns a string value. That's on purpose.
Be careful! What you see in Access is not
necessarily what you get in Word if you output numbers. Word does not honor
formatting from Access unless you output text strings.
We also output whether or not we met our goal.
Our query checks the percentages and uses the IIf function to return either ‘met our goal’ or ‘did
not meet our goal’. We’ll use this information in our report.
Let’s see now
how to set up Mail Merge in Word.
|