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

The Excel file can be found JoinTables II.xlsm

I will show you here how to join the Orders and Customers Table

1) Create a new Spreadsheet and call it ReportJoin

2) Data -> From Other Sources -> ExlJoinTablesNoDSN

3) Select just one field of the Customers Table

4) Go to the end of the wizard and Select "View Data in MS Query"

5) Go To Table Add...
6) Add the Orders Table
7) Remove the ID column
8) Drag a relationship line from Customers.ID to Orders.Customer ID.
9) Add individually all the fields you like form the two table
10) Click Return Data to MS Excel

Congratulation you are done!

You have just connected to MS Excel as it was a relational DB, and you have used SQL to join the two table!!!

As final step let me call this table TblJoinOrdersAndCustomers, by now you should now that you can do it by going to the Name Manager

A caveat of this approach comes from the connection to the Excel file. This connection, whether you decided to do it DSN or DSN connectionless, points directly to the location of the file, as you can see from this screen-shot in the Connectiong String text box.

So as you move your file to another location, this solution will not work anymore.

Just to be more clear:

If you have any Connection that point to a file path, as you move this file, the connection will break.

If it is a DSN connection, you just need to go to the Control Panel and change the property of this connection so that it points to the new file location.

If it is a DSN connectionless connection, you need to go the the Table External Data Property Definition Dialog box and change the connection path.

In the next post I will show you how to sort out this problem when you are connecting the Excel file without a DSN connection. I will assume that you use the current excel file as the external data source for you query, so that if you happen to move it, the file will keep working fine.