Access Queries-Adding Power with SQL.

So you've mastered using the Access graphical query wizard. That's not enough for you-perhaps you want to build dynamic queries in another langugage that are then used to retrieve data from Microsoft Access databases. You might want to use Access to execute queries on tables linked to another database engine, such as DB2 or SQL Server, using SQL keywords native to that platform. You may need to merge together two similar but distinct queries into a single returned set of results. For these tasks and more you can the mighty SQL.

What is Access SQL?

SQL stands for Structured Query Language.SQL is a language for interacting databases and it was conceived by IBM in the 1970's. Over time, it grew into an industry standard and the SQL syntax is now managed by the American National Standards Institude (ANSI). Microsoft Access supports ANSI SQL, along with some neat additional keywords that are not part of the standard.

SQL is tailored specifically for manipulating sets of data and it works really well for updating and retrieving data from a relational database such as Access. SQL is a little more like a human language than C or Basic in that commands to manipulate data in the database are issued in a sentence-like format.

Because much of Access SQL is part of the ANSI standard, once you become familiar with Access SQL, you'll be well on your way to knowing how to query data from larger, more capable database systems such as Oracle, SQL Server, or IBM's DB2.

Getting Started

Any query that you can construct in the Access Query Designer can be viewed in SQL form by clicking on the query view selector on the upper left side of the screen and choosing "SQL View" from the drop-down list that appears there. Constructing your query using the Access Design View and then using the SQL View to see the corresponding SQL statement is a pretty straightforward and easy way to begin familiarizing yourself with SQL. Of course, you can also do the opposite: Write a SQL statement and then paste it into the SQL View, then change to the Design View to see a graphical representation of what the SQL statement does.

The Basics

In standard SQL there are four things that you can do. You can:
1) Retrieve columns of data from a table or tables 2) Insert new rows of data into a table 3) Update columns of data already existing in a table 4) Delete rows of data from a database.

Other kinds of tasks can be performed using Access SQL, such as creating indexes, creating new tables, and or adding columns to existing tables. These are not part of standard SQL and though most databases support them the exact syntax of these commands varies from database to database. We'll cover only the SELECT statement here.

Retrieving Data from a Table

Let's start with a Customer table as shown below.

To retrieve data from an existing database table, use the SELECT command. In its simplest form, the SELECT command is followed by a list of the columns that you would like returned, the word FROM, and the name of the table where the data resides. For example:

This SQL command would return the first and last name from each row in the Customers table.

Alternatively, you can use an asterisk ("*") to return the values of all columns in the table.

Executing this query will return the same rows as the previous query, but will also return all columns.

Let's say you wish to see only those customers that live in North Carolina. To restrict the results of your query to only those rows that match certain criteria, use the WHERE clause. WHERE always follows the FROM clause. If our previous example query were modified to return only those customers from North Carolina, it might look like this:

Based on our Customer table example, the results returned by the query would look like this:

Multiple conditions can be included in a WHERE clause by separating them with AND or OR logical operators. Parentheses can be used to control the order of evaluation of the elements in a WHERE clause.

By default, rows returned by a SELECT statement will be ordered by whatever the primary index of the table is. If you wish the rows to be returned in a specific order, you can include an ORDER BY clause. The ORDER BY clause is always the last part of the SQL statement. In our example, if we wished to sort our North Carolina customers alphabetically by their last names, then we would use this SQL statement:

An ORDER BY clause can be used to sort on more than one column-just separate each element in the sort order with a comma.

We can see that the results of the query are now sorted by last name.

Following a column name with DESC causes the column to be sorted in descending order.

Retrieving Data, Part II

Part of SQL's power is that it can do calculations on sets of data. A query that does such calculations is called an aggregate query and uses a slightly different format than the straight-forward, plain-vanilla SELECT query. To create an aggregate query using the Access Query Designer, you would choose "View Totals" from the View menu, and then choose an aggregate function from the row in the query designer titled "totals".

An aggregate query is one that includes one or more aggregate functions. Some of the more commonly used aggregate functions are listed here:

SUM	Calculates the sum of values contained in a specified column
AVG	Calculates the average of values contained in a specified column
COUNT	Calculates the count of rows returned by the query. You can also calculate a list of unique values contained in a column using the COUNT function by including the DISTINCT modifier, as in COUNT(DISTINCT mycolumn)
MIN or MAX	Calculates the minimum or maximum value contained in a column.

To use an aggregate function, you must first place the function in after the SELECT statement. So to count how many customers are in the Customer table you could use this SQL:

Executing this query will return a resultset similar to the following:

If you wish to subtotal aggregate calculations, or "group" them by a column value, you need to include that column both in the list of columns following the SELECT statement, and also following in a separate GROUP BY clause that should be placed just before the ORDER BY clause (if one exists). For example, to see how many customers we have in each state, we would use this grouped aggregate query:

When executed, our resultset would look like this.

Note that if you wish to order by a column, that column must appear in your group by clause.

When you use a WHERE clause with an aggregate function, the WHERE clause is applied before the function is. This means that you can not include aggregate functions in a WHERE clause. If you wish to restrict the rows returned by the query based on the results of an aggregate query, you need to use the HAVING clause.

The HAVING clause follows the GROUP BY clause and includes an aggregate function and a condition. So, if using our Customer table, if we wished to list only those states with more than three customers, we would need to use a SELECT statement with a HAVING clause similar to this statement:

There is only one state in our sample table for which we have more than two customer records, so our query will return this result.

Get It Together, or Joining Tables with SQL

Relational databases are named so because data in one table can relate to that in another. We've been using the Customers table for our examples up until now, and let's assume that in the same database there is also an Invoices table that contains a list of all of the invoices that we have sent out. Let's also assume that in that Invoices table there is a CustomerID column that indentifies the customer to whom the invoice has been sent.

If we wanted to list the count of invoices for each of our customers then, we could use this aggregate query:

This would return this list of CustomerIDs and the count of invoices for each customer.

But what if we wanted to have the customer's first and last name returned, in addition to their CustomerID number?

Since the customer's first and last names are stored in a different table, the Customer table, we need to use another SQL command, the JOIN command. The JOIN command is used to specify how the tables in a SQL statement that contains more than one table should relate, and it is included between the table names as part of the FROM clause.

There are three types of JOINs. We'll start here with the simplest of the three, the INNER JOIN. The INNER JOIN is what you end up with when you use the Access Query Designer to connect columns in two different tables and choose option 1, "include rows where the joined fields from both tables are equal". This is the most common type of join and the type of join that we would use to include first and last name in our count of invoices by customer. With the INNER JOIN statement in place, our SQL statement would look like this:

When we execute the query, we get a resultset containing a count of invoices per customer, and each customer's first and last name.

The two other types of JOINs are LEFT JOINs and RIGHT JOINs. Left and right joins are similar. Both include all of the values in one table and only those that match on the joining column, from the other table. The difference between left and right joins is only that using a left join includes all rows from the table on the left and only matching rows from the right table and the right join includes all rows from the table on the right and only matching rows from the table on the left side of the join.

You might use a left or right join if you knew that one of the tables that you intended to join did not have matches for each of the values in the other's joining column. Let's say that some of our customers are new customers, who have not yet purchased anything from us. We can use a LEFT JOIN to get a list of the count of invoices for each of our customers, even those who don't yet have any invoices.

The syntax for a LEFT JOIN is very similar to an INNER JOIN. In fact, our previous query, modified to include invoice-less customers, would look like this:

I've included Invoices.InvoiceID field inside the COUNT aggregate function because we wish to return the count of invoices.

I could also have included an asterisk (*) instead, like this:

Doing so, however, would have counted the customer record as a row. You can see that while in the result above, Bob Smith is listed as having a count of zero, below he is listed as having a count of one.

The difference here is that with the Invoices.InvoiceID field specified, the value of the COUNT function would return zero when there are no invoices for a customer, but an asterisk, the COUNT function would return a value of one for customers with no invoices.

Access Joining Weirdness

If you use the Access Query Designer to rough out your SQL statements you will notice that Access includes parentheses, sometimes lots of them, around your JOIN clauses. This can look intimdating. Don't worry. Your queries do not need those parentheses and will continue to work without them.

The same is true for the extra parentheses that Access sometimes places around the conditions in WHERE and HAVING clauses. These too can be safely removed.

Access also terminates all SQL queries that it generates with a semicolon. This semicolon is not necessary and Access will correctly interpret queries that do not end in a semicolon.

Esoteria

If, while in the Access Query Designer, you click on the Query menu, and on SQL Specific, you'll notice that on the resulting fly-out menu there are three options for three types of queries that can not be constructed using the graphical query designer.

Unions

The first of these types of queries is the UNION query. A UNION query is a form of JOIN-that is, it's a way to merge the data from multiple tables into a single returned result. The difference between a JOIN and a UNION is that a JOIN merges tables horizontally, while a UNION does combines them vertically.

UNION queries can be tricky. All individual SELECT statements in a UNION query must have the columns of the same type in the same order. If one SELECT statement has fewer columns than the other, use the NULL keyword as a placeholder for the missing columns.

Also, only column names specified in the top-most SELECT statement can be included in a UNION query's ORDER BY clause.

A UNION query might be used when you wish to combine data from two unlike sources into a single query. Let's say that around Christmas time of every year your company sends Christmas cards both to customers and to employees. A month before Christmas you must provide the printer with a list of names that will be printed on the cards.

Let's assume our employee table contains these records:

You might use a UNION query like this:

This UNION query would return a single list, containing both the names of customers and employees, and sorted last name first.

Pass-Through Queries

Essentially, a pass-through query is a way to send a SQL statement to another database to which Access has been linked via its linked tables functionality. More powerful databases such as SQL Server and Oracle support SQL commands that Access does not, and this provides a way to execute SQL statements containing those commands against data in those host databases. Access does not try to interpret or parse pass-through queries, so parameters cannot be included in them. This can be a tough limitation to work around if you have the need to do so.

Data Definition Queries

Data Definition Queries are used to manipulate the underlying structure of the Access database. If you are using SQL to query the Access database from an application that you've written, you might use Data Defintion Queries to create new tables or indexes, or to add columns to existing tables.

Some Final Tips

SELECT TOP n and SELECT TOP n PERCENT
If you do not wish to retrieve all of the rows that match your query criteria, you can make use of a couple of handy modifiers that Access provides for use with the SELECT statement. Use the TOP n modifer to return only a designated count of rows. TOP n PERCENT returns the the first n percent of rows that match your query.

WHERE x in (n1,n2)
Use the IN operator if you wish to find that where a column matches one of two or more non-contingous values. The values in the list can be either numeric or alpha.

Comparing dates

When including dates in SQL WHERE statements, it's best to use the Access time/date functions. For example, if you wish to return all invoices created in the last 7 days, use the Access DATEDIFF function as follows:

Some of the abbreviations that can be used with the Access DATEDIFF function are: "yy" or "yyyy" for year, "mm" or "m" for month, "dd" or "d" for day, "hh" for hour, "n" for minute, and "s" for second.

When including a date in a query, you must delimit it with pound signs ("#") like this:

VBA and Access SQL

Most VBA functions, such as MID, INSTR, CINT, etc. can be included in Access SQL statements. Moreover, if you are comfortable writing functions using VBA, then you can write them and include them in queries.

Access SQL is quite powerful and we've just scratched the surface here. Much more information about the capabilities of Access SQL can be found reading the Microsoft Jet SQL Reference topic in the Access online help.

Happy querying!