Fundamentals of Queries and Data Selection
Practical Learning: Introducing Queries
Introduction to Queries
A query is a technique of selecting some or all data to present to the user. A query is also a database object like a table but a query is used to hold the results of data analysis. Query design consists of selecting the fields that would be part of a query.
Analyzing the records is also referred to as querying. Data analysis can be performed on tables, forms, queries, or reports. You can also use an external application like Microsoft Excel for data analysis.
To analyze data in Microsoft Access, display the table or query in the 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:
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. The top wide area of the Query window 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:
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(s) 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
If you plan to use a query many times, you should save it. This is done the same way as for a table:
Unlike a form or report, the name of a query must be different from the name of any table of the same database. After saving a query, it is represented in the Navigation Pane by an icon and a name.
To display a query in Design View, from the Navigation Pane, right-click the query and click Design View.
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.
Once a query has been created and saved, it becomes a regular database object. If a query exists already, to use it, the user can open it like a table. To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query. Here is an example:
Private Sub cmdOpenVideoTitles_Click() DoCmd.OpenQuery "VideoTitles" End Sub
Closing a Query
After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:
Private Sub cmdCloseVideoTitles_Click() DoCmd.Close acQuery, "VideoTitles" End Sub
When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen.
Introduction to SQL and Queries
Querying in SQL
In the SQL, the language used to examine and process records is called the Data Maniplulated Language or DML. The most basic operation of DML for data analysis consists of selecting data. To support this, the SQL provides the SELECT keyword introduced in the first lesson. Whe it comes to data analysis, the primary formula to follow is:
SELECT what FROM what-object[;]
The SELECT and the FROM keywords are required. Remember that the SQL is not case-sensitive. This means that SELECT, Select, and select represent the same word.
The what object can be the name of a column of a table or query. The what-object 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 formula with the name of the desired column. Here is an example:
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 the Design View, to access its 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. Here is an example:
Notice that some values display more than once. Also notice the total number of 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. Here is an example:
SELECT DISTINCT CabinType FROM Cabins;
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.
You can select fields in a range and drag them to the lower part of the window. You can also select fields at random, then drag one of the selected fields to the bottom part.
Practical Learning: Selectting Various Fields
Selecting Fields in the 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
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 want.
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 in the Tables/Queries combo box. After selecting the source of the records, in the Available Fields list box, select the fields that will participate in the query. Here is an example:
The second page allows you to select the layout of the query. The options are Detail and Summary. The third page allows you to name the query. Here is an example:
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 visually include everything from the originating table or query in the Query window:
In your SQL statement, use the asterisk * as the what factor of our formula. Here is an example that selects all fields from a 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];
When writing the name of a table or a column, if it's in more than one word, you must include it in square brackets. Here is an example:
SELECT [Account #], [First Name], [Last Name], [Phone #] FROM Employees;
Remember that even if the name is in one word, you can include it in square brackets. Here is an example:
SELECT * FROM [Employees];
Data Selection and Record Sets
We already know that, to create a record set, you can call the OpenRecordset() method of the Database class. So far we pass the Expression as the name of a table. The argument can also be passed as SQL SELECT statement. Here is an example:
Private Sub cmdGetEmployees_Click() Dim dbKoloBank As Object Dim rstEmployees As Object Set dbKoloBank = CurrentDb Set rstEmployees = dbKoloBank.OpenRecordset("SELECT * FROM Employees") End Sub
In this case, the second argument, a member of the RecordsetTypeEnum enumeration can be one of the following:
Queries and the Microsoft Access Object Library
To create a new query in the Microsoft Access Object Library, declare a variable of type Object. To let you create a query, the database is equipped with a method named CreateQueryDef. It takes two arguments. The first argument, which is required, is the name of the query. The second argument is optional. Here is an example of starting a query:
Private Sub cmdGetEmployees_Click() Dim dbKoloBank As Object Dim rstEmployees As Object Set dbKoloBank = CurrentDb Set rstEmployees = dbKoloBank.OpenRecordset("Employees") Set rstEmployees = Nothing Set curDatabase = Nothing End Sub
The second argument is a SQL statement that holds the list of fields for the query. You can first store that statement in a string and pass that string as the second argument. Here is an example:
Private Sub cmdCreateQuery_Click() Dim curDatabase As Object Dim qryEmployees As Object Dim strStatement As String ' Get a reference to the current database Set curDatabase = CurrentDb strStatement = "SELECT FirstName, LastName FROM Employees;" ' Create a new query named EmployeesInfo Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement) End Sub
Getting a Reference to a Query
The queries of a database are stored in a collection named QueryDefs. You can use it to get a reference to a query by accessing it through its index. Here is an example:
Private Sub cmdGetQueryName_Click() Dim dbExercise As Objet Dim qryEmployees As Object ' Get a reference to the current database Set dbExercise = CurrentDb ' Get a reference to the third query in the queries collection Set qryEmployees = dbExercise.QueryDefs(2) MsgBox "Name of 3rd query: " & qryEmployees.Name End Sub
The SQL Property of a Query
If you don't pass the second argument, to formulate the SQL statement, you can later access its property named SQL. You can simply assign the desired statement to this property. Here is an example:
Private Sub cmdCreateQuery_Click() Dim dbExercise As Object Dim qryEmployees As Object ' Get a reference to the current database Set dbExercise = CurrentDb ' Create a new query named EmployeesInfo Set qryEmployees = dbExercise.CreateQueryDef("EmployeesInfo") qryEmployees.SQL = "SELECT FirstName, LastName FROM Employees;" End Sub
DAO and Queries
To support queries, the DAO library provides a class named QueryDef. The queries of a database are stored in a collection named QueryDefs.
Creating a Query
To let you programmatically create a query in DAO, the Database class is equipped with a method named CreateQueryDef. The syntax of this method is:
Public Function CreateQueryDef(ByVal Optional NewQueryName AS String, ByVal Optional SQLStatement As String) As QueryDef
The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query. Here is an example:
Private Sub cmdCreateQuery_Click() Dim strStatement As String Dim curDatabase As Database Dim qryCustomers As QueryDef ' Get a reference to the current database Set curDatabase = CurrentDb strStatement = "SQL Statement" ' Create a new query named CustomersRecords Set qryCustomers = curDatabase.CreateQueryDef("CustomersRecords", strStatement) Set qryCustomers = Nothing End Sub
When creating a query, you can make it use a SQL statement as its contents. To do this, pass the statement as the second argument of the CreateQueryDef() method. Here is an example:
Private Sub cmdCreateQuery_Click() Dim dbExercise As DAO.Database Dim qryEmployees As DAO.QueryDef Set dbExercise = CurrentDb Set qryEmployees = _ dbExercise.CreateQueryDef("StaffMembers", _ "SELECT EmployeeNumber, EmployeeeName " & _ "FROM Employees") dbExercise.Close Set dbExercise = Nothing Application.RefreshDatabaseWindow End Sub
The Alias Name of a Table or Query
An alias is another name for the object used as the source of a query. You can create an alias when designing a query or in the SQL code. To visually create an alias, click anything in the top list of items. In the Property Sheet, change the value of the Alias field:
To create an alias in the SQL code, on the right side of the name of the table or query in the FROM clause, add a space and type a name of your choice. Here is an example:
SELECT EmployeeNumber, FirstName, LastName FROM Employees empl;
After creating the alias, you can use it to qualify the name of a field in the SQL statement. You use the alias as if it were the name of the table or query applied to the field. Here is an example:
SELECT empl.EmployeeNumber, empl.FirstName, empl.LastName FROM Employees empl;
You can put the AS keyword before the alias. Here is an example:
SELECT houses.PropertyNumber, houses.PropertyType, houses.Bedrooms, houses.Bathrooms, houses.MarketValue FROM Properties AS houses;
The alias can be in more than one word. In that case, you must include it between [ and ]. Here is an example:
SELECT [staff members].EmployeeNumber, [staff members].FirstName, [staff members].LastName FROM Employees AS [staff members];
You can use the alias to qualify some or all fields. You can also use the alias to qualify the * field. Here is an example:
SELECT kids.* FROM Students kids;
The Caption of a Field
If you create a query and display its result, by default, each column displays its caption as the name of the column. To display a caption of your choice, display the query in Design View. Add or select a column. Right-click the column and click Properties. In the Property Sheet, click Caption and type the desired caption.
In SQL, as seen for queries, the alias of a field can be created using the AS keyword as in the following formula:
column-name AS Alias
The column-name factor is the name of the column in the table (or query). The AS keyword is required. The Alias factor is the caption you want the column to display. Here are examples:
SELECT PropertyNumber AS [Prop #], PropertyType, Bedrooms AS Beds, Bathrooms AS Baths, [Year Built], [Market Value] FROM Properties;
The Alias Name of a Field
To visually create an alias for a field, in the Design View of a query, on the left side of the column name, type the caption of your choice, followed by :, and followed by the name of the column. Here is an example:
Primary Operations on Fields Selections
Concatenating Some Strings
When creating a SQL expression, you can combine two or more columns to display them together as one. To concatenate two column names, you can use the + (or & (the ampersand)) operator. To concatenate more than two strings, enter their names separated by + (or &) operators.
Here is an example:
Practical Learning: Concatenating Strings in Data Selection
An Expression for Data Selection
An arithmetic operator can be used to create an expression for the value of a field of a query. Here is an example:
Calling a Function for Data Selection
You can call a function in your SQL, that is, in the place of a column. 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. Here is an example
This expression says, "If the the value of the Gender field is Male, display M, otherwise display F". If this expression is entered in the Field box of a query, when the query is run, a column named Sex would display and its values would result from the expression.
SELECTing Fields From Different Tables
If you have more than one table in your database, you can use a statement that selects any field(s) you want from those tables. Neither the tables nor the columns need to have anything in common. The formula to follow is:
SELECT what-field(s) FROM Table_1, Table_2, . . . Table_n
You start with the SELECT keyword followed by the list of fields from the tables. If the tables have columns with different names, you can simply list the name of each column. Here is an example:
SELECT AccountNumber, EmergencyName, EmployeeNumber, Title, HourlySalary FROM Customers, Employees;
When you select fields from different tables, in the result, each of the records of the first table would display, each showing the first record (combination of the selected columns) of the second table. Then each of the records of the first table would show again, followed by the second record (combination of the selected columns) of the second table. This will continue until all records of the second table have displayed. Consequently, the resulting query would contain (Number of Records of First Table) * (Number of Records of Second Table). For example, if the first table contains 4 records and the second table contains 2 records, the statement would produce 4 x 2 = 8 records. Therefore, the above statement would produce:
If the tables have fields with the same name, you must qualify at least the column(s) with the same name. This can be done as follows:
SELECT AccountNumber, Customers.FirstName, Customers.LastName, EmergencyName, EmployeeNumber, Employees.FirstName, Employees.LastName, Title, HourlySalary FROM Customers, Employees;
If you don't qualify the common names, you would receive an error when you execute the query.
Managing the Fields in a Query Window
Selecting a Column
Some 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.
Removing a Column From a Query
If you don't need a column anymore on a query, 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;
Replacing a Column
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;
Moving a Column
Columns on a query are positioned incrementally as they are added to it. 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;
A Query as a Datasheet Object
A query uses the same approach of a table to present its data: it is made of columns and rows whose intersections are cells. Data entry on a query is done the same as on a table: data is entered into cells.
Like tables, queries provide you with a fast means of printing data. To print a query, if it is opened, on the Ribbon, click File and click Print
Query Aesthetic Formatting
The Datasheet View of a query appears exactly like that of a table. It is aesthetically formatted using the same font and color characteristics as a table. Here is an example:
Practical Learning: Ending the Lesson