To query the records of a database, you can use Boolean algebra combined with some operators. Boolean Algebra works on logical statements. A statement is a sentence that acknowledges a fact or a possibility. That fact is eventually evaluated as being true or false. There are three main types of logical statements:
These are the types of evaluations you make when analyzing the records of your database.
In Lesson 2, we saw how to create a table and how to populate it with a few records. In Lesson 3, we saw how to present the data of a table to a user but through a form. In that lesson, we presented all the records of a table to a user. A query is a technique of using all data or only selecting a few records to present to the user. Data used on a query can originate from a table, another query, or a combination of tables and/or queries.
The universal or the most popular language used to query a database is called the Structured Query Language and abbreviated SQL.
Like most other database environments, Microsoft Access supports SQL. Like every computer language, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it.
The most fundamental word used in SQL is called SELECT. As its name indicates, when using SELECT, you must specify what to select.
There are various ways you create a query in Microsoft Access.
The Query Wizard offers the simplest approach to creating a query where in step by step you specify the data that the query will make available. The wizard presents the tables that are part of the database and you select which fields you need. Such a query is called a Select Query.
To use the Query Wizard, on the Ribbon, you can click the Create tab and, in the Queries section, click Query Wizard . This would display the New Query dialog box:
On the New Query dialog box, you can click Simple Query Wizard and click OK. The first page of the Simple Query Wizard expects you to choose the origin of the query as a table or an already created query.
When creating a query, in reality you create a SQL expression but Microsoft Access takes care of creating a SQL statement behind the scenes for you. As mentioned already, when creating a query, you must select a table. In SQL, this is equivalent to the following formula:
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.
Query design consists of selecting the fields that would be part of a query. We previously learned that fields could be added to a query by using the Query Wizard. Fields can also be added by designing a query. To proceed with this approach, the query should be displayed in Design View. You can also write a SQL statement to select the fields for a query:
This would display the Show Table dialog box that allows you to specify the table or query that holds the fields you want to use in the intended query.
When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query:
When a query is displaying in Design View, to access its code:
When starting a new query, you must specify where data would come from. If you are manually writing your SQL statement, from our previously seen syntax, you must replace the WhatObject factor by the name of a table or query. If you are visually creating the query, the Design View displays a list of already existing tables in the Tables tab, and a list of already created queries in the Queries property page:
A simple query can have its data originate from a single table. In the Show Table dialog box, to choose the table that holds the information needed for this query, you can click that table and click Add. You can also double-click it. After selecting a table, some tables, a query, or some queries, you can click the Close button of the Show Table dialog box. If the Show Tables dialog box is closed or for any reason you want to display it:
The Query window is presented like a regular window. If the database is set to show overlapped windows, its title bar displays its system button on the left section. This can be used to minimize, maximize, restore, move, resize, or close the window. Like all Microsoft Access window objects, the title bar displays a special menu when right-clicked:
The right section of the title bar displays the
classic system buttons of a regular window.
To create the fields for a query, you use the table(s) or query(queries) displayed in the upper section of the window. Once you have decided on the originating object(s), you can select which fields are relevant for your query:
To make a field participate in a query, you have various options:
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;
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;
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;
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];
In the Navigation Pane, a query is represented by an icon and a name.
Executing a query consists of viewing its results but the action or outcome may depend on the type of query. To view the result of a query:
If you manually write a SQL statement and want to execute it, change the view to Datasheet View.
Sometimes, the idea of using a query is to test data or verify a condition. Therefore, a query can provide just a temporary means of studying information on your database. When performing the assignment or when testing values before isolating an appropriate list, you can add, insert, delete, replace or move fields at will. We have already covered different techniques of adding or inserting fields, from the Query Wizard or from a list of fields in the top section of the query window. Some other operations require that you select a column from the bottom section of the query window:
Since selecting a column in the Query window is a visual operation, there is no equivalent in SQL.
As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with another column:
To remove a column from a SQL statement, simply delete it. An example would be:
SELECT EmployeeName, DateHired, Title FROM Employees;
SELECT EmployeeName, Title FROM Employees;
To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:
To replace a column from a SQL statement, simply change its name to the name of another existing column of the same table or query. An example would be:
SELECT EmployeeName, DateHired, Title, Salary FROM Employees;
SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;
Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first select it. Then:
Since moving a column in the query window is a visual operation, there is no equivalent in SQL. Otherwise, in the SQL statement, you can either edit the statement or delete the field in one section to put it in another section. An example would be:
SELECT EmployeeName, DateHired, EmployeeNumber, Salary FROM Employees;
SELECT EmployeeNumber, EmployeeName, DateHired, Salary FROM Employees;
A query uses the same approach of a table to present its data: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to either prepare data for analysis or to isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.
Data entry on a query is done the same as on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality. Like a table, a query provides navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the last or any record in the range of those available.
Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed sheet but not a professionally printed document. Data printing on a query is done with the exact same approaches and techniques as for a table.
When creating a form or a report that would be used to present data to the user, we selected a table as the source of data. In the same way, if you have created a query that holds some records, you can use it as the base of data for a form or report.
If you delete the form or report, the query would still exist because it is a separate object, but it would lose its data holder.
Instead of first formally creating a query before using it for a form or report, you can select the data from a table or a query and use it on the form or report. Behind the scenes, Microsoft Access would create the SQL expression that is directly applied to the form or report. Such a query is not saved as an object. This technique is used when you do not need a formal query as the base of data for a form or report.
There are various techniques you can use to create a query specifically made for a form or a report:
After creating the form or report, if you delete it (the form or the report), the expression would be lost also.