Microsoft Access for Beginners
- Additional Information
Understanding SQL - The Basics
In the chapter on queries,
I briefly wrote about Structured Query Language (SQL) which Microsoft Access and
other relational databases use to select and manipulate information. SQL (often
pronounced "sequel") is a scripting language resembling English that is used to
relay instructions to the database and specify exactly what is to be done with the
data. Many Access users may think of a query as an object within the database
window that can be run to retrieve data but the query is actually the SQL code within
this object. The Query Builder window that most Access users are familiar
with is simply an interface that makes it easier to select tables and fields and
specify what the query should do. As you use this interface, Access writes
the SQL code, referred to as a statement, in the background
and saves it when you click the save button on the Query Builder interface.
In addition to these standalone queries, SQL statements can be directly used as
the data source for forms, reports and controls within Access. SQL statements
can also be issued from macros and within VBA code in order to automate operations
on data. If you move beyond Access to databases like SQL Server, you will
also see that SQL statements can be run from a command line to affect data being
stored in a network database. The following are examples of queries that you
could actually run on the demonstration database and you might find
them self-explanatory.
SELECT * FROM tblPersonnel WHERE LastName = "Ashford"
UPDATE tblDepartments SET DeptName = "Production" WHERE DeptNo = 014
DELETE * FROM tblAbsences WHERE EmpKey = 8
(The asterisk [*] is a wildcard symbol used to indicate all records matching the
criteria in the statement.)
Many Access users will rely on the Query Builder interface to construct queries
as needed. While this may be all that you need when using Access, it's important
to understand the basics of SQL if you actually want to understand how your database
works. Especially if you use other database systems, you will be much more
efficient when it comes to retrieving and analyzing data if you can write at least
basic queries rather than depending on whatever interface might be available at
the time. Despite it's appearance, SQL is a very simple language that you
can learn the basics of in less than a day so there's really no reason to avoid
it.
Behind the Scenes
By selecting SQL View instead of Datasheet view in the Query Builder, you can view
and edit the SQL statement directly. The following screenshots show a SELECT
query on two of the tables in the demonstration database that displays records for
employees on the first shift who have more than one absence recorded.
At first, the SQL view looks like a confusing mess until you focus on the keywords
that I've highlighted in red in the above picture. Notice that all of these
keywords are followed by table and field names. Each of these keywords corresponds
in some way to a part of the Query Builder window.
Access gets a little wordy when it writes the SQL so here's a simplified version
of the query:
When processing the SQL statement, the program ignores extra whitespace and lines
in the statement. I formatted the below statement as I usually do for readability
but I could also put everything in one continuous line or every word on a separate
line and Access would read it just as easily. I've also removed the table
names from the code below to simplify things. Specifying the table names before
the field names often isn't necessary in queries unless you have the same field
name in more than one table.
SELECT Employee, LastName, FirstName, Count(AbsDate)
FROM tblPersonnel
LEFT JOIN tblAbsences
ON tblPersonnel.EmpKey = tblAbsences.EmpKey
WHERE Shift = "1"
GROUP BY Employee, LastName, FirstName
HAVING Count(tblAbsences.AbsDate) > 1
ORDER BY LastName;
Let's take a look at the individual parts:
- SELECT / FROM - First, the query specifies the fields to be displayed.
It lists the fields that are to be shown in the order they are placed in the grid
and separates them with commas. Notice that the Shift field is not listed
as its Show checkbox is cleared in the Query Builder. It's only being used
in the query to provide criteria to limit the records returned.
- JOIN ... ON - SQL uses JOIN clauses to refer to the relationships between
tables. In this case, it means that all of the records from the table on the left
side of the relationship (tblPersonnel) are available to the query while only the
corresponding records from the right side of the relationship (tblAbsences) are
available. This join displays all employees, including those who have no records
in tblAbsences. This is also referred to as a LEFT OUTER JOIN. An INNER JOIN
would only show employees who had records in both tables. The ON keyword specifies
the fields on which the tables are to be joined. The use of the words LEFT and RIGHT
do not refer to the tables' placement in the QBE window but rather on their order
in the relationship. In this case, the value of the EmpKey field is supplied
by tblPersonnel on the left side of the relationship and tblAddresses uses a given
value to link an address record to an employee record.
- WHERE - This clause specifies the conditions to be met by the data. It specifies
criteria that each row must meet in order to be included in the results. In this
example, we're looking at only first shift employees.
- GROUP BY - Group By is used to create groupings within the data based on
specific fields. This query is grouping by the fields in the query that identify
the employee, they are listed here in the order shown on the design grid.
The AbsDate and Shift fields are not included as they are either not part of the
grouping or are not shown.
- HAVING - HAVING is used to specify criteria for a group defined by the GROUP
BY clause and cannot be used unless GROUP BY is present. The difference between
WHERE and HAVING is that WHERE is applied to individual records before the query
performs any grouping. HAVING is applied after the grouping is done and is
referred to by Access as an aggregate function.
In this case, the group defined by employee must have more than one record
in tblAbsences.
- ORDER BY - The final keyword lists any fields used to sort the query results,
in this case the last name. Again, you can sort by multiple fields and the
results will be sorted according to the order of the sorted fields in the SQL code.
The final results of the query.
Note that the semi-colon (;) at the end of the Order By clause is not part of that
clause but actually indicates the end of the SQL statement. This indicator
is optional but if you include it and then continue the statement after it, you
will not be able to run the query.
SQL contains many other statements, commands, operators and functions but the ones
listed above will be enough to keep you from panicking when you view the SQL for
a query.
For more information:
Access SQL: Basic Concepts, Vocabulary and Syntax
- From Microsoft Office Online, a series of articles on using SQL to work with data
in Access.
10 Tips for Using Wildcard Characters in Microsoft Access
Criteria Expressions - From TechRepublic by Susan Harkins
Sams Teach Yourself SQL in 10 Minutes (3rd
Edition)
(Available on Amazon.com)