The Queries of a Database: Data Selection

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 first 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 Selection Using SQL

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

Data Selection For a Form

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