|
Excel provides the ability to bring data that is "external" to the
current workbook into your workbook. The examples shown
here demonstrate how to include data stored in an Access database into a
workbook and manipulate that data in various ways. You can, however, bring
data from any data source that has ODBC capabilities. For example, many accounting
software packages provide ODBC files with which you can query your accounting
data into Excel.
Under the Data menu, there is a "Get External Data" option. This is
called "Import External Data" in versions starting with Excel 2002.
To start the process of querying an external data source, choose the New
Database Query option. The Choose Data Source dialog appears and is shown
below. Also note that Microsoft Query has started and shows in your
Taskbar. Microsoft Query is the program that Excel uses to query and
return the external data.
Note that MS Access Database has been selected. As these examples will
be using the Northwind Sample database that ships with Access, that is the
option we want. Also note there is a checkbox at the bottom of the dialog
that allows you to use the Query Wizard. If you are particularly adept at
writing SQL or you are creating a complex query, you may want to uncheck this
box and create the query manually in MS Query. I generally use the wizard
even if I know that I will be modifying the query in MS Query later. With
MS Access Database selected, click on the OK button. You will be presented
with a standard file navigation dialog that allows you to select the database
you would like to query.
Navigate to Northwind.mdb and click OK.
This starts the query wizard and shows the first page, Choose Columns.
The left listbox shows all the tables and queries in the database.
Unfortunately, it doesn't show which are tables and which are queries. I
advocate a naming convention when using Access where tables start with Tbl and
queries start with Qry, but that's just me.
For this example, choose the Customers table by clicking on the plus sign next
to it. This will expand that table to show the available fields. To
include a field in your query, select it and then click the greater-than-sign
button (>). The selected field will be shown in the right listbox.
Double clicking the field name works also.
Select the CustomerID, Company Name and City. Your dialog box should
look like this.
If it does, click Next to show the Filter Data dialog.
The Filter Data dialog allows you to set up criteria for your query. If
you know SQL, you're writing the WHERE clause. Setting up criteria is
simply limiting the records returned by your query. Let's limit this query
to only those records whose City is equal to Berlin. To do this, select
City in the left hand listbox, select equals in the first combobox and Berlin in
the second. Like this:
You can set criteria for any or all of the fields and you can set multiple
criteria for the same fields. If you use And's and Or's, you may not get
the results you want. Generally, the more complicated your criteria, the
better off you are doing it manually in MSQuery. If your screen looks as
above, click Next to show the Sort Order dialog.
The Sort Order dialog is pretty straight forward. You can sort by up to
three fields in ascending or descending order. If you don't choose any
field, the sort order will be whatever order the underlying table or query has.
Choose to sort by CompanyName in Descending order so that your screen looks like
this:
Click Next to show the Finish dialog.
As its name suggests, the Finish dialog is the last step in the process.
You'll notice that you have three radio button options. You can return the
results of the query to Excel, edit the query further in MSQuery, or create an
OLAP cube. OLAP cubes are beyond the scope of this article, so we'll focus
on the first two. Here's what your dialog should look like:
Click Finish to return the data to Excel. Excel will again be in focus
and will show the Returning External Data to Microsoft Excel dialog box, shown
here.
The default is to return the data to the existing (active) worksheet at
whichever cell was active when you started the process. The "marching
ants" are circling the cell shown in the refedit control (G7, in this case).
Change it to A1, and click OK. You can change the query table properties
via the Properties button, but it's just as easy to change them once the data is
in Excel. Now your spreadsheet will look like this:
That's right, one whole record. Woo hoo! That tells us that the
Customers table in the Northwind database has one record whose City is Berlin.
|
