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:
