Creating Queries

Note

Once you create a query, you can use it for mail merges the same way you do a database table.

Queries help you focus in on specific information in your database. For example, in a huge database with lots of fields, you can create a query that shows just first names and email addresses; or you can create a query that just shows mailing label information.

Or let's say you're going to drum up business for your Java certification seminars , so you want to call all the people in your list of contacts in a 20 mile radius who aren't Java certified. Figure 35-47 shows the table that will be the basis for examples in this section.

Figure 35-47. Data source viewer window, showing data to run a query on

You can write the query in straight SQL, or use the Query Definition window. You can edit a query created by the Query Definition window in the SQL editing window, and vice versa.

Writing or Editing a SQL Query

1. Open the SQL query composition/editing window.

   • In the data source viewer, right-click on Query and choose New SQL Command.

     

     Note that if you are attempting to edit an existing query, this will open the query editing window with the graphical interface, not the SQL writing window.

   • You can also go into the Data Source administration window and select the data source, then select the Query tab. Click the Enter SQL Command icon.

   

2. Type the SQL command. For full SQL syntax consult your SQL documentation. The online help also contains examples and additional information.

   An example is shown in Figure 35-48. The code says, give me the contents of the NAME field and the PHONES field in the contacts_marketing table, but only for the people who have O stored in their USER1 field (the field where whether they're Java certified is stored) and who live in Boulder or Golden. And list the results in ascending order by the contents of the NAME field.

   Figure 35-48. Writing or editing a query in SQL

   

3. Save the query by clicking the Save icon and enter a name for the query.

   

4. Run the query by clicking the Run icon.

   

5. The results will appear in the query window, shown in Figure 35-48.

Figure 35-49. Results of the query, run in the SQL query composition window

Using the Query Definition Window

1. Choose View > Data Source to open the data source viewer. Then open the query definition window by right-clicking on the Queries option and selecting New Query Design.

   

2. Use the query design window in Figure 35-50 to compose the query. The online help contains examples and additional information.

   Figure 35-50. Selecting what you want to see in the query

   

   Note

   Right-click in the area on the left below the Or options and select Distinct Values, if you want to only get one record back when data appears multiple times. For example, to record all the last names in your address database in a query but the name "Sierra" occurs several times, you can choose the Unique Values command to specify in the query that the name "Sierra" will occur only once. In SQL this command corresponds to DISTINCT keyword.

3. Save the query by clicking the Save icon and enter a name for the query.

   

4. Run the query by clicking the Run icon at right.

   

5. The results will appear in the query design window, as shown in Figure 35-51.

   Figure 35-51. Results of the query, run in the query design window