Querying a Database

Queries Fundamentals

Introduction to Queries

After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives.

Data filtering is performed using the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

• Start Microsoft Access and open the ROSH database

To create a query in Microsoft Access, on the main menu, you can click Insert -> Query and select an option in the New Query dialog box. Alternatively, in the Database window, you can first click the Queries button. If you click New under the title bar of the Database window, the New Query dialog box would display. While in the Queries section of the Database window, you can also use one of its options to proceed.

After creating a query as we will see in different examples, you can either only view and dismiss it after the necessary analysis, or you can save it for later use. When saving a query, you must give it a name. You can follow the rules and suggestions we have applied to the tables and forms so far. Additionally, when naming a query, because it can be used, as we will see later on, as a data source for a form, avoid giving it the same name as an existing table. If you try this, Microsoft Access would display an error:

To programmatically create a query using either the Microsoft Access Library or DAO, you can use the CreateQueryDef() method of the Database object. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

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.

1. Start Microsoft Access and open the ROSH database
2. In the Database window, click the Forms button
3. On the main menu, click Insert -> Query
4. In the New Query dialog box, click Simple Query Wizard and click OK.
   If necessary, in the Tables/Queries combo box, select Table: StaffMembers

After creating a query and saving it, you can use it as the source of data for a form. To do this, you can visually specify it in the Record Source property of the form. You can also assign the name of a query, as a string, to the form's RecordSource property. Here is an example: Private Sub cmdGetRecordset_Click() Me.RecordSource = "ListOfEmployees" End Sub Instead of first creating a query, you can access the Record Source property of the form and click its ellipsis button. This would prompt you to visually create a query. After creating it, you can close the query window. Microsoft Access would create the necessary SQL statement for you and assign it to the Record Source property of the form.

Instead of visually creating a query, you can write a SQL statement that selects the records and assign that statement to the RecordSource property of the form in code.

Once a query has been created and saved, it becomes a database object like the others we have used so far. If a query exists already, to use it, the user can open it like a table. To do this, in the Queries section of the Database window, the user can double-click the query or right-click it and click Open.

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

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 (Nice!).

The main purpose of data filtering consists of selecting records. As you know already, records are stored in tables but they can be identified by the columns of a table. Therefore, before filtering records, you must first specify the columns that would be involved in your filtering.

If you are creating a query using the wizard, first select a table (or an already created query):

After specifying the table (or query) that holds the records, to select a column, in the Available Fields list, you can double-click the desired column(s):

The technique of selecting the fields is the same applied when creating a table using the Table Wizard.

If you are creating a query in the Design View, when you are presented with the Show Table dialog box, to select a table, you can click it and click the Add button, or you can double-click the table:

If you have already selected a table and want to change it or want to add a table (or query), you can right-click the Select Query window and click Add Table. After specifying the table, to select a column, you can drag it from the table and drop it in a column in the lower section of the window:

Instead of adding one column at a time, you can select more than one column using the Ctrl or the Shift keys and dragging the selection to a column in the lower section of the window. To include all columns in a query, drag the * field and drop it in the bottom section.

Practical Learning: Selecting Fields For a Query

1. In the Sample Fields, double-click FirstName, LastName, DateHired, Salary, IsMarried, and IsATeacher:
    
   
2. Click Next. If you are asked whether you want a detail or a summary query, make sure the Detailt radio button is selected and click Next
3. Change the Title to StaffSocialInformation and click Finish
4. While the query window is selected and displaying, on the Query Datasheet toolbar, click New Object: AutoForm to generate a form
5. Close the form
6. When asked whether you want to save it, click Yes
7. Accept the suggested name as StaffSocialInformation and click OK
8. Close the query
9. In the Forms section of the Database window, right-click StaffSocialInformation and click Design View
10. In the Properties window, notice that the name of the query acts as the Record Source
11. Close the form

Data select in the SQL consists of using the SELECT keyword. The primary formula to follow is:

SELECT What FROM WhatObject;

As stated already, SQL is not case-sensitive. This means that SELECT, Select, and select represent the same word.

The What factor of our syntax is the name of the column(s) of a table. The WhatObject factor can be the name of a table (or a view).

If you (visually) create a query, either using the Query Wizard or the Design View, the database engine would take care of creating the appropriate SQL statement for you. After saving the query, to view its statement, open the query in design view, right-click its window, and click SQL View. You can also access the SQL View option from the main menu under View.

To select everything from a table, you can use the asterisk as the range of values. For example, to select all records, you would use a the statement as follows:

SELECT * FROM Students;

This example would select all records from a table named Students. As opposed to selecting all records, you can select one particular column whose fields you want to view. To do this, you can replace the What factor in the syntax with the name of the desired column. Here is an example:

SELECT LastName FROM Students;

This statement would select only the column that contains the last names. To consider more than one column in a statement, you can list them in the What factor of our syntax, separating them with a comma except for the last column. The syntax to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

As an example, to select the names, gender, and home phone of records from a table called Students, you would the following statement:

SELECT FirstName, LastName, Gender, HomePhone
FROM Students

As mentioned earlier, to programmatically create a query, you can pass the SQL statement as the second argument of the CreateQueryDef() method. Here is an example that uses the Microsoft Access Library:

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 DateHired, FirstName, " & _
                   "LastName, Department FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

You can use the same approach with DAO.

Practical Learning: Viewing a SQL Statement From a Query

1. In the Database window, click Queries
2. In the Queries section, double-click StaffSocialInformation
3. To view its SQL statement, right-click its title bar and click SQL View
    
   
4. To switch it to Design View, right-click its Title Bar and click Query Design
    
   
5. Close the Select Query window. If asked whether you want to save the change, click No

As mentioned previously, after creating and saving a query, you can use it a data source for a form. Instead of primarily creating a query, you can either type a SQL statement in the Record Source property or use its ellipsis button to create a query. Everything is done as seen above.

Practical Learning: Selecting Data for a Form

1. In the Database window, click Forms and double-click Create Form In Design View
2. In the Properties window, click the Record Source field to reveal its ellipsis button and click that ellipsis button
3. In the Show Table dialog box, click Students, click Add, and click Close
4. In the list of fields, click FirstName, press and hold Ctrl
5. In the list of fields, click LastName, ParentsNames, EmergencyName, and EmergencyPhone
6. Release Ctrl
7. Drag one of the selected items and drop in the first empty field in the lower section of the window:
   
8. Close the Query Builder window
9. When asked whether you want to save it, click Yes
10. Display the Field List of the form and notice that its title bar displays a SQL statement
11. Also notice that the Record Source of the form is made of a SQL statement
     
    
12. Save the form as StudentsContactInformation
13. Drag the fields from the Field List to drop them on the form and design it as follows:
     
    
14. Right-click the form and click Form Header/Footer
15. Using the Command Button Wizard, add a button named cmdClose and labeled Close that would be used to close the form
     
    
16. Save and close the form

One of the actions you can take consists of rearranging the list of records in an order other than the one in which they were entered. For example, a user enters the list of students in the order they arrive. At one time the user may want to see a list of students in alphabetical order based on they last names. Rearranging a list of records is referred to as sorting.

Microsoft Access provides the means of sorting records on all database objects, including tables, queries, and forms. To sort the records of a table, a query, or a form, you can right-click the column or the control that displays its values and click Sort Ascending. This would sort the records based on the field you selected. When a list is sorted in ascending order:

• If the list is made or numeric values, the lowest value would become the first, followed by the second to lowest value, and the highest value would become the last. This is the case for the values in an AutoNumber, a COUNTER, or an AUTOINCREMENT column of a table
• If the list is made of strings (words), the alphabet would be used. The string whose first letter is the highest in the alphabet from a, b, c, etc would be the first. In this case, the ascending order is the same as the alphabetical order. For example, in a list made of Paul, Walter, Anne, and John, in that order, when sorted in ascending order, the list would become Anne, John, Paul, Walter. If various strings in the list start with the same letter, the strings with the same starting letter would be grouped first. Then among the strings with the same starting letter, the second letter would be considered and the same algorithm would be applied
• If the list is made of dates, the earliest date would become the first and the most recent date would become the last
• If the list is a combination of numbers and strings, the numbers would be arranged in incremental order first, followed by the list of strings in alphabetical order
• If the list contains empty values, the empty values would be the first, the other values would be arranged in order depending on their type

To sort the records in the reverse order, you can right-click a column (table or query) or a control (form) and click Sort Descending. After sorting records, if you want to perform another operation, you may need to reset the list to its original arrangement. To do this, you can right-click the object and click Remove Filter/Sort. You can also access these three options on the main menu under Records. They are also available on the toolbar(s).

After sorting the records, you can close the object but you would be asked whether you want to save the arrangement. If you click Yes, the object would be saved with the new arrangement and you can view it next time.

If you are working in a query, to include the arrangement in it, open the query in the Design View. In the lower section of the window, click the Ascending field of the desired column and select Ascending from its combo box. In the same way, you can sort the field in reverse order by selecting the Descending option.

To programmatically sort records on a table, a query, or a form, etc, call its OrderBy() method and pass it the name of the column on which the sorting would be based. After calling OrderBy(), access its OrderByOn Boolean property and set its value to True. To remove the sorting, access the OrderByOn and set its value to False.

Sorting Records on a Query

If you are visually creating a query using the Select Query window, to sort the records, after selecting the column on which the sorting would be based, click the box corresponding to its Sort row, you can click the arrow of its combo box and select Ascending. 

To sort the records in reverse order, select Descending instead.

Practical Learning: Sorting Records on a Query

1. In the Database window, click Queries.
   Right-click StaffSocialInformation and click Design View
2. In the lower section of the window, right-click the small bar above FirstName and click Cut
3. In the top section of the window, click FirstName, drag it and drop it on top of DateHired in the lower section of the window
4. In the lower section of the window, click the Sort box that corresponds to the LastName column, then click the arrow of the combo box and select Ascending
    
   
5. Switch the query to Datasheet View to view the result
6. To save the query, on the main menu, click File -> Save As...
7. Type Staff Members in Alphabetical Order as the name of the query and click OK
8. Close the query
9. In the Queries section of the Database window, right-click StaffSocialInformation and click Design View
10. In the lower section of the window, click the Sort box that corresponds to the DateHired column, then click the arrow of the combo box and select Ascending
11. Switch the query to Datasheet View to view the result
12. To save the query, on the main menu, click File -> Save As...
13. Type Staff Members by Seniority as the name of the query and click OK
14. Close the query

In the SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The field used as the basis must be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named StaffMembers. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName;

If you use the * operator to include all fields, you can order the list based on any of the table's fields, as we learned during data analysis. Imagine that you have created a query that includes all fields. The following statement would list the records of the StaffMembers table based on the alphabetical order of the LastName column:

SELECT * FROM StaffMembers ORDER BY LastName;

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM StaffMembers ORDER BY LastName ASC;

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM StaffMembers ORDER BY LastName DESC;

If you want to programmatically create a query from one of these statements, remember that you can use the CreateQueryDef() method.

Practical Learning: Sorting Records on a Form

1. In the Forms section of the Database window, right-click Students and click Design View
2. Display the Form Footer section. On the Toolbox, click the Combo Box and click the left side under the Form Footer bar. If the Combo Box wizard starts, click Cancel
3. In the Properties window, change the name of the combo box to cboColumnNames1
4. Set its Row Source Type to Value List
5. Change the Caption of its accompanying label to Sort by:
6. Make sure the Control Wizard button of the Toolbox is down. On the Toolbox, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
7. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
8. Click under Col1 and type Ascending Order
9. Press the down arrow key and type Descending Order
10. Click Next
11. In the third page of the wizard, accept the first radio button and click Next
12. Change the label to in and click Finish
13. In the Properties window, change the combo box' Name to cboSortOrder
14. Access the properties of the form by double-clicking the button at the intersection of the rulers and click the Events tab
15. Double-click On Open to select [Event Procedure], then click its ellipsis button
16. Implement the event as follows:
     

    Private Sub Form_Open(Cancel As Integer) On Error GoTo FormOpen_Err Dim curDatabase As Object Dim strColumnsNames As String Dim tblStudents As Object Dim fldColumn As Object ' Get a reference to the current database Set curDatabase = CurrentDb ' Get a reference to a table named Students Set tblStudents = curDatabase.TableDefs("Students") ' Retrieve the name of each column of the table and ' store each name in the strColumnsNames string For Each fldColumn In tblStudents.Fields strColumnsNames = strColumnsNames & fldColumn.Name & ";" Next ' Set the strColumnsNames string as the data source of the combo box cboColumnNames1.RowSource = strColumnsNames ' Select the name of the first column as the default of the combo box cboColumnNames1 = tblStudents.Fields(0).Name cboOrderBy = "Ascending Order" Exit Sub FormOpen_Err: MsgBox "There was an error when opening the form." & vbCrLf & _ "Error #: " & Err.Number & vbCrLf & _ "Description: " & Err.Description & vbCrLf & _ "Please contact the program vendor if " & _ "he is not sleeping at this time." Resume Next End Sub

17. Return to the form and click the Sort By combo box
18. In the Events section of the Properties window, double-click After Update
19. Click its ellipsis button and implement the event as follows:
     

    Private Sub cboColumnNames1_AfterUpdate() Dim strColumnName As String Dim strSortOrder As String ' Get the name of the column from the Sort By combo box strColumnName = cboColumnNames1 ' Sort the records based on the column name from the combo box Me.OrderBy = strColumnName Me.OrderByOn = True ' Set the In combo box to ascending order by default cboSortOrder = "Ascending Order" End Sub

20. Return to the form and click the in combo box
21. In the Events section of the Properties window, double-click After Update
22. Click its ellipsis button and implement the event as follows:
     

    Private Sub cboSortOrder_AfterUpdate() Dim strColumnName As String Dim strSortOrder As String strColumnName = cboColumnNames1 ' Unless the user selects Descending Order... If cboSortOrder = "Descending Order" Then strSortOrder = "DESC" Else ' We will consider that it should be sorted in ascending order strSortOrder = "ASC" End If Me.OrderBy = strColumnName & " " & strSortOrder Me.OrderByOn = True End Sub

23. Return to the form and, from the Toolbox, add a Command Button to the right side Form Footer section
24. When the Command Button Wizard starts, click Cancel
25. Change the button Name to cmdRemoveFilterSort and set its Caption to
    Remove Filter/Sort
26. While the button is still selected, in the Properties window, double click On Click
27. Click its ellipsis button and implement the event as follows:
     

    Private Sub cmdRemoveFilterSort_Click() Me.OrderBy = "StudentID" Me.OrderByOn = True Me.cboColumnNames1 = "StudentID" Me.cboSortOrder = "Ascending Order" End Sub

28. Return to the form and save it
     
    
29. Using the combo box in the Form Footer section, try sorting the records by the LastName is descending order
     
    
30. Remove the sort order
31. Try sorting the records in ascending order based on the date of birth
     
    
32. Notice that the records with no date of birth appear first
33. Close the form

Data filtering consists of isolating particular records based on a condition, also called a criterion or criteria. You start data filtering by selecting one or more columns that hold data you are interested in. This allows you to have access to all values of the table. Then, among the selected columns, instead using all available records, you specify a condition. Only the records that abide by the rule(s) you set would be produced. To specify a condition, you combine the SELECT expression with the WHERE operator. The basic formula to follow is:

SELECT What FROM WhatObject WHERE Expression;

The What factor is used to specify the column(s) whose data would be considered. The WhatObject is the name of the table (or query) that holds the data. The SELECT and the WHERE keywords are required. The Expression is the condition that will set the rule to follow. The Expression is a real expression. It can be made of the name of a column involved in a special conditional statement.

Programmatically Filtering Data

To programmatically perform data filtering on a table, a query, a form, or a report, use its Filter property and assign it the WHERE condition. To apply the filter, access its FilterOn property and assign it a value of True. To remove the filter, assign to False value to its FilterOn property.

Data Filtering With Operators

Consider the following list of registrants of a summer camp:

Imagine that, from this table of students, you want to extract only a list of male students, you would write the condition as Gender = Male. This would be done as follows:

SELECT * FROM Students WHERE Gender="Male";

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 [Students] WHERE [Gender]="Male";

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 Students.FirstName, Students.LastName
FROM Students WHERE Students.Gender="Male";

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

SELECT [Students].[FirstName], [Students].[LastName]
FROM [Students] WHERE [Students].[Gender]="Male";

To perform data filtering on a table, a query, or a form, after displaying the object, you can right-click the value under the column on which you will apply the condition and click Filter By Selection. In this case the equality of a WHERE condition would be applied. Based on this, the above WHERE Gender="Male" condition can be obtained by right-clicking a Male value under the Gender column and clicking Filter By Selection:

The opposite to the equality condition is the not equality. For example, instead of getting a list of male students as above, to get a list of students who are not male, you can write the condition as <>"Male" as follows:

SELECT * FROM Students WHERE Gender<>"Male";

To visually apply the not equality condition on a form, a query, or a report, you can click the value that would be excluded and click Filter Excluding Selection. The Filter By Selection and the Filter Excluding Selection options are used to apply the equality and the inequality conditions when filtering. Actually, a condition is created using the conditional operators we studied in Lesson 7 (but not exactly the conditional statements). To use them visually, right-click the table, the query, or the form, and use the text box of the Filter For option. In the box, type the expression including the column, the operator, and the value. Here is an example:

After typing the expression, press Enter and the condition would be applied. After a few examples of WHERE conditions on a table named Students:

To get a list of students who live in MD, you would use a statement like the following:

SELECT *
FROM Students
WHERE State='MD'

To get a list of girls with their names and email addresses, you would use a statement as follows:

SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
WHERE Gender='Female'
ORDER BY LastName

To use the inequality operator, you can write the following statement:

SELECT FirstName, LastName, Gender
FROM Students
WHERE Gender <> 'Female';

To get a list of students born before June 1988, you would use a statement as follows:

SELECT FirstName, LastName, Gender, DOB
FROM Students
WHERE DOB < '1988-06-01';

To get a list of students born before 1990, you would use a statement as follows:

SELECT FirstName, LastName, DOB, EmailAddress
FROM Students
WHERE DOB <= '1990-01-01'

To get a list of female students by denying those who are male gender, you would write the following statement:

SELECT LastName, FirstName, Gender, DOB
FROM Students
WHERE NOT (Gender = 'Male')

To get a list of students where the record doesn't include an emergency name, you would use a statement as follows:

SELECT LastName, FirstName, Gender, EmergencyName
FROM Students
WHERE EmergencyName IS NULL

To get a list of the students whose records include a number to call in case of emergency, you could use the following statement:

SELECT LastName, FirstName, EmergencyName, EmergencyPhone
FROM Students
WHERE EmergencyPhone IS NOT NULL

After filtering data on a table, a query, or a form, you should reset the object. To do this, you can right-click anywhere in the object and click Remove Filter/Sort.