Beyond Excel: VBA and Database Manipulation

Sep 22 2009   9:06AM GMT

Creating a Query



Posted by: Craig Hatmaker
Tags:
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.

MSQuery

MSQuery

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:
Location: http://support.microsoft.com/kb/824265
Download: http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=EN
Useful Information: http://www.wilsonmar.com/northwind.htm

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: