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
Drag tables to your query from
the Tables window.
Drag field names from tables
placed in the top pane to the middle pane to include them
in the result set.
Uncheck the SHOW check box to
have a filter on a column that is not returned in the
result set.
Join tables by dragging field
names from one table to another in the top pane of Query
View. See Part 11.
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).
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.
