Microsoft Access for Beginners - Additional Information
Understanding SQL - The Basics
You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!
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 By Example (QBE) 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 an SQL statement, in the background and saves it when you click the save button in the query designer.
In addition to these standalone queries, SQL statements can be used as the data source for forms, reports and controls within Microsoft 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 Shift = 1;
UPDATE tblDepartments SET DeptName = "Procurement" WHERE DeptNo = 4;
DELETE FROM tblPersonnel WHERE EmpKey = 15;
(The asterisk [*] is a wildcard symbol used to indicate all columns in record source. It is not required for DELETE queries in Microsoft Access.)
Many Access users will rely on the QBE interface to construct queries as needed. While this may be all that you need when designing applications, it's important to understand the basics of SQL if you actually want to understand how your database works. 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, especially if you use other database systems. Despite its appearance, SQL is a very simple language that you can learn the basics of with a day or two of practice 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 shown in all caps 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.
When processing the SQL statement, the program ignores extra white space 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, even though you’ll see it further down in the query. In the QBE window, the Show checkbox for the field would be cleared as 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. These are much the same as the relationships that are defined between tables in the Relationships Window. 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, in this case the LeadID field. The corresponding fields on which two tables are joined must be of the same data type in order to permit a join.
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 because the EmpKey field is the primary key for that table. tblAbsences uses a given value to link an address record to an employee record and is therefore on the right side of the relationship.
Thinking of LEFT and RIGHT for table relationships can be confusing but it may help if you compare it to reading a sentence from left to right, and think of the parent table (the table that supplies the value for the field that's being linked on) on the left. As you move to the right of the relationship, you see the child tables that use the field as a foreign key to link to the parent table. A LEFT JOIN would therefore be a join that starts from the left of the relationship (Leads in the above example) and a RIGHT join would start from the right (Companies). Note the word OUTER is optional in both relationships. Both of these conditions place a priority on one of the tables, selecting all records from one and matching records from the other. An Inner Join only looks for matching records in both tables.
WHERE - This clause specifies the criteria that each row must meet in order to be included in the results. In this example, we're looking for records where the Shift field equals 1. This clause can specify multiple conditions using the AND and OR keywords and parenthesis to group conditions for the right result. Some of the possible WHERE clauses in this sample could be:
WHERE LastName = “Merz”
WHERE Current <> 0 AND HireDate < #1/1/2008#
WHERE (Current <> 0 AND JobTitle = “Clerk”) OR JobTitle = “Bookkeeper”
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. Grouping is the default treatment for fields in SQL queries and is only noticeable in the end results when aggregate functions like counts or aggregates are applied. For example, in the following query, the COUNT() function is used to get a count of employees in each group.
SELECT Count(EmpKey) AS CountOfEmpKey, HireDate, Shift, Department
FROM Leads
GROUP BY HireDate, Shift, Department;
The groupings in the query are applied in the order that they’re shown in the statement so this query is going to group first by the hiring date. If that was the only grouping, you would get a list of hire dates and the number of employees for each date. Then the query breaks out the results by Shift and finally Department so the final query returns the job lead count for each combination of HireDate, Shift and Department in the table.
HAVING - HAVING is used to limit the records returned based on properties of groups 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. If you specify a particular employee shift in the WHERE clause, only records with that shift will be returned. HAVING is applied after the grouping is done. In the examples shown above, you could use HAVING to return only the shifts for which there was more than a specific number of employees found. This would have to be done after the SQL statement completed the grouping by shift so it would be done through the HAVING clause. The HAVING keyword is more often used to select by the results of aggregate functions like COUNT or AVG.
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.
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.
The example above also demonstrates the WHERE and HAVING keywords. This is a short query on a database I created to analyze data from my website logs. The query shows the number of page requests and groups by the file name requested from the site (cs-uri-stem). The Count() function on the first line counts the number of requests in each group. The WHERE clause uses the Like keyword to limit the query to certain types of content, in this case, it’s using the * wildcard to select all files that end with the ZIP extension. Finally, the HAVING clause uses the Count() function again to limit the query results to groups with a count of over 100. In other words, I only want to see cases where a specific type of file (ZIP files) was requested more than 100 times
SQL contains many other statements, commands, operators and functions but the ones listed above will be enough to keep you from feeling lost when you view the SQL for a query.
Read the entire Microsoft Access for Beginners series offline ...
Microsoft Access for Beginners
Expanded, updated for Office Access 2010 and available from Amazon.com.
