Introduction to Queries

Fundamentals of Queries and Data Selection

Introduction

Practical Learning: Introducing Queries

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the States Statistics2 database

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

1. On the Ribbon, click Create
2. To start a new query, in the Queries section, click the Query Design button

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:

• Double-click the table or query
• Click the table or query and click Add

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:

• In the Query Setup section of the Design tab of the Ribbon, click the Show Table button
• Right-click anywhere on the query window and click Show Table...

Practical Learning: Introducing Query Design

1. On the Show Table dialog box, click States
2. Click Add
3. Click Close

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:

• Drag it from the list and drop it in the bottom section of the query window
• Double-click a field from the list
• In the bottom part of the query window, click an empty Field box to display a combo box. Then click the arrow of that combo box and select the desired item from the list. Here is an example:

Practical Learning: Adding a Field to a Query

• From the list in the top part of the window, drag StateName and drop it in the first column in the bottom part of the window:

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 the query is currently closed, in the Navigation Pane:
  • Right-click the query and click Open
  • Double-click the query
• If the query is currently opened in the Design View:
  • On the Ribbon, click the Run button
  • On the Ribbon, click the View button
  • On the Ribbon, click the arrow of the View button and click Datasheet View
  • On the status bar of the Query window, click the Datasheet View button

If you had manually written a SQL statement and want to execute it, change the view to Datasheet View.

Practical Learning: Executing a Query

1. On the Ribbon, click the Run button
2. Close the Query window
3. When asked whether you want to save it, click No

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:

• Right-click the tab or title bar of the window and click Save
• Click the Save button on the title bar of Microsoft Access
• If you are viewing the query, close it

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.

Opening a Query

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:

• On the Ribbon, click the arrow of the View button and click SQL View

  

• Right-click the tab or the title bar of the window and click SQL View
• Right-click anywhere in the window and click SQL View
• On the status bar of the window, click the SQL View button

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

Introduction

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

1. On the Ribbon, click Create
2. To start a new query, in the Queries section, click the Query Design button
3. On the Show Table dialog box, double-click States
4. In the States list, double-click StateName

   

5. In the States list, click AreaSqrMiles
6. Press and hold Ctrl
7. Click AdmissionUnionDate
8. Release Ctrk
9. Drag one of the selected items to the bottom side of the window

   

10. In the States list, click AdmissionUnionOrder
11. Press and hold Shift
12. Click Region
13. Release Shift
14. Drag the selected items to the bottom side of the window

    

15. To execute, on the Ribbon, click the Run button

    

16. To save, on the Ribbon, click the Save button
17. Set the query name as StatesNames
18. Click OK
19. Close the query

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 the top part of the window, double-click * in the list of fiels
• In the top part of the window, drag * and drop in the bottom part of the window. Here is example:

  

  

• Select all fields in the table and drag the selection to the bottom part of the 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;

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. 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

Introduction

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

Introduction

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

Field Selection

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

Alias Names

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

1. On the Ribbon, click Create and click the Query Design button
2. On the Show Table dialog box, double-click States
3. Click the cell on the right side of Field and type State: StateName + ' ' + Abbreviation
4. From the States list, dtag AreaSqrMiles and drag it to the second column at the bottom

   

5. To execute, on the Ribbon, click the Run button

   

6. Close the query
7. When asked whether you want to save, click No

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

Sex: IIf([Gender]="Male","M","F")

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:

• To select a field in the lower section of the view, click the tiny bar of the column header:

  

• To select a range of columns, click the column header of a column at one end, press and hold Shift, then click the column header at the other end

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 delete a column:
  • Once it is selected, press Delete
  • Right-click the column header and click Cut
• To delete a group of columns, select them and press Delete

To remove a column from a SQL statement, simply delete it. An example would be:

SELECT EmployeeName, DateHired, Title FROM Employees;

To

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;

To

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:

• To move a field, click its column header once. Click it again and hold your mouse down, then drag in the direction on your choice. Here is an example:

  

• To move a group of columns, first select the group and then proceed as if it were one column

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

Data Entry on a Query

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.

Query Printing

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

• Close Microsoft Access