Querying a Database
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.
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:
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.
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.
Data select in the SQL consists of using the SELECT keyword. The primary formula to follow is:
SELECT What FROM WhatObject;
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.
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.
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:
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.
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.
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.
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.
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.
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.
|Previous||Copyright © Yevol, 2007||Next|