SQL stands for Structured Query Language. As the name implies, it is used in the creation of queries. Whenever we create a Query using the Access Query Design Grid, Access converts the information we provide into SQL Code. We can view and edit this code by selecting SQL VIEW from the RESULTS group of the QUERY DESIGN ribbon. But why go to the trouble of learning SQL when we can just use the Design Grid? There are many reasons for this. For advanced users there are things which can be done in SQL that are too complex for the Design Grid to handle. In addition to this, SQL is also used within the Access Visual Basic programming langage thereby allowing us to automate queries and use variables in query criteria. (See my post on Automating an Update Query)
However, SQL is also used in other areas of Access such as the properties windows where we can define Record Sources for forms, Row Sources for Combo Box controls, and criteria for filters to name just a few. As such, even at a relatively basic level, it is good to have a general awareness of SQL, and maybe some knowledge about how to create and edit simple SQL Statements.
Lets take a look at a simple SQL Statement used to query a database table. In this example, the table is called tblCustomer. We are going to select three fields from this table - FIRSTNAME, SURNAME, and CITY. The criteria we are going to use ='bolton' under the CITY field. The query is designed to show a three column list of records where the value contained in the CITY Column is 'Bolton'. Here is the SQL Code:
SELECT FirstName, Surname, City
FROM tblCustomer
WHERE City="bolton"
NB: When you look at code using Access SQL View you find that the syntax is slightly different. Extra brackets are put around the expression following the Where clause; and the field names after the Select clause are written with a table name [full stop] field name, like this - tblCustomer.FirstName. This is how Access codes it own version of SQL. It is, however, perfectly capable of reading the simpler version printed above. Just be aware that Access will code statements slightly differently for its own purposes.
As you can see there are three line to this SQL Statement. The capitalised words at the beginning of each line are SQL keywords (or 'Clauses') and the small case words following relate to database fields, tables and criteria. The Statement begins with the SELECT clause. This is saying we are going to select the following fields (ie FirstName, SurName, and City) in this Statement. In the next line we have the FROM clause. This is saying that the fields selected above are taken from the following table (ie tblCustomer). The final line is the WHERE clause. This is saying that we are only interested in records where the following expression is true (ie city=Bolton). If we had created this Query using the Query Design Grid, it would have looked like this:
 |
| Figure 1: The equivalent query created using the Query Design Grid. |
One thing that may strike you when you compare the SQL Statement with the Query Design Grid, is how simple and brief the Code actually is. A simple application of SQL is to enter a statement like that above for a form's RECORD SOURCE, as opposed to using a standard query created using the Grid. This saves us having to create a seperate query, and minimises the number of Objects appearing in our database window. Just enter the whole SQL statement, as a single line, directly into the RECORD SOURCE cell of the PROPERTIES WINDOW.
 |
| Figure 2: An SQL Statement used as a forms RECORD SOURCE. |
This has been a brief and simple introduction to Access SQL. There is, of course, so much more to the subject. I intend to post more articles some time in the future which go into more detail.
No comments:
Post a Comment