Make the most of your repository

Quick and Easy Mail Merge

Why not make the most of your repository, as well providing the backbone of your reports it is also a great source of clean organised data that is easily accessible by other programs. Here’s one way to address envelopes or letters using MS Access and Word.

Create a new blank database in Access.

Create a new query in design mode, close the Show Table dialogue and go to Query>SQL Specific>Pass-Through.

Now click on the Properties icon on the toolbar and click the ellipsis next to ODBC Connect Str, this will open the Select Data Source dialogue.

Click the Machine Data Source tab and click New and follow the instructions to create a Data Source Name based on Oracle BI Server from the drivers list, follow the instructions and enter appropriate user names and passwords to suit your systems configuration. Enter your repository user name and password and answer yes to save this information in the connection string.

Type your SQL query. Click the icon to run the query and test you SQL.

Now create another new query in design view, click the queries tab in the Show Table dialogue and select your pass through query, add the columns to the new query.

Click the drop down arrow next to the Query Type icon and select Make-Table Query, save the query with an appropriate name, and click the Run Query icon.

You will be presented with a warning about the number of records to be pasted into the table, dependent on your needs you may decide to set some selection criteria to break the list into more manageable chunks, this may depend on whether you are merging to a letter, labels or envelopes. You could create multiple Make- Table queries to populate appropriately named tables based on select by County, State, City or Name beginning with…… They can all be based on the one pass through query.

If you have the coding skills you can automate the running of these queries and refreshing of the tables.

Once the make table query has run, click Tables in the objects list and you will see the table you have created, select it navigate to the Tools>Office Links>Merge it with Microsoft Office Word, or click the down arrow next to the Office Links icon, follow the instructions in the dialogue, simple.