Selecting Mail Merge Data Records with SQL SELECT
Introduction
The tutorial shows how to select mail merge data records from a database or a spreadsheet with help of SQL SELECT queries. SQL SELECT is the most commonly used data query language. It is used to limit processing only to a specific subset of data records that satisfy a user-defined selection criteria. For example, use this method to select customer records that are limited to a specific city or a postal code. This tutorial applies to AutoMailMerge™ plug-in for the Adobe® Acrobat®.
Tutorial Overview
The tutorial shows how to use SQL SELECT queries to limit mail merge processing to a specific subset of data records. This method can be used with Microsoft Access databases (*.accdb, *.mdb) and Microsoft Excel spreadsheets (*.xlsx, *.xls). It can be also used with any other database that supports SQL SELECT via an ODBC driver. The tutorial assumes that user already has a fillable PDF form and focuses solely on SQL SELECT examples (use this link to learn how to prepare a PDF form).
Prerequisites
You need a copy of the Adobe® Acrobat® DC along with the AutoMailMerge™ plug-in installed on your computer in order to use this tutorial. You can download trial versions of both the Adobe® Acrobat® and the AutoMailMerge™. This tutorial can be also used for older versions of Adobe Acrobat.
Step 1 - Open a PDF Form
Start the Adobe® Acrobat® application and using "File > Open..." menu open a PDF form template that was prepared.
Open a PDF form
Step 2 - Open the "Mail Merge" Dialog
Select "Plug-Ins > Mail Merge..." to open the "Mail Merge" dialog.
Open the Mail Merge dialog
Step 3 - Edit Mail Merge Settings
Press the "Edit Settings..." button to configure the mail merge parameters.
Edit mail merge settings
Step 4 - Select Data Source
Press "Browse..." button to select an input database or spreadsheet.
Press Browse button
Step 5 - Select Data Source Type
We are going to use MS Access database as an example. Select "Microsoft Access Database (*.accdb)" from the list of supported data sources. If you are working with an Excel spreadsheet, then select "ODBC Microsoft Excel Worksheet" instead.
Select Microsoft Access
Here is a table that is used in the tutorial:
Specify output options
Step 6 - Select Table
Select the data table that contains mail merge records from the list:
Specify output options
Next, check "Use custom SQL Select" option and press "Create New Statement..." button to open a dialog that will help you to create a simple SQL query. Alternatively, you can type SQL SELECT statement into the edit box.
Step 7 - Specify Selection Criteria
Use "SQL Expression" dialog to specify data selection criteria. For example, we have selected all data records that have "Corvallis" in the "City" data fields (contained in Customers table):
Specify output options
Press "OK" button to close "SQL Expression" dialog.
Step 8 - Review or Edit Selection Criteria
Now you should see the SQL SELECT statement in "Select Table" dialog:
SELECT * From [Customers] WHERE City='Corvallis'
Note that you can edit SQL SELECT statment right in the text box.
Specify output options
Optionally, edit the statement to create a more complex query by adding multiple selection rules. This can be done with AND or OR operators. For example, the following statement selects all records from Customers table that contain 'Portland' in the City field and '97035' in the ZIP field.
SELECT * From [Customers] WHERE City='Portland' AND ZIP='97035'
Step 9 - Review Selected Records
Press OK button on "Select Data Table" dialog once done entering the selection statement. Press "View and Filter Data Records" button to see the records selected by the statement.
Press View and Filter Data Records button
Only records that satisfy the SQL SELECT statement will be visible in the "View and Filter Data Records" screen. The following screenshot corresponds to "SELECT * [Customers] WHERE City='Corvallis'" expression:
Press View and Filter Data Records button
Step 10 - Complete Mail Merge Setup
Complete the rest of the mail merge setup as usual. We are not going to cover it in this tutorial. You can find a complete list of AutoMailMerge step-by-step tutorials here: https://www.evermap.com/AutoMailMerge.asp#Tutorials
More SQL SELECT Examples
The following statement selects all records from Customers table that contain 'Portland' OR 'Corvallis' in the "City" field.
SELECT * From [Customers] WHERE City='Portland' OR City='Corvallis'
Here are the selection results that contains records for both cities:
Example of the OR operator
The following statement selects records where value of the "Age" field is within 20 and 40. The "Age" field is assumed to be of numeric data type.
SELECT * FROM [Customers] WHERE "Age" BETWEEN 20 AND 40
Here are the selection results:
BETWEEN Operation sample
Working with Dates
The most difficult part when working with dates is to be sure that the format of the date you are trying to use, matches the format of the date column in the database. The following statement selects records where value of the "Date" field equals to 3/1/2019. The "Date" field is assumed to be of "Date/Time" data type in MS Access database (*.accdb) and contains only a date part.
SELECT * FROM [Customers] WHERE "Date"=#2019-03-01#
Note that proper syntax for date expressions in Access SQL is: "FieldName"=#YYYY-MM-DD#.
Here are the selection results that contain one record for March 1st, 2019:
Selecting a specific date
The following statement selects records where value of the "Date" field is before 3/7/2019.
SELECT * FROM [Customers] WHERE "Date" < #2019-03-07#
Here are the selection results that contain records for 1-6 March 2019:
Selecting a specific date
The next statement selects records where value of the "Date" field is before 3/6/2019 and after 3/2/2019.
SELECT * FROM [Customers] WHERE "Date" < #2019-03-06# AND "Date" > #2019-03-02#
Here are the selection results that contain records for 3, 4, and 5th of March:
Selecting a specific date
Using Saved Queries in MS Access
It is possible to create and save queries in MS Access database. Query is a common way to select a specific subset of data records that satisfy a certain selection criteria. Queries can be saved right into the database and become alternative views for the data tables. All saved queries will be shown in the "Select Table" screen in AutoMailMerge as Views (see screenshot below). You can work with "Views" in the exactly same way as with regular tables. Using saved queries is practical when you need to reuse the same selection criteria multiple times. There is no need to use SQL SELECT queries in AutoMailMerge, because the query already selects all necessary records.
Selecting a specific date
There are many excellent SQL tutorials available on Internet that cover SQL syntax in detail. Here is one of the good starting points: https://www.w3schools.com/sql/sql_select.asp.