Excel Tip of the Day: How to use Excel as a Relational Database Part I

Here you can find the Excel Workbook for the tutorial JoinTable.xlms

I am going to show you how to use Excel as a Relational database. This is quite an advanced topic, so I will assume that a user is already familiar with Excel 2007 table, names as pointer to functions, VBA and how to access to external data source. I will cover each of this topic in details in my post, but I will assumer them as known in this one in order not to loose focus and keep the post shorter.
What you need: Excel 2008, Access 2007 and the Northwind database sample.
In this post I will show you how to connect to an Excel Spreadsheet and query it with MS Query, so I will show you how to deal with Excel as it was a relation data source.
The interesting part is that I will show you how to query an Excel Workbook form itself.

Create the Workbook and DSN Connnection

1) Create a new Workbook and name it JoinTables, save it as .xlms, a Macro enabled spreadsheet

2) Create a DSN connection to the Northwind data base (see here ) as name it NorthwindDSN


 Create the Orders Query

1) Rename Sheet1 to Orders

2) Go to Data -> From Other Sources -> From MS Query

3) Select the NorthwindDSN Source

4) Go ahead and import the Orders Table in Orders Sheet cell A5.
    Select the following fields: Order ID, Custormer ID, Order Date, Shipped Date, Ship Name, Ship Address, Ship City, Ship State/Provicne, Ship Zip/Postal Code, Ship Country/Region, Payment Type



This is what you should end up with in your Orders Spreadsheet

We need now to change this Table Name "Table_Query_From_NorthwindDSN" in a more meaningful name.
1) Go to Formula -> Name Manager
2) Select Table_Query_From_NorthwindDSN
3) Click Edit
4) Change it name to TblOrders
5) Click Ok, Close

You have now a Name called TblOrders that we can use to refer to the Table Orders.


Create the Customer Query


1) Rename Sheet2 to Customer

4) Go to Data -> From Other Sources -> From MS Query

5) Select the NorthwindDSN Source

6) Go ahead and import the Customer Table in Customers Sheet cell A5.
    Select the following fields:ID, Company, Last Name, First Name, Email Address, Business Phone

We have now two table connected with each other with the custormer ID field.
The customer ID field act as primary key in the customer table, and as foreign key in the Orders Table.
Our target is to join the two table together using MS query.
Before going ahead I would like to point out tha we can have 3 different case

1) Orders and Customers Table come form a Data Base

2) Order il a Spreadsheet Table, Customers is a DB Table (and viceversa)

3) Orders and Customer are both Excel Table

To see if there is any difference in the solution I suggest we are going to trasform the Customer Table in a local SpreadSheet Table.

1) Right click on the Customers  Table
2) Table -> Convert To Range -> OK

This way we do not have any more the Customer Table connected to the Northwind DB.

3) Click on the Table
4) Insert ->  Table -> Ok

We have name a spread sheet Table that will be called Table3. Again let's change it to a better name

1) Go to Formula -> Name Manager
2) Select Table3

3) Click Edit
4) Change it name to TblCustomers
5) Click Ok, Close

 If you go to the Names Combo box, you will see that we have tow Names: TblCustomers, TblOrders with workbook scope.
If you are using Excel 2003, it is worth point out that
1) The table Orders will have a scope limited to the worksheet, so to make it global you will have to create another name that points to it
2) The equivalent in Excel 2003 of a Table is called "List", so you need to create a List, rather than insert a table

I will cover both of this two topics in more details in my later post.
It is time now to move to part two.