Using SQL

We can try some of our SQL commands using an SQL test program that we will explore in more depth in Chapter 25. For the moment, however, we'll use it just to see how SQL might be used. Figure 24.5 shows a screen shot of how the program looks after selecting the Xtreme.mdb database.

Figure 24.5. SQL Tester program after selecting a database.

The Table Names list box shows the tables that are contained within the Xtreme.mdb database. If you click one of those tables, the program reads the fields from that table and presents them in the Field Names list box. This is shown in Figure 24.6.

Figure 24.6. SQL Tester program after selecting a table.

Programmer's Tip

Tables that begin with Msys* in Figure 24.5 are internally generated tables that aren't normally used in SQL queries. We could have written the test program to remove these, but we left them in to show you that Access does maintain tables that aren't normally visible to you.

The first entry in the Field Names list box is an asterisk ( * ) that enables you to select all the fields from the table. Otherwise, you can double-click on the individual fields you want to use in the query. The fields are shown in alphabetical order, but that probably isn't the order in which they're actually stored in the table (more on this issue in Chapter 25).

If you use the wildcard asterisk and don't want to restrict the query with a WHERE predicate, you would simply click on the Submit button. A sample run is shown in Figure 24.7.

Figure 24.7. SQL Tester program after selecting all fields and clicking Submit.

The query that was tested is shown in the Query text box near the center of the display. The data grid at the bottom displays the results of the query. The title bar for the data grid shows how many records were returned from the query. Although you might not be able to tell, the background color for each row alternates between white and a pale green. (This little display touch should give you a warm fuzzy feeling if you're old enough to remember the mainframe style of printer paper.)

Adjusting Column Widths

Notice that some of the columns aren't wide enough to show all the data. For example, although the first column width is okay, the next two column headers are too long to fit within the default column width. Even though the data for the second column can be seen in full, the same is not true for column three (Customer Name ). However, the data grid control is smart enough to let the user widen a column at runtime. Figure 24.8 shows the same display after I expanded the column widths. To widen a column, simply move the cursor to the vertical bar between the two fields. The cursor will change to the double-arrow cursor. Click the left mouse button and drag the bar until the column is the desired width.

Figure 24.8. Sample run in Figure 24.7 after increasing column widths.

Now you can see the full column header name as well as the data within the columns.

Adding a WHERE Predicate

Figure 24.9 illustrates how to enter a query with a WHERE predicate. After selecting a subset of the available fields, we clicked the Add Where button. The program is now waiting for you to enter the WHERE predicate.

Figure 24.9. Adding a WHERE predicate to a query.

Notice that the query added each field name as I double-clicked it before clicking the Add Where button. If the field name contains a space in it, the program supplies the required brackets around the name (for example, [Contact First Name] ). If there is no blank space in the name (for instance, City ), no brackets are added.

Programmer's Tip

You can use brackets around a field or table name in a query at any time. However, they aren't required unless the field or table name has a blank space in it.

The program places the WHERE keyword in the query and waits for you to type in the rest of the query. A complete example is shown in Figure 24.10.

Figure 24.10. Sample run with a WHERE predicate.

As you can see in Figure 24.10, we filtered the dataset to those instances where the Contact Position in the Customer table was Owner. The query returned 120 records, which is smaller than previous queries. (Compare this with the data grid title bar in Figure 24.9.)

Using an ORDER BY Clause

In Figure 24.10, the records that are returned are in no particular order. We can, of course, add an ORDER BY clause to place the same data set in order. A sample run using an ORDER BY clause is shown in Figure 24.11.

Figure 24.11. Sample run using an ORDER BY clause.

Figure 24.11 shows that the same number of records is returned, but now they're sorted in alphabetical order by last name. You can see the query that was used in the Query text box.

Actually, a simple ORDER BY clause like the one used in Figure 24.11 isn't necessary. The reason for this is because the data grid control is smart enough to sort a single column for us automatically. To place the records in sorted order, simply click on the column head that you want to use for the sort . If you had clicked on the column head titled Contact Last Name in Figure 24.10, you would have produced the same results shown in Figure 24.11.

Does that mean that ORDER BY is unnecessary? No, not at all. Consider the query and results shown in Figure 24.12.

Figure 24.12. Sample run using a compound ORDER BY clause.

We scrolled the dataset to get to the city of Hong Kong, which has multiple records for that city. Within the records in which the city is Hong Kong, notice how the records are sorted in alphabetical order by last name. We did this by using a compound ORDER BY clause in the query as shown in Figure 24.12. You cannot produce this ordering by simply clicking a column header in the data grid.