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 10 Queries - Filtering records with SQL WHERE clause

This is example syntax for using a WHERE clause
SELECT Employees.* FROM Employees WHERE (Employees.Title = 'Sales Representative')

MICROSOFT QUERY 2016
You must paste or type queries in Microsoft Query. There is no interface to help you with query syntax.
Click Here to vote for the restoration of the Microsoft Query graphical user interface
Click Here to vote for support for advanced query options afforded by the Data Model (which is missing from Excel for Mac)
Tip: You can paste queries made in Microsoft Query for Windows into Microsoft Query on the Mac and they will work, even if they can't be displayed in Query View on the Mac.

MICROSOFT QUERY 2011
The middle panel of Microsoft Query has controls that specify which fields will be in the result set and what criteria will be used to filter the data within those fields. This example shows how to use SQL to filter your database to deliver records that contain the phrase “Sales Representative” from the Employees table of ExampleDatabase.xls.

If you type or paste the following SQL text example (above) into the SQL View and switch to Query View and then click the TEST button, MSQuery will attempt to execute the SQL statement.
Experiment by making adjustments to your query in Query View.

In Query View you can
Switch back and forth between Query View and SQL View to learn the syntax that MSQuery uses for SQL queries. Note especially the use of parenthesis and single quotation marks. Notice the difference in the result set when the Show checkbox is checked under the filter and when it is not (an extra column at the end of the data set is the difference).


Filter SQL query

Filters use logical operators. Heres' one of many lists of SQL Lite operators:
http://docs.oracle.com/html/A95915_01/sqopr.htm

Special notice to users of Oracle database sources:

Here's a tip from Actual Technologies:
Unlike the Windows version of MS Query, the Mac version does not automatically prepend the schema / user name to the table name when creating a SQL query.  Instead, you will need to manually edit the query in the "SQL View" to add the user name to the query.  This is only required when the table belongs to a different user.

You would use the "Query View" to graphically created your query in MS Query.  Then, press the "SQL View" to edit the SQL manually.  For example, if you had the following SQL:

SELECT CONTACTS.CONTACT_ID, CONTACTS.CONTACT_NAME FROM CONTACTS

If the tables belong to the schema / user named "ACCOUNTING", you would modify your query like this:

SELECT CONTACTS.CONTACT_ID, CONTACTS.CONTACT_NAME FROM ACCOUNTING.CONTACTS

At this point, you will not be able to switch back to the "Query View", but you will be able to test your query and return your results to Excel.
Part 1
Part 9 (Previous)
Part 11 (Next)