Introduction to SQL

Introduction

The primary language used to analyze records in a database is called the Structured Query Language and abbreviated SQL. Microsoft Access supports the SQL but its own interpretation.

There are various ways you can use SQL in Microsoft Access:

• You can start a query and switch to SQL View. This would open a blank window you can use to write your code
• You can create a statement for the Record Source of a form or report or the Control Source of a control
• You can write code that includes a SQL statement

When a query is displaying in Design View, to access its code:

• On the Ribbon, you can click the arrow of the View button and click SQL View
• You can right-click its title bar and click SQL View
• You can right-click anywhere in the window and click SQL View

The most fundamental word used in SQL is called SELECT. As its name indicates, when using SELECT, you must specify what to select. The formula you can use is:

SELECT What FROM WhatObject;

The FROM keyword is required. The WhatObject of our formula is the name of the table or query you would select from the wizard. An example would be:

SELECT What FROM Employees;

The SQL is not case-sensitive. This means that SELECT, Select, and select represent the same word. To differentiate SQL keywords from "normal" language or from the database objects, it is a good idea to write SQL keywords in uppercase.

A SQL statement must end with a semi-colon.

The What factor of our formula represents the field(s) you select from a table or query.

In the SQL, to add one column to a statement, replace the What factor of our formula with the name of the column. An example would be:

SELECT FirstName FROM Employees;

If you want to include more than one field from the same table, separate them with a comma. For example, to select the first and last names of a table named Employees, you would write the statement as follows:

SELECT FirstName, LastName FROM Employees;

To include everything from the originating table or query, use the asterisk * as the What factor of our formula. Here is a statement that results in including all fields from the Employees table:

SELECT * FROM Employees;

The name of a field can be delimited by square brackets to reduce confusion in case the name is made of more than one word. The square brackets provide a safeguard even if the name is in one word. Based on this, to create a statement that includes the first and last names of a table named Employees, you can write it as follows:

SELECT [FirstName], [LastName] FROM [Employees];

To identify a field as belonging to a specific table or query, you can associate its name to the parent object. This association is referred to as qualification. To qualify a field, type the name of the object that is holding the field, then add a period followed by the name of the field. The basic syntax of a SELECT statement would be:

SELECT WhatObject.WhatField FROM WhatObject;

Imagine you want to get a list of people by their last names from data stored in the Employees table. Using this syntax, you can write the statement as follows:

SELECT Employees.LastName FROM Employees;

Or

SELECT [Employees].[LastName] FROM [Employees];

In the same way, if you want to include many fields from the same table, qualify each and separate them with a comma. To list the first and last names of the records from the Employees table, you can use the following statement:

SELECT Employees.FirstName, Employees.LastName FROM Employees;

Or

SELECT [Employees].[FirstName], [Employees].[LastName] FROM [Employees];

If you want to include everything from a table or another query, you can qualify the * field as you would any other field. Here is an example:

SELECT Employees.* FROM Employees;

Or

SELECT [Employees].* FROM [Employees];

You can also use a combination of fields that use square brackets and those that do not:

SELECT FirstName, [LastName] FROM Employees;

The most important rule is that any column whose name is in more than one word must be included in square brackets.

You can also use a combination of fields that are qualified and those that are not

SELECT [Employees].[FirstName], LastName FROM [Employees];