Fundamentals of Queries and Data Selection
To analyze data in Microsoft Access, display the table or query in Datasheet View, the form in Form View, or the report in Report View. To assist you with data analysis, when a table, a query, a form, or a report is displaying in their regular view, the Ribbon is equipped with a section titled Sort & Filter in the Home tab:
Practical Learning: Introducing Queries
Introduction to Query Design
Query design consists of selecting the fields that would be part of a query. Fields can be added by designing a query.
To start designing a new query, on the Ribbon, click the Create tab. In the Queries section, click the Query Design button . This would display the Show Table dialog box.
Practical Learning: Starting a Query
The Show Table Dialog Box
When starting a new query, you must specify where data would come from. If you are visually creating the query, the Show Table dialog box displays a list of existing tables in the Tables tab:
The Queries tab shows a list of already created queries in the Queries property page.
A simple query can have its data originate from a single table or an existing query. If you are using the Show Table dialog box, to choose the table or query that holds the information needed for this query, click the tab of the category. Then:
When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query:
After selecting a table, some tables, a query, or some queries from the Show Table dialog box, you can click the Close button of the dialog box. If the Show Table dialog box is closed or for any reason you want to display it:
Practical Learning: Introducing Query Design
The Query Window
When designing a query, you use a window named the Query window. If the database is set to show overlapped windows, the query's 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 tab or the title bar displays a special menu when right-clicked.
In the top wide area of the Query window, the query displays an object ((a) table(s), (a) query (queries)) or a group of objects that was (were) selected to create the query. The lower portion of the query displays various boxes. The upper and the lower sections of the query window are separated by a splitter bar that you can use to resize them:
Adding a Field to a Query
To make a field participate in a query, you have various options:
Practical Learning: Adding a Field to a Query
Executing a Query
Executing a query consists of viewing its results. The action or outcome may depend on the type of query. To view the result of a query:
If you had manually written a SQL statement and want to execute it, change the view to Datasheet View.
Practical Learning: Executing a Query
Managing a Query
Saving a Query
As mentioned already and as we will see in different lessons and sections, there are various ways you can use a query or a SQL statement. For example, you can start a query simply to see some records or to test a condition. If you plan to use a query many times, you should save it. This is done the same way as for a table:
If the query was not previously saved, you would be asked whether you want to save it. You will have to name the query. Unlike a form or report, the name of a query must be different from the name of any table of the same database.
To display a query in Design View, from the Navigation Pane, right-click the query and click Design View.
The Query Builder
After saving a query, it is represented in the Navigation Pane by an icon and a name.
The Properties of a Table
To let you get some information about a query, Microsoft Access provides a dialog box that allows you to know the name of the table you are accessing as well as the date and time when it was created or modified.
To display the properties of a query, right-click the query in the Navigation Pane and click Object Properties. Here is an example:
The Property Sheet of a Query
In the Design View of a query, Microsoft Access provides a Property Sheet window that allows you to specify or manage some characteristics. To display the Property Sheet of a query, right-click any part of the query in Design View and click Properties.
Unlike the table, the content of the Property Sheet depends on the area of the query that is being accessed. To access the properties of the query itselft, after displaying the Property Sheet, click an empty area in the top section of the Query window. Here is an example:
Notice that the Property Sheet has only one tab. To display the properties of a field, click it in the bottom section of the window:
This time, the Property Sheet has two tabs.
Introduction to SQL and Queries
Querying in SQL
Querying a database is equivalent to selecting records or their values. To support this operation, the most fundamental word used in SQL is called SELECT. As its name indicates, when using SELECT, you must specify what to select.
To select records, you create a SQL expression. The most basic formula to follow is:
SELECT what FROM what-object[;]
The SELECT and the FROM keywords are required. The what-object part is the name of a table or a query.
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. An example would be:
SELECT what FROM Employees
As an option, you can end a SQL statement with a semicolon but it is not required.
In the SQL, to add a 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;
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. Here is an example:
SELECT [FirstName] FROM Employees;
The name of the table or query can also be delimited by square brackets. Here is an example:
SELECT [FirstName] FROM [Employees];
Opening the SQL Code of a Query
When a query is displaying in Design View, to access its code:
Practical Learning: Introducing SQL Code
Distinct Field Selection
If you specify a column to select from a table (or query), every record would come up. This can result in repeated records. Sometimes you want to show each value only once.
In the SQL, to get a list of non-repeating values, put the DISTINCT keyword between SELECT and the name of the column.
Practical Learning: Introducing SQL Code
Adding Many Fields to a Query
To specify the fields of a query, use the table(s) or query(queries) displayed in the upper section of the Query window. From their lists, you can select which fields are relevant for your query. You select the fields using the same techniques we have used for the Field List of a form or report, using the mouse, the Ctrl, and the Shift keys of the the keyboard.
Selecting Fields in SQL
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 make your code easy to read, and especially if the statement is very long, you can put it in different lines. The primary approach is to put the SELECT statement on its own line and the FROM clause on its own line.
A second technique is to put each field name on its own line. Here is an example:
SELECT FirstName, LastName FROM Employees
In fact, the comma of each field that has one can be put on the next line, preceding the next field. Here is an example:
SELECT FirstName ,LastName ,Gender ,DateOfBirth FROM Employees
Practical Learning: Using the Print Preview of a Report
Introduction to the Query Wizard
Probably the easiest way to create a query is by using a wizard. As seen with other categories of objects (forms and reports), Microsoft Access provides a wizard to help you easily create a query. The wizard presents the tables and queries of the database so you can select the fields you need.
To use the Query Wizard, on the Ribbon, click the Create tab. In the Queries section, click Query Wizard . This would display the New Query dialog box:
On the New Query dialog box, 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.
After creating a query using the Simple Query Wizard, it becomes a normal query like one you had designed. This means that you can manage its fields any way you like.
Selecting All Fields
To include everything from the originating table or query:
Here is a statement that results in including all fields from the Employees table:
SELECT * FROM Employees;
Qualifying the Names of Fields
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 what-object.what-field FROM what-object[;]
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. Here is an example:
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. Here is an example:
SELECT [Employees].[FirstName], LastName FROM [Employees];
Practical Learning: Ending the Lesson