Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
So far we’ve discussed MS Query and its uses. Today we’re going to see just how easy it is to use in 9 simple steps.
Step 1 – Find Data
You probably have a copy of a sample MS Access database called “Northwind” on your PC. We will use it for this exercise. The most fool proof method for finding that database is to use “search” to find “*.mdb” (Prior to Office 2007) or “*.accdb” (Office 2007 version) starting in C:\ and including all subdirectories. If “Search” doesn’t find it, download Northwind from Microsoft (See references below).
Step 2 – Launch MS Query
From XL’s Office 2007 menu: Click Data > From Other Sources > From Microsoft Query
Prior to Office 2007: Click Data > Get External Data (or Import External Data) > New Database Query
Step 3 – Select Database
Make sure “Use the Query Wizard to create/edit queries” is unchecked. Choose “MS Access Database” as your Data Source and click “OK”. Select the Northwind database from the location you identified in step 1.
Step 4 – Add Tables
Double click “Orders” and “Customers” to add the tables to MS Query then close the “Add Tables” dialog. To remove a table from MS Query click on a table and hit delete. To add tables click the “Add Tables” icon (grid with a plus sign) or from the menu select Tables > Add Tables.
Step 5 – Create Joins
In some versions of MS Query, obvious joins are created for you. In our example we need the “ID” field/column from the “Customers” table to be linked to the “Customer ID” field/column in the “Orders” table. To do this, simply click and drag either field to its match in the other table.
Step 6 – Select Fields/Columns
From the “Orders” table double click “Order ID”, “Customer ID”, and “Order Date”. From the “Customers” table double click “First Name”. (You don’t need every field in the picture).
Step 7 – Filter Results
From the menu select Criteria > Add Criteria
Select Field: “Customers.State/Province”; Operator: “equals”; and type “CA” into Value:
Click Add and Close.
Step 8 – View SQL
This isn’t necessary at this point, but I want you to see the SQL MS Query created for you. If you’re new to SQL, this is a great way to overcome any anxiety you might have about learning SQL as you can always use MS Query to build simple SQL statements for you (we will be using them exclusively later on). If you’re an SQL veteran you probably find the code verbose and ugly. You can change the SQL here if you like.
Step 9 – Return Results to XL
Click the “Return to XL” icon (open door with an arrow) or from the menu select: File > Return data to Microsoft Excel.
Congratulations! You’ve just put your data into the most widely accepted, most widely loved, and one of the most powerful database analytical tools available.
References: Northwind Sample Database:
Useful Information: http://www.wilsonmar.com/northwind.htm