When querying databases, you've got to ask the right question.

Enhancing the power of relationships.

The power of relational databases lies not so much in their ability to store vast amounts of information, but in their capacity to sort out complex data relationships and then assemble custom reports on them. Thus, for example, a database that contains customer names and sales information can report which customers bought a particular product and any number of other relationships that link them. The key, however, is knowing how to ask the right questions of the database, and this article shows how.

In relational database jargon, a question is called a query and represents a request for information from tables in one or more databases. When a query is posed and the data sorted, the underlying database doesn't change. The software just looks into the tables, searches for and processes the requested relationships and then issues the customized answers, leaving the original data untouched.

Most applications built on relational databases incorporate a querying tool known as query-by-example (QBE), which uses a graphical approach to construct queries. Although QBE tools are visual and relatively easy to use, they're somewhat limited. To create complex queries, especially when dealing with multiple databases, users must turn to a language called structured query language (SQL) or structured English query language. The acronym for both is pronounced sequel. In today's world of client-server architecture and data warehouses, it's important to understand the fundamentals of SQL, for it is the basis of all database queries.

We'll build queries using the QBE feature of Microsoft Access and briefly explain the SQL code underlying each request.

ADDING MORE DATA

Before we can begin, we must add more data to the database file that we built in earlier articles (see the box "Download the Database Tables"). Launch Microsoft Access and open the file--Cust_Track_2000. Click the Forms tab and open the previously created Customers form by highlighting the selection and clicking Open (exhibit 1, at right).

[Exhibit 1 ILLUSTRATION OMITTED]

If the toolbar isn't displayed, click View, Toolbars, Database, and it will resemble exhibit 2, at right.

[Exhibit 2 ILLUSTRATION OMITTED]

Add the following sales orders: For customer 1 (Mark Fly), add a sale by clicking in the OrderDate column in the first empty row and typing the date 12/20/99, as in exhibit 3, page 37. Now click in the first empty row under Order Details_Product ID and type 1; then tab to Quantity and type 40; tab to SalePrice and type 37.50. Finish the order by filling in the second row of order details to match exhibit 3.

[Exhibit 3 ILLUSTRATION OMITTED]

Using the navigation bar at the bottom left of the form, click on the next record button to get to customer 2. Make sure the information for Julie Fly shows. Now input a new order by typing 12/10/99 in the OrderDate column and fill in the order information found in exhibit 4, page 37.

[Exhibit 4 ILLUSTRATION OMITTED]

Similarly, add an order for Lora Masters (exhibit 5, page 37) and LaVonne Hayes (exhibit 6, page 38). When finished, close the Customers form by clicking on the bottom X in the upper right hand corner of the form.

[Exhibits 5-6 ILLUSTRATION OMITTED]

DESIGNING A SINGLE QUERY

Now that the new information is entered, we'll design a query that selects just our Arkansas customers so we can telephone them. To do this we need the company name, contact first name, phone number and state (so that only Arkansas businesses can be selected).

Click the Queries tab and the New query button. Highlight the Simple Query Wizard and select OK. Select Tables: Customers, if it isn't already selected. Pick the fields desired from the Customers table by moving them from Available Fields to Selected Fields using the move button as shown in exhibit 7, at right.

[Exhibit 7 ILLUSTRATION OMITTED]

Click on Next and accept the prefilled Customers Query by clicking Finish. At this point, the query will report all customers from all states. To limit it to Arkansas, select the design button to modify the query using the QBE feature. To select Arkansas, type Arkansas in the Criteria row under the StateOrProvince column, as shown in exhibit 8, below.

[Exhibit 8 ILLUSTRATION OMITTED]

Now execute the query by clicking on the run button. This should produce a list of Arkansas customers, as shown in exhibit 9, below.

[Exhibit 9 ILLUSTRATION OMITTED]

It isn't necessary to design the same query every time the underlying data change; the database automatically updates as you enter new information. Thus, the next time you run the same query, all current information will be displayed.

You can view the SQL programming code by clicking View, SQL View, as shown in exhibit 10, page 40.

[Exhibit 10 ILLUSTRATION OMITTED]

Exhibit 11, page 40, shows the programming code behind the scenes as you use QBE. When you are finished viewing, close the query and save the changes when prompted, calling the query Customers Query.

[Exhibit 11 ILLUSTRATION OMITTED]

To understand some of the SQL code terminology, see the sidebar, "What the SQL Terms Mean," below. The SELECT statement specifies the columns to include in the query. Notice that Access places the table name (Customers) and a period (.) before each column name. While this isn't necessary for single table queries, it's required when specifying more than one table because they might share a common name. The FROM statement indicates which table should be used in the query (Customers), and the WHERE statement limits the query to Arkansas.

DESIGNING A TWO-TABLE QUERY

Next we'll design a query that monitors customers' purchases--specifically how long it's been since their last order--and that will require accessing two tables. To start the query, click the New query button and use the Design View option. Click on OK. The information needed is in the Customers and Orders tables, so highlight them. Click Add and close the Show Table. You can adjust the Customers table by clicking and dragging as needed until it looks like exhibit 12, page 41.

[Exhibit 12 ILLUSTRATION OMITTED]

Now select the desired fields (Company Name, ContactFirstName, PhoneNumber and OrderDate) from the tables by double-clicking on them. Since only the most current sales order is needed, turn on the totals feature by selecting the Totals button on the toolbar. To select the most recent (last) order date, click on the Group By box in the Orders column and click on the down arrow key to select Max as shown in exhibit 13, page 41.

[Exhibit 13 ILLUSTRATION OMITTED]

To organize the list, sort the results so the oldest will appear on top. To do this, click the Sort box under Orders (exhibit 14, page 41), and select Ascending.

[Exhibit 14 ILLUSTRATION OMITTED]

Now click the Run query button and the results will resemble exhibit 15, at right.

[Exhibit 15 ILLUSTRATION OMITTED]

When finished, close the query and a prompt will ask if you wish to save changes. Select Yes and type a query name, Q_Old_Orders, and click OK.

EXAMINING THE TWO-TABLE SQL CODE

In order to examine the SQL programming code, highlight Q_Old_Orders, click Open, View and SQL View. The SQL code for the two-table query is shown in exhibit 16, at right.

[Exhibit 16 ILLUSTRATION OMITTED]

The SELECT statement specifies the columns to include in the query from the Customers and Orders tables. Notice the query asks for a data value equal to the maximum (max) order date (which Access named MaxOfOrderDate) AS of the most recent date in the OrderDate column of the Orders table. The FROM statement indicates that we are using the Customers table and joining (INNER JOIN) it with the Orders table based on common values in the CustomerID column of both tables. The GROUP BY clause tells the query to group the results by CompanyName and to show the ContactFirstName and Phone Number along with the CompanyName. Finally, the ORDER BY clause tells the computer to sort the query in ascending order starting with the oldest OrderDate in the query.

DESIGNING A MULTIPLE TABLE QUERY

How can SQL queries be used in real situations? Let's say that after taking an inventory, the owners discovered the business was running low on shirts. While shirt sales were high, December net income fell from November. To find out why, perform the following new query.

Click New query and Select OK for the highlighted Design View. Highlight and Add the following tables: Customers, Orders, Order Details and Products. Close the Show Table box and arrange the tables to view all the items, as shown in exhibit 17, page 42.

[Exhibit 17 ILLUSTRATION OMITTED]

Double-click on the following from the tables to add the fields to the query: Company Name, Order Date, Product Name, Cost, Quantity and Sale Price. Next, add calculations to the query by using the bottom scroll bar to show more empty columns to the right. In the first empty box, click in the Field row and type Profit Per Unit: [Saleprice]-[cost], as in exhibit 18, above. The words before the colon are the column heading, and the words after it are the calculation commands. This will calculate the profit per unit for each item.

[Exhibit 18 ILLUSTRATION OMITTED]

To calculate the profit per sale, click in the next empty box in the Field row and type Order Profit: [Profit Per Unit]*[Quantity], as in exhibit 19, at left.

[Exhibit 19 ILLUSTRATION OMITTED]

To display this item as currency, click in the Table row under the Order Profit column, click on the Properties button and in the Field Properties' Format cell, highlight Currency, as in exhibit 20, at left, and close the Field Properties box.

[Exhibit 20 ILLUSTRATION OMITTED]

To calculate the total sales dollars for each order, click in the next empty box in the Field row and type Sales $:[SalePrice]*[Quantity]. To display the total sales dollars as currency, repeat the above operation in the Table row under the Sales $ column.

To understand the drop in net income, calculate the profit margin by clicking in the next empty box in the Field row and type Profit Margin: [Profit Per Unit]/[SalePrice]. To display this information as a percentage, click in the Table row under the Order Profit column, click on the Properties button and in the Field Properties' Format cell, choose Percent, and close the Field Properties box. To display only the shirt sales, type shirts in the Criteria row in the ProductName column, as shown in exhibit 21, at left.

[Exhibit 21 ILLUSTRATION OMITTED]

Finally, to organize the query by order date, select the Descending option in the Sort row for the OrderDate column and then click on the Run button to get the results, as shown in exhibit 22, above.

[Exhibit 22 ILLUSTRATION OMITTED]

Based on these data, it appears the inventory level is down because the company sold shirts at a lower price in December. It also appears that the December net income might be less than in November due to the smaller profit margin.

EXAMINING MULTIPLE TABLE SQL CODE

To examine the SQL code, click View, SQL View. The code will look like exhibit 23, below.

[Exhibit 23 ILLUSTRATION OMITTED]

The query says SELECT from Customers, Orders, Order Details and Products tables. Notice the use of the AS clause in the SELECT statement. For instance, we created a data item in the query called Order Profit, which is obtained by multiplying Profit Per Unit by (*) Quantity. The FROM clause indicates that the Products table will be joined (INNER JOIN) with the Customers table; however, the join must be threaded through the Orders and Order Details tables. The INNER JOIN statement begins by searching for a match on the Customer ID values between the Customers and Orders tables. When one is found, the INNER JOIN next matches the Order ID columns in the Orders and Order Details tables. Last, the INNER JOIN links the Product ID columns of the Orders Details and Products tables using the ON clause. As a result of this series of INNER JOIN commands, we know which customers placed orders and what products they ordered. The WHERE statement limits the Product Name to Shirts and the ORDER BY clause requests that the query results be shown in descending order (DESC) based on the OrderDate in the Orders table.

When you're ready to close the query, a prompt will ask if you wish to save changes; select Yes and type Q_Net_Income Down and click OK.

AUTOMATING QUERIES

To automate this query for other users, make the following adjustments to match exhibit 24, above.

[Exhibit 24 ILLUSTRATION OMITTED]

Highlight the query Q Net_Income_Down and click on the Design View button. If the SQL is showing, click on View and Design View. In the Criteria row under the OrderDate column, type Between [Type Beginning Sales Date `MM-DD-YY'] And [Ending Sales Date `MM-DD-YY']. This allows the user to specify the beginning and ending sales dates. Similarly, in the Criteria row under the ProductName column, type [Type of Product- `Jeans' / `Shirts' / Etc]. This allows the user to specify the type of product to examine.

Click on the Run button to get the Enter Parameter Value box in exhibit 25, at left.

[Exhibit 25 ILLUSTRATION OMITTED]

Type 12-01-99 for the beginning date and select OK, and 12-31-99 for the end date and click OK. Note that Access converts the hypens (-) in the date to slashes (/). Then type Jeans as the type of product and click OK. This should display the results shown in exhibit 26, below. After checking the results, close the query and save the changes by clicking on Yes.

[Exhibit 26 ILLUSTRATION OMITTED]

As you can see, relational databases can do more than organize information and provide lists. They can calculate data that are stored in those lists.

To be sure, relational databases have considerable power and flexibility--once you learn how to ask the right questions in the right way. While users often rely on QBE tools to produce simple queries, there are times when one must use SQL to refine database queries. This is particularly true in distributed database and data warehouse environments. There is much more that databases can do, and you'd be wise to begin using this application in your work.

Download the Database Tables

This is the fourth in a series of tutorials on creating and using databases. If you wish to follow along in this article as we describe how to create queries, you will need the database we built in two earlier articles because we will be using it in this tutorial (see "Building a Database From Scratch," JofA, Nov. 99, page 63, and "Working With Databases," JofA, May00, page 70).

You can either build your own database using the earlier two articles or you can download the completed database from http://ais-srvr.coba. usf.edu/Jofa/Index.htm.

Key to Instructions

To help readers follow the instructions in this article, we use three different typefaces.

Boldface type is used to identify the names of icons, agendas, URLs and application commands.

Boldface italics type is used to identify options in a menu.

Sans serif type indicates instructions and commands that users should type into the computer.

What the SQL Terms Mean

There are several variants of SQL, each having its own peculiar syntax. However, if you understand the fundamentals, it is easy to adapt to the idiosyncrasies of each relational database package.

SQL
Statement   Clauses    Explanation

SELECT                 Lists the columns to be included in the
                       query (such as Company Name, Order Date).

            FROM       Identifies the tables from which the columns
                       will be chosen (Customers, Orders).

            WHERE      Includes the conditions for row selection
                       (Company Name = "Mart," Order Date =
                       "12/20/99").

            AS         Defines a new data item created in the query
                       (Total) as the result of a mathematical
                       function of two or more columns in the table
                       (Quantity x Sale Price).

            ORDER BY   Sorts rows in ascending or descending order
                       based on one or more column values (Order
                       Date, Customer Last Name).

            GROUP BY   Groups rows based on common column values
                       (Group By Order Date, Group By Product).

INNER                  Combines two or more tables into a dynaset
JOIN                   (virtual table) based on the quality of
                       values in common columns.

            ON         Defines the common columns used to join two
                       or more tables into a dynaset.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

EXECUTIVE SUMMARY

* RELATIONAL DATABASES can do more than just store vast amounts of information. Their real power is in their ability to sort out complex data relationships and then assemble custom reports.

* THE KEY TO HARNESSING that power is knowing how to ask the right questions.

* WHEN A QUERY IS POSED and the data sorted, the underlying database doesn't change. The software just looks into the tables, searches for and processes the requested relationships and then issues the customized answers, leaving the original data untouched.

* MOST APPLICATIONS BUILT on relational databases incorporate a querying tool known as query-by-example (QBE), which uses a graphical approach to construct simple queries. More complex queries use a language called structured query language (SQL).

* A DATABASE APPLICATION can do more than just sort out relationships from a single database; it can search multiple tables and report the results in myriad ways.

* IT CAN EVEN PERFORM some basic calculations, such as the profit earned from a sale or profit margins.

* QUERIES CAN BE SET UP TO PERFORM complex tasks with just the click of a button, and they can be saved for future use.

DAVID C. HAYES, CPA, is an accounting doctoral student at the University of South Florida, Tampa. His e-mail address is [e-mail]. JAMES E. HUNTON, CPA, PhD, is the Quinn Eminent Scholar of Accounting Information Systems at the University of South Florida. His e-mail address is [e-mail].