Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query with Text Files
For Excel PivotTable Analysis

(continued)

We'll also join SALESREP (Sales Rep ID#) in the ORD table to the SALID (Sales Rep ID#) field in the SREP (Sales Reps) table. Note that, although these last two fields do not have the same name, they do have the same data format and contain the same domain of data; that is, the numbers in the two tables match.

Add Fields to the Report

Our next step is to specify the fields to add to our report.

At the top of the list in each table box is an asterisk. Double-click on the asterisk if you want to add all of the fields in this table to your report. Otherwise, double-click each field you want in your report. This will cause the field to appear in the column heading of the Data Pane, at the bottom of the display.

For this example, we want the fields illustrated in this figure:

You'll find it easier to select the fields in the order that you want them to appear (left to right) in the report. If you decide you want to rearrange them after you've selected them, you can select a column heading and then drag it to the position you want. The program is a little fussy at that point, but it can be done.

You'll see two buttons with exclamation points in the toolbar. The right-most one includes two semi-circular arrows, as shown here. Make sure this button does not appear to be pushed in. If it is, MS Query will attempt to run the query every time you make a change to it. This can be annoying.

Add Criteria to the Report

For this example, we want to add criteria. To display the Criteria Pane, click on the Show/Hide Criteria button shown here. After you do so, the Criteria Pane will appear between the Table Pane and the Data Pane.

For this example, we want to run a separate report for each state. Therefore, we'll add a criterion to restrict the returned data set by state. To do so, drag the “ST” field from the CUST table box to the Criteria Field of the Criteria Pane.

If this were a one-time report, and you only wanted the records for, say, West Virginia, you could just type "WV" in the Value line of the Criteria Pane. But in this example, we want a refreshable report that will allow us to choose each state as needed. This means we must add a parameter instead of a value in the value line.

To indicate a parameter place a "prompt" string -- which is enclosed in square brackets -- in the value field. In this example, we've added "[State]" in the value field.

Entering a prompt string will cause a prompt box to come up when we run this query. The string between the brackets will be displayed in the box to remind us what to type in the text box. The query will then run with that value as its criterion.

In MS Query you can have multiple, complex criteria, and more than one of them can be parameterized. The only requirement is that the query must be generated using the graphic interface. More complex queries require a different method, beyond our scope.

Test Your Query

At this point, we've finished our query, and MS Query has constructed an SQL statement for us. We can see it by clicking on the "SQL" button in the toolbar. Notice that the parameter is represented by a question mark. After you view the query, Cancel this box.

To test your query, click on the exclamation point icon in the toolbar, shown here. MS Query will display a dialog titled Enter Parameter Value. This dialog will contain a text box for each prompt string you've specified. For our example, it will say "State". Enter "WV", "OH", or "PA". When you click on the OK button, the data will be displayed in the Data Pane.

Save the Query

To save the query, choose File and Save As. This works pretty much as you would expect, with one exception: When you use a prompt, MS Query prompts you at inappropriate times. Specifically, when you save your query or when you close it, Excel issues the prompt. When this happens, just give Excel the information it asks for and then save the file as you normally would.

It's a good idea to save your query in the default directory. If you keep all your queries in this directory, MS Query can find them on its own without your having to search for them.

previous 1 2 3 4 5 next