1. Start Microsoft Access
2. Open the FunDS1 database from Lesson 15

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

SELECT What FROM WhatObject;

The What factor can be the name of a column of a table or query. The WhatObject 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 syntax with the name of the desired column. Here is an example:

SELECT LastName FROM Employees;

To select everything from a table or query, you can use the asterisk as the What factor of our formula. For example, to select all records, you would use the statement as follows:

SELECT * FROM Employees;

Alternatively, you can precede the * with the ALL keyword. Here is an example:

SELECT ALL * FROM Employees;

To consider more than one column in a statement, you can list them in the What factor of our formula, separating them with a comma. The formula to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

SELECT FirstName, LastName, HourlySalary
FROM Employees;

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 [Employees];

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 Employees.FirstName, Employees.LastName
FROM Employees;

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

SELECT [Employees].[FirstName], [Employees].[LastName]
FROM [Employees];

The Visual Basic language is equipped with an impressive library of functions. These functions can also be used in queries 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.

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 data manipulation language (DML) of the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

1. To start a new form, on the Ribbon, click Create
2. In the Forms section, click Form Design
3. Double-click the button at the intersection of the rulers.
   In the Properties window, click Record Source, then click its ellipsis button
4. In the Tables property page of the Show Table dialog box, double-click Manufacturers, StoreItems, Categories, and SubCategories
5. On the Show Table dialog box, click Close
6. From the StoreItems list, double-click ItemNumber and DateEntered
7. From the Manufacturers list, double-click Manufacturer
8. From the Categories list, double-click Category
9. From the SubCategories list, double-click SubCategory
10. From the StoreItems list, double-click ItemName, UnitPrice, and DiscountRate
11. In the lower section of the window, click DiscountRate and press Tab
12. Type DiscountAmount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice * DiscountRate))
13. Press Tab
14. Type AfterDiscount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice - DiscountAmount))

15. Close the Query Builder
16. When asked whether you want to save, click Yes
17. Save the form as InventoryAnalysis
18. Complete the design of the form as follows:

19. Switch the form to Form View to see the result:

20. Switch the form to Design View

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

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.

As you may know from reading books, an index is a list of words that makes it easy to locate information. When it comes to a book, an author or editor can check each chapter to get key words, create a list of those word and put that list at the end of the book. When it comes to a database, you can ask the database engine to use the values of one or more columns as the basis of an index. In other words, you would create an index based on one or more fields and the databse engine would take care of everytihng behind the scenes, which would consist of eventually find the records when a request is made.

To programmatically create an index in SQL, use the following formula:

CREATE [UNIQUE] INDEX IndexName ON ObjectName(Column(s) Options);

The CREATE INDEX expression and the ON keyword are required. Like every object, an index must have a name, in this case ObjectName. You must also specify the table or a query that holds the list whose index you wan to create. You must also specify the column(s) that has(have) the values used for the index. Here is an example of creating an index:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX LocateContractors ON Contractors(ContractorCode);"
End Sub

In most cases, you use only one column as the basis of your index. You can also use more than one column. In this case, in the parentheses of the name of the table, list the columns separated by commas. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX FindContractors " & _
                 "ON Contractors(LastName, FirstName);"
End Sub

By habit or pure tradition, most people start the name of an index with either idx or IDX_.

There are many characteristics to apply to an index.

If you create an index on a column that includes null values, the index may end up with null values. If you want the database engine to exclude null values, add a WITH DISALLOW NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH DISALLOW NULL;"
End Sub

Another option is to ask the databsae engine to ignore null values in the index you are creating. To do this, add a WITH IGNORE NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH IGNORE NULL;"
End Sub

In most cases, you want to make sure that all words used in an index are unique (an index with a repeating word can be confusing). Normally, this is the default characteristic of an index. Still, if you want to enforce it, you can precede the word INDEX with UNIQUE. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode);"
End Sub

In most cases, an index is created on the column(s) used as the primary key. Normally, if your table formally includes a primary key, that (those) column(s) is(are) used as the index. Otherwise, when creating an index, if you want to indicate that it is based on the column(s) used as the primary key, add a WITH PRIMARY clause to it. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH PRIMARY;"
End Sub

To delete an index, use the following forrmula:

DROP INDEX IndexName ON TableName;

Here is an example:

Private Sub cmdDeleteIndex_Click()
    DoCmd.RunSQL "DROP INDEX IDX_Contractors ON Contractors;"
End Sub

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 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. Here is an example:

Private Sub cmdSortByLastName_Click()
    OrderBy = "LastName"
    OrderByOn = True
End Sub

To remove the sorting, access the OrderByOn and set its value to False.

1. On the Ribbon, click Design and, in the Controls section, click More.
   Make sure the Use Control Wizards option is selected .
   In the Controls section of the Ribbon, click the Combo Box and click the left side under the Form Footer bar
2. 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
3. Click under Col1 and type Item Number
4. Press the down arrow key and type Date Entered
5. Complete the list with Manufacturer, Category, Sub-Category, Item Name, Unit Price, and Price After Discount
6. Click Next
7. In the third page of the wizard, accept the first radio button and click Next
8. Change the label to Sort by:
9. Click Finish
10. In the Properties window, change its Name to cbxColumnNames
11. In the Controls section of the Ribbon, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
12. 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
13. Click under Col1 and type Ascending Order
14. Press the down arrow key and type Descending Order
     
    
15. Click Next
16. In the third page of the wizard, accept the first radio button and click Next
17. Change the label to in and click Finish
18. In the Properties window, change the combo box' Name to cbxSortOrder
19. Using the objects in the Controls section of the Ribbon, complete the design of the Footer section as follows:
     

    Control Name Caption Additional Properties Combo Box cbxColumnNames Sort by: Row Source Type: Value List Combo Box cbxSortOrder in   Button cmdRemoveFilterSort Remove Filter/Sort

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 Employees. 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 Employees 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 Employees table based on the alphabetical order of the LastName column:

SELECT * FROM Employees 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 Employees ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM Employees 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 Employees ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM Employees 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.

1. Double-click the Sort By combo box to access its properties and click the Events tab
2. Double-click After Update, then click its ellipsis button
3. Implement the event as follows:
   

   Option Compare Database
   Option Explicit
   
   Private strColumnName As String
   Private strSortOrder As String
   
   Private Sub cbxColumnNames_AfterUpdate()
   On Error GoTo cbxColumnNames_AfterUpdate_Error
     
       ' Get the string selected in the Sort By combo box
       ' and find its equivalent column name
       If cbxColumnNames = "Item Number" Then
           strColumnName = "ItemNumber"
       ElseIf cbxColumnNames = "Date Entered" Then
           strColumnName = "DateEntered"
       ElseIf cbxColumnNames = "Manufacturer" Then
           strColumnName = "Manufacturer"
       ElseIf cbxColumnNames = "Category" Then
           strColumnName = "Category"
       ElseIf cbxColumnNames = "Sub-Category" Then
           strColumnName = "SubCategory"
       ElseIf cbxColumnNames = "Item Name" Then
           strColumnName = "ItemName"
       ElseIf cbxColumnNames = "Unit Price" Then
           strColumnName = "UnitPrice"
       ElseIf cbxColumnNames = "Price After Discount" Then
           strColumnName = "AfterDiscount"
       Else
           strColumnName = ""
       End If
       
       ' 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
       cbxSortOrder = "Ascending Order"
   
       Exit Sub
       
   cbxColumnNames_AfterUpdate_Error:
       MsgBox "There was an error when trying to sort the records. " & _
              "Please report the error as follows." & 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

4. In the Object combo box, select cbxSortOrder
5. In the Procedure combo box, select AfterUpdate
6. Implement the event as follows:
   

   Private Sub cbxSortOrder_AfterUpdate()
   On Error GoTo cbxSortOrder_AfterUpdate_Error
       
       ' Unless the user selects Descending Order...
       If cbxSortOrder = "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
       Exit Sub
       
   cbxSortOrder_AfterUpdate_Error:
       MsgBox "There was an error when trying to sort the records. " & _
              "Please report the error as follows." & 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

7. In the Object combo box, select cmdRemoveFilterSort
8. Implement the event as follows:
   

   Private Sub cmdRemoveFilterSort_Click()
       Me.OrderBy = ""
       Me.OrderByOn = False
       
       Me.cbxColumnNames = "Item Number"
       Me.cbxSortOrder = "Ascending Order"
   End Sub

9. Return to the form and save it
10. Using the combo box in the Form Footer section, try sorting the records by the Manufacturer

11. Click the Remove Filter/Sort button
12. Sort the records by Unit Price in descending order

13. Close the form
14. When asked whether you want to save, click Yes

If you create a SQL expression what involves more than one table, you can use one of the fields to sort the records. To do this, after the join expression, add your sort clause. Here is an example:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "ORDER BY Employees.LastName;"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

Of course, you can use any column in the SQL statement. Here is an example that uses a column of the second table:

Private Sub cmdUseJoin_Click()
    RecordSource = "SELECT Employees.EmplNbr, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Departments.Department " & _
                   "FROM Employees " & _
                   "INNER JOIN Departments " & _
                   "      ON Employees.DeptCode = Departments.DeptCode " & _
                   "ORDER BY Departments.Department;"
                
    txtEmployeeNumber.ControlSource = "Employees.EmplNbr"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub