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
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.
MICROSOFT QUERY 2016
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.
MICROSOFT QUERY 2011
To join two tables:
A line joining the two tables will
appear. Double-click the line to edit the join.
- Add two tables to the
top panel of MSQuery. (See screen shot)
- 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.
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.
LEFT OUTER JOIN Categories
ON Products.CategoryID = Categories.CategoryID