SELECT What FROM WhatObject;

The What factor can be the name of a column of a table or query. The WhatObject factor can be the name of a table or a query.

To specify the column you want to select, replace the What factor in the syntax with the name of the desired column. Here is an example:

SELECT LastName FROM Employees;

To select everything from a table or query, you can use the asterisk as the What factor of our formula. For example, to select all records, you would use the statement as follows:

SELECT * FROM Employees;

Alternatively, you can precede the * with the ALL keyword. Here is an example:

SELECT ALL * FROM Employees;

To consider more than one column in a statement, you can list them in the What factor of our formula, separating them with a comma. The formula to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

SELECT FirstName, LastName, HourlySalary
FROM Employees;

When writing the name of a table, a query, or a column, if it's in more than one word, you must include it in square brackets. To be safe, even if the name is in one word, you should still include it in square brackets. Based on this, the above statement would be written as follows:

SELECT * FROM [Employees];

Another suggestion you can use is to qualify the name of each column to indicate the table or query it belongs to. To do this, type the name of the table or query, followed by a period, followed by the name of the column or the *. Here is an example:

SELECT Employees.FirstName, Employees.LastName
FROM Employees;

You can also delimit each name with square brackets as follows:

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

The Visual Basic language is equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName] 

The SQL statement would be:

SELECT Employees.DateHired,
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM   Employees;

Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left$ function to retrieve the first character of the middle name and include the call to that function in your query. Here is an example:

SELECT Employees.DateHired,
       [FirstName] & " " & Left([MiddleName],1) & " " & [LastName]
FROM   Employees;

In this case, some records don't have a middle initial because they don't have a name. For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

SELECT Employees.DateHired,
       IIf(IsNull([MiddleName]),
           [FirstName] & " " & [LastName],[FirstName] & " " & 
	   UCase(Left([MiddleName],1)) & " " & [LastName])
FROM Employees;

In the same way, you can use any of the built-in functions we reviewed in previous lessons.