Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 11 - Queries - Relational database joins

The ability to use SQL to join tables in Microsoft Query makes Microsoft Office 2011 a relational database tool when you use an Excel (.xls) workbook as a data source.

Paste or type a query having a JOIN clause (see example below). There is no graphical user interface for queries in 2016. Click here to vote for restoration of the Query builder.

To join two tables:
  1. Add two tables to the top panel of MSQuery. (See screen shot)
  2. Drag a field name that both tables have in common from one table into the other. MSQuery may prompt you to verify the field names if it can not determine matched fields automatically. The data types of the two fields must be identical.
A line joining the two tables will appear. Double-click the line to edit the join.

Be advised that it is possible to easily make queries that produce very large number of results that take a long time to return, or even a runaway query that will never end. The Macintosh version of Microsoft Query does not have a time-out feature. You may have to click on the Microsoft Query dock icon to Force Quit Microsoft Query if a query is taking too long. Here is the syntax for a left outer join and what it looks like in Query View.

SELECT Products.ProductName, Categories.CategoryName

FROM Products

            LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID

SQL join dialog

Part 1
Part 10 Part 12 (Next)