If you look closely at the results when you were able to Create Your First SQL Report in Excel, you see some columns with meaningful, but un-usable data. Columns like Customer ID, SalesPerson ID and TerriitoryID are needed, but who is Customer 29672? If you don’t have all your client numbers memorized (and who does), then you need to have Multiple Tables For Useful Information.
Depending on how the data is structured (and this is where our allies in Information Technology can help), you may even need more that one table to get the useful information. With Adventure Works, we need to use the Customer and Person tables to get the name of a customer for a sales report. These tables are said to be “related” and the database is a “Relational Database”.
Our non-technical readers might ask why, and we will tell you it is really to simplify your data and keep it clean. Imagine a scenario where a person changes their name as when my daughter got married. Instead of updating her last name in a person table, if the name was part of the SalesOrder Header (non-relational), you would have to update EVERY Sales record, not just the person, to have the new, correct information. This would be time consuming and prone to a lot of data entry error. With a relational table structure, you only have to update the name change in one place.
Many readers may be familiar with an Excel vlookup. With a vlookup, you could have a list of all of your sales order header data in one tab, customers in another and finally the persons in a third tab. Using the technique you used to Create Your First SQL Report In Excel, you should create two new tabs. One for Customer and on for Person. When you finish, you will have three tabs , or worksheets, in your Excel workbook. Remember to give meaningful names to each tab, table and query.
Now that the data is in your your workbook, you can create a couple of vlookups to display the last name of the customer. First you need to create a vlookup for the PersonID then for the PersonLastName. For Person ID, you’ll use the formula “=VLOOKUP([@CustomerID],Customer,2,FALSE)”
For PersonLastName, your formula is “=VLOOKUP([@PersonID],Person,5,FALSE)” and now you should see the last name of the person related to each sale.
While this works to connect Multiple Tables For Useful Information, it is not very efficient. The report requires you to have EVERY person and EVERY customer to be part of the excel workbook! For the AdventureWorks sample, that is not really that big. For any real database, that will be too big to manage. As you add features like a search into your reporting, you’ll find it a lot hard (and slower) as well.
So, why did we do this and what can you do? Well, the why is so you can understand how tables logically connect together. The what can we do is coming next. You didn’t think this was then was the only way to link Multiple Tables For Useful Information… did you?