Native Excel Database Integration

Once you put your data in a database, you need an efficient way to get it out. Without writing a single line of code, you can easily incorporate data from a database into an Excel workbook using Microsoft Query (MS Query).

MS Query is included with every edition of Microsoft Office. As you can see in Figure 16.1, MS Query is a visual query tool that looks similar to the query design view in Access. Using MS Query, you can define a query that runs and returns data to Excel. A query is basically a question that is phrased in terms that a database can understand. The data that a query returns is referred to as a result set or a recordset.

Figure 16.1

Microsoft Query in action

MS Query is a useful, but for some reason, underused application. This may reflect the general user's lack of understanding about databases. Alternatively, MS Query may turn some people off because of some of its usage quirks. However, if you give MS Query a chance and invest some time learning how to use it, you'll find that it allows you to do many useful things.

Data retrieved using MS Query is associated with an external data range. As you learned in the last chapter, an external data range is a range of data in Excel that is somehow associated with an external data source. You can set up an external data range so that it refreshes itself at specific times to ensure that it always contains the most up-to-date data. In addition, MS Query allows you to harness the power of parameter queries.

A parameter query is a query that is set up to prompt for or accept some criteria when the query executes. This allows you to use the same query to return data associated with a specific data item. For example, rather than creating 12 queries where each returns a specific month's data, you could create a single query that prompts you to enter the month desired. When you create a parameter query in Microsoft Query, you can instruct Excel to retrieve the parameter from a particular cell. Further, you can set up the external data range associated with the data to refresh the data whenever the cell containing the parameter changes. This is powerful stuff. I'll demonstrate an example of this later in the chapter.

NOTE Microsoft Query is not installed by default. You may require your Microsoft Office setup CD to install MS Query the first time you try and use it.

Excel, Meet My Database. Database, This Is Excel

To use MS Query, select Data ^ Import External Data ^ New Database Query from the Excel menu. If this is the first time you're querying a particular data source, you need to set up a new data source by choosing New Data Source as shown in Figure 16.2. This displays the Create New Data Source form shown in Figure 16.3.

In step one in Figure 16.3, you provide a name for the database. The name you put here is the name that shows up in the list of databases that you can see in Figure 16.2. I like to use the name of the database followed by the server or computer on which the database resides. This practice is handy in the development process because you may have two copies of a given database; one for testing and another "production" or live version.

Figure 16.2

Selecting a database

Figure 16.2

Selecting a database

Figure 16.3

Creating a new data source

Create Mew Ddtd S iur. r

(SI

What narre do you want to give your data source?

1,

|Nc*thwind - localhost

i

Select a d»iver for ihe type oí database you war* to access:

El

Ckcfc Conneci and erier any information requested by Ihe diiva;

Ï

Connect... |

Scfect a cfcfayl: idjts for yoiit data source .(oplffrtiij

4.

1

J

F Sdi/b rnjj me ID at,d paií'Arní in tlíe dala íouice deíh'í.lon

J

J] 1 OK 1 Cancel 1 J

Figure 16.4

Connecting to an Access database

Figure 16.4

Connecting to an Access database

In step two, you select a driver for the database. The choice of a driver is critical because the driver handles all of the communication between MS Query and the database. Database vendors usually provide drivers that are specific to their product. For connecting to an Access database select the Microsoft Access Driver (*.mdb).

In step three, you specify where the database resides and any other connection-related details. This step is database specific. If you selected the Access driver, the dialog box shown in Figure 16.4 appears.

For an Access database, most of the time all you need to do is click Select and locate the desired database on your filesystem. In the following screen shot, I've located the Northwind sample database.

Figure 16.5

Choosing a data source

Figure 16.5

Choosing a data source

Figure 16.6

The Query Wizard leads you through the process of creating elementary queries.

Query Wizard - Choo&e Columns

What columns of data do you want to include in jicu query? ¿valabte tables and columns: Columns in

Query Wizard - Choo&e Columns

What columns of data do you want to include in jicu query? ¿valabte tables and columns: Columns in

S Categories

A

S Customers

0 Employees

EmployeelD

ÜH3

TilleQf Courtesy

Ri'thDntfi

V

Preview üf data in selected cola nan

LastWame RrstName i

Preview üf data in selected cola nan

Iliasidc SûIes Cacfdinator 5alas Manager

[?t| Fib'ac'/' ND1,',1 I I rititpara ~| - F- | [je*!? | Cancel |

NOTE The Northwind sample database ships with Microsoft Access. It will be used for all Access examples.

In step four, you indicate a default table. This is an optional step. If you are sure that you'll always use the same table, go ahead and select it here; otherwise leave it blank. When you're finished creating the new data source, it will appear in the list of databases as shown in Figure 16.5. At this point you're ready to select the desired database and define your query.

You Are an Advanced Player

See that check box at the bottom of Figure 16.5? The one that says "Use the Query Wizard to create/ edit queries?" If you just need to return data associated with a single table or view in the database, you could use the Query Wizard. The Query Wizard (Figure 16.6) is a tool meant to make it easier to create simple queries.

Although Query Wizard does make it easier, it also limits you to the most elementary types of queries. I'd encourage you to avoid the Query Wizard and learn how to use MS Query's normal design view (Figure 16.1).

There are five basic steps to creating a query using MS Query. To illustrate the process, I'll create a query that summarizes sales in the US by an employee in the Northwind database.

Figure 16.7

After adding tables, you are ready to add fields to the result set.

Figure 16.7

After adding tables, you are ready to add fields to the result set.

Step One: Add Tables

Immediately after you select a database and assuming you're not using the Query Wizard, MS Query prompts you to add tables to the query. To add tables, double-click the table(s) you'd like to use. After you've added all the tables you need, click the Close button. An example of the Add Tables dialog box is shown here.

MS Query automatically recognizes any defined relationships between the tables and displays them in the MS Query window. In Figure 16.7, I've added three tables to the query. If you need to remove a table from the query, click once on the table and press Delete.

To summarize sales by an employee in the Northwinds database, I need the Employees, Orders, and Order Details tables.

Step Two: Select Fields to Include in the Result Set

The next step is to select the fields that you want to include in the result set. You can select fields several ways:

♦ Double-click the field name in the Table pane. This makes the selected field the last column of the result set.

♦ Drag the field name(s) from the Table pane to the Data pane. This allows you to place the field(s) before the first column, between existing columns, or after the last column. To select multiple contiguous fields, press and hold Shift and then select the first and last fields that you want to include. To select multiple noncontiguous fields, press and hold Control while selecting the fields you want to include.

♦ Click in the first empty column of the Data pane and choose from the list of fields in the dropdown control. An example of this method is shown in Figure 16.8.

♦ To remove a column from the Data pane, click the column label to select the entire column and then press Delete.

For my query, I need Employees.LastName, Employees.FirstName, and Orders.ShipCountry. Thankfully, you can also create calculated fields. For example, to summarize sales by employee, I need to determine the amount of each order detail item. The amount of each order detail item is determined by multiplying the quantity field by the unit price field and then applying the discount for the item (multiply by 1 minus the discount field). To create a calculated field, click in an empty column and enter the desired formula (see Figure 16.9).

After you enter the formula, you can double-click the formula you just entered. Doing so displays a dialog box (Figure 16.10) that allows you to change the column heading that is displayed in the result set. In fact, you can double-click any column heading in the Results pane to change the column heading that will be displayed or to indicate whether the results should be summarized in some way (sum, average, count, etc.). I added the column heading OrderTotal to the calculated field.

Figure 16.8

Selecting fields to include in the result set

Figure 16.9

Specifying a calculated field.

Figure 16.9

Specifying a calculated field.

Figure 16.10

The Edit Column dialog box allows you to edit column attributes.

Figure 16.10

The Edit Column dialog box allows you to edit column attributes.

Step Three: Filter the Results

If you need to filter the results that are returned, you need to display the Criteria pane (View ^ Criteria). You can add fields to the Criteria pane in a way similar to how you added fields to the Data pane. You only need to add fields that will be used in the filter. Table 16.1 contains examples of the operators you can use in the Criteria pane to filter the results returned by the query.

Table 16.1: Using Operators in the Criteria Pane

To Specify this Enter this Example interpretation

Equals = =West Return only the records with the value

West in the Criteria field.

Is not equal to <> <>North Return only the records that do not have

North in the Criteria field.

Table 16.1: Using Operators in the Criteria Pane (continued)

To Specify this

Is greater than

Is greater than or equal to

Is less than

Is less than or equal to

Is one of

Is between

Begins with

Ends with

Contains

Is null

Is not null

Enter this Example

Between

Like

Like

Like

Is Null

Between 5 and 10

Like A*

Like *A

Is Null

Is Not Null Is Not Null

Interpretation

Return only the records whose Criteria field value is greater than 100.

Return only the records whose Criteria field value is greater than or equal to 0.

Return only the records whose Criteria field value is less than 0.

Return only the records whose Criteria field value is less than or equal to 10.

Return only the records whose Criteria field value is East or West.

Return only the records whose Criteria field value is between 5 and 10.

Return only the records whose Criteria field value begins with A (or a).

Return only the records whose Criteria field value ends with A (or a).

Return only the records whose Criteria field value contains the string oo (or OO).

Return only the records whose Criteria field value is empty.

Return only the records whose Criteria field value is not empty.

Note that you can apply criteria to fields which are not included as part of the result set. Also, you can create complex criteria by using multiple fields as part of the criteria expression. Criteria listed in different columns but on the same row are considered an And condition. Criteria listed in the same column but on different rows indicate an Or condition.

NOTE The Not operator can be applied in front of any expression to return the opposite values. For example you could specify Not Between 5 and 10 to return only those records whose Criteria field value is not between 5 and 10.

In Figure 16.11, I've placed a filter on the ShipCountry field to limit the results to just those order records that were shipped to the USA. Note that the quotes around USA were automatically added by MS Query after I entered =USA.

Figure 16.11

Filtering the results returned using the Criteria pane of MS Query

Figure 16.11

Filtering the results returned using the Criteria pane of MS Query

Step Four: Set the Sort Order

You can return a result set that is sorted using one or more fields. Sorting is optional of course. You could just return the results to Excel in the default order that they came from the database. The default order depends on the underlying table structure of the table(s) used in the query.

To sort the result set on a single field, click in any record in the field you want sorted, and then click either the Ascending or Descending toolbar buttons depending on how you want it sorted.

To sort the result set on multiple fields, start by sorting on the most important field. Then, while holding down the Ctrl key, sort additional fields in order of importance. When you hold down the Ctrl key, MS Query preserves any existing sort orders.

You can also define a sort order by choosing Records ^ Sort from the MS Query menu. This displays the following Sort dialog box. For my query, I specified a sort on the LastName column as shown in the following screenshot.

Starting with the primary sort column, select the column to include in the sort, select Ascending or Descending, and then click Add.

Step Five: Return the Result Set to Excel

Once you're satisfied that the query is retrieving the data you want the way you want it, select File ^ Return Data to Microsoft Office Excel and then indicate where to place the data as shown in the following screenshot.

Impart Data

®

Where do you want to put the data?

1 OK 1

(*) Existing worksheet:

=5heet4!$A$5

[ Cancel |

o Mjew worksheet

Create a PivotTablc report,..

| Properties... | Parameters ,.

[ Edit Query... |

Note that you can also save the query (by choosing File ^ Save in MS Query) so that it can be used from other workbooks without going through all of the steps to set it up again. Any queries you save will show up on the Queries tab when you select Data ^ Import External Data ^ New Database Query in Excel.

Turbo Charge Your Data Range

While using MS Query to retrieve data is a powerful capability, you can add more horsepower to your solution by using parameter queries. A parameter query is a query that allows you to specify one or more of the criteria values each time the query is executed.

You want to see sales figures for the Midwest division? Fine, run the query and specify "Midwest" for the division criteria. What's that? You want to see sales for the Northwest instead? No problem, refresh the query and specify "Northwest" for the division criteria. Parameter queries allow you to develop this kind of "push-button" reporting in which your end users can easily get the data they are interested in with a few clicks of the mouse.

For the most part, parameter queries are created just like any other query using MS Query. The key difference is how you specify the criteria on the field that you would like to use as a parameter field. In Figure 16.11, I used the ShipCountry field to filter the result set so that only records shipped from the USA were included. This query can be easily converted to a parameter query by making the modification shown in Figure 16.12.

Rather than hard-code USA as a value, I've entered a prompt in brackets. When you execute this query, MS Query will prompt you for the value to use using the text you supplied in between the brackets.

Enter Parameter Value

m

Enle* a country

F~

1 « 1

Cancel |

A basic parameter query

Figure 16.12

A basic parameter query

Figure 16.13

The Parameters dialog box

Figure 16.13

The Parameters dialog box

Once you return the data to Excel, things get even more interesting. If you right-click anywhere in the external data range and choose Parameters, a dialog box similar to the one shown in Figure 16.13 will display.

In Figure 16.13, I've instructed the data range to retrieve the value to pass on as the parameter from cell B2. Also, if the cell's value changes, I've set it up to rerun the query with the new value. To really make this easy from a user's perspective, you could use the Data Validation feature on the cell used for the parameter value. In Figure 16.14, I've filled in the necessary Data Validation values. The end result (Figure 16.15) is an interactive worksheet that allows users to easily retrieve just the data they're interested in seeing. All without a single line of code!

Figure 16.14

Use Data Validation on the parameter cell.

Figure 16.14

Use Data Validation on the parameter cell.

Figure 16.15

MS Query + Parameter Query + Data Validation = Power

LJ Microsoft EKte

- Chapter 16 Examplesjtls

BS8

Iriîl] Fila Eiit V»bv Insert Format Tools Data Wrrdow

Help - 3 X

! -J

-1 .1 -J ..i :

ÈM 1

I £-ë; j ^

B2

f* Canada

A

B 1

tu

1

2

Country

Canada ]▼

3

Canada

5

LiistName

UK USA

ril'll Sales'

6

Cal:'ahari

Laura

1,276.40

7

Davolio

Nancy

e ¡601.42

a

Dodsworih

Anne

966.80

3

Fuller

Andrew

3,034.50

10

King

Robert

3715.56

11

Levdiling

Janet

12,155.73

12

Peacock

Margaret

4 ¡826.05

13

Suyarna

Michael

3,412.33

14

15

16

V

M <

> H j\ Sheet!

SheetS Xiheet3_\5heet4 A<

'I >r

[ Ready

0 0

Post a comment