Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query to Treat Excel
As a Relational Data Source

(continued)

In the Add Tables dialog, double-click on each of the tables you want to add. Notice that all of the named ranges appear here.

For this example, let’s add all of the tables. To do so, select each table in turn, and then choose Add or double-click. Doing so displays them in MS Query, as shown here. After you've added each table, close the Add Tables dialog.

The grey pane near the top of this figure is called the Tables pane. The white area at the bottom is called the Data pane. When you execute the query, the data will be returned to a grid in the Data pane.

The middle pane is called the Criteria pane. It isn't visible by default. To see the Criteria pane, choose View, Criteria. You also can choose the Show/Hide Criteria button, shown here, to toggle whether this pane is visible.

Now, let’s join the tables shown in the Tables pane.

The matching field in CUST and ORD is CustNum. Click on CustNum in CUST, and drag it to CustNum in ORD. When you drop, a line will appear, joining the two tables.

The matching field in ORD and SREP is SalID. In a similar fashion, connect the SalID field between ORD and SREP.

Now, let's use these tables to create a query.

Suppose we're interested only in sales in West Virginia. In that case, we would restrict the returned data set to just the records where the ST (state code) field in the CUST table is equal to WV.

We set up this filter by dragging the ST field from the CUST table to the top-left cell of the Criteria pane, and then by expressing the filter we want to use. You tell MS Query what value you want this field to be equal to by typing the value in the second line of the criteria pane. In this case, we type WV. (MS Query adds single quotes around WV when you move off the cell.)

On the other hand, if we wanted to show sales everywhere except West Virginia, we could enter the expression, <> WV in this cell. This would return all records where the state code does not have the value WV.

Please note that these criteria are not case sensitive when you query Excel files, but they might be case sensitive when you query other data sources. For example, queries against an Oracle or SQL Server database may be case sensitive, depending on how your database is set up.

Next, we need to tell MS Query which columns we would like to see in our Excel report. For this exercise, let's choose to see the customer number, the customer’s last name, the type of customer (cash or credit), the amount of the order, the delivery date, and the name of the sales rep. To do this, double-click on the fields in the tables shown in the following figure, and they'll appear as headings in the data grid.

previous 1 2 3 4 5 next