Using SQL In Access
If you already know SQL you probably feel constrained by the Query Builder in
Access. But you don’t have to be. Open a new query in Access and when you’re
asked to select the tables your query will be based upon, just close the window.
You will notice at this point that the toggle button which usually shows
Design View or Datasheet View now shows SQL. Feel free to type SQL directly into
this window. If you already have a query, you can still open the SQL view -- it
is always available.
click the image for a video of this functionality
The SQL view is important because there are some types of queries which you
cannot build graphically. It leads many of us to assume that they cannot be run
in Access. This is incorrect. One example is the UNION query. As you know, a
JOIN combines tables vertically. In other words, you choose columns from one
table and combine them with columns from another table. The UNION query combines
rows from one table with rows from another table. For an example see the
Northwind database
installed with Microsoft Office under the query
Customers and Suppliers by City.
Another powerful tool is the Pass-Through query. If you need data from a SQL
Server database or an Oracle database and it needs to come from a stored
procedure, you can use the Pass-Through query to call the stored procedure on
the server. Access doesn’t understand stored procedures, but it can hand off the
name to a server that does and receive the recordset that is generated.
It can be difficult to find other SQL specific keywords like DISTINCT or TOP.
These can be found by a right clicking in the query window and choosing Query
Properties as shown below.
click the image for a video of this functionality
If you want to use the SQL keyword SELECT DISTINCTROW, you would choose
unique records and set the value to yes. For the SQL keyword SELECT DISTINCT you
would choose unique values and choose yes. For TOP values or percentage you
would choose Top Values and choose either a number or a percentage.
See the Tips and Tricks
section of the I Get It! Development website for a video demonstrating how to
use this functionality.
Whether you type your SQL, or use the query builder to add in SQL for you,
remember that you do have a much more complete library of SQL to use in Access.