In our previous segments, we found we need to link Multiple Tables For Useful Information and using vlookups with entire tables is easy, but inefficient. Not to worry, it’s very easy to use SQL Joins for Better Table Links! Don’t let the term scare you, a SQL Join is just telling SQ that two (or more) tables are logically linked together… “joined” at the hip. Using MS Query, you don’t even need to know any special syntax… you just need to know how to use a mouse!
To get started, go back to the original SalesOderHeader report where you learned to Create Your First SQL Report In Excel. We will edit this query to add and link to the tables for customer and person. In the report you need to click into the “Data” ribbon, then click the small triangle under the refresh all and then “Connection Properties”. In connection properties, cick “Definition” then “Edit Query”.
Now comes the good stuff…
You are in MS Query, one of the most underrated and underpublicized tools within Excel / Windows. You saw the earlier magic when you created a report, but now we need to get a bit more sophisticated. We need to add the two tables, “Customer” and “Person” to the query and then link them together. Search though the list, then “double click” Customer then Person.
The two new tables will appear, and MS Query will even “guess” the link for “SalesOrderHeader” to “Customer”.
I like to expand the boxes for each table to make them more visible. Next “drag” a link from the “Customer” table from the field “Person ID” to “Business Entity ID”. And then double click FirstName then LastName, and finally the “Query Now” exclamation point icon. You should see the information in the query window that looks like this…
Close the window and the information will be in Excel!
From here, it is just a matter of cleanup. Delete the two vlookup columns you created for PersonID and PersonLastName. You also can delete the tabs you created for Customer and Person as they are not needed anymore. For a bonus, we can get back to the query and a few more tables.
Sales Territory (for TerritoryName), Address and StateProvidence so we can get the shipping location. Add the tables as you did the others and link the tables with the same drag and drop technique. If a link / line shows up you don’t want, click on it and then click the delete key.
Finally, you’ll want to rename a couple of columns, which you need to do here… not back in the spreadsheet. Double-click the heading for “name” where you had the territory and rename it “TerritoryName” then Name for the StateProvnce. When done, it should look something like this…
Close the window and the results will be in your spreadsheet!
Hopefully you see how quick and easy you can start grabbing information and pull it straight into Excel.
Next we’ll use Pivot tables to Build Intelligence into your Business Data!