Home

Data Analysis

 

Techniques of Data Analysis

 

Introduction

In the previous lessons, we introduced data filtering as a technique of creating sub-lists of items based on existing data. Data analysis consists of examining the records of a table, an already existing query, or a form, to isolate records that respond to one or more criteria. To assist you with this, Microsoft Access provides all the (visual) tools you need. These include filtering by form and logical junction operators. Consider the following list of videos:

 

Filtering By Form

When performing data analysis on a table, a form, or a query, one of the available options you can use is referred to as filtering by form. To use it on a table or a query, on the main menu, you can click Records -> Filter -> Filter By Form. Alternatively, on the toolbar, you can click the Filter By Form button. This action empties the table of all values and creates a combo box under each column header:

To select a value from a column, click the arrow of the combo box under that column and select the desired value. For example, from the above table, suppose you want to see a list of videos directed by Adrian Lynn. You can select this name from the combo box under Director:

After making the selection on a table, a query or a form, to see the result, you can click the Apply Filter button on the Form View toolbar . Here is an example:

Notice that the string on the status bar displays Filtered.

This technique of performing the filtering can also by applied to a form. Just as done for a table or a query, you can right-click a form and click Filter By Form:

While a form is displaying in Form View, you can also click the Filter By Form button on the form View toolbar. You can also use the main menu as mentioned above. When you do this, two tabs would appear in the bottom left section of the form:

To select a value, click its control first. This would display a button with a down-pointing arrow on the right side of the control. You can click that button to select the desired value. For example, from the above list of videos, suppose you want to see the list of movies that were released in 1991. You can click the arrow of (c) Year and select 1991:

When you start the Filter By Form, the form fires the On Filter event. After selecting the value, you can right-click the form and click Apply Filter/Sort to see the result. When you apply a filter on a form, it fires the On Apply Filter event.

Advanced Filtering

To make data analysis even friendlier, whether on a table or a form, you can use a window that resembles the Select Query. To display it, after opening a table in Datasheet View or a form in Form View, on the main menu, click Record -> Filter -> Advanced Filter...

You can use this window approximately as you would the Select Query: you select the fields in the upper section and set the criteria, conditions, or sorting in the lower section. For example, to see the list of movies rated R from the above table, in the upper section of the Filter window, you can double-click Rating. Then, in the Criteria box corresponding to the Rating, type "R".

If you use the Advanced Filter on a form, it fires an On Filter event

After setting the criteria, you can right-click an empty area in the upper section of the window and click Apply Filter/Sort. The table in the background would come up and show the result:

If you apply a filter on a form, it fires the On Apply Filter event.

 

 

Practical Learning Practical Learning: Introducing Data Analysis

  1. Start Microsoft Access and create a blank database named Solas Property Rental
  2. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View and complete the table as follows:
     
    Field Name Data Type Caption Other Properties
    PropertyTypeID AutoNumber Property Type ID Primary Key
    PropertyType   Property Type  
    Description Memo    
  3. Save the table as PropertyTypes and switch it to Datasheet View
  4. Create the records as follows:
     
    Property Type Description
    Apartment In a one-level or a multi-level building, an apartment occupies a section or area on a level. It is made of one or more bedrooms and other options.
    Townhouse A townhouse is a multi-level house that is attached to one (on either left or right) other townhouse or two (on left and right) other townhouses. A townhouse also has a front and a back yards that belong to it.
    Single Family A single-family is a house that stands on its own, that is, it is not attached to another. Unlike a townhouse, the area around (including both sides) the single-family house belongs to it.
  5. Close the table
  6. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View
  7. Under Field Name, type PropertyID and set its Data Type to AutoNumber
  8. Right-click it and click Primary Key
  9. Set its Caption to Property ID
  10. Save the table as Properties
  11. Set the next empty Field Name to DateFileCreated
  12. In the next empty Field Name, type PropertyTypeID and set its Data Type to Lookup Wizard...
  13. In the first page of the wizard, accept the first radio button and click Next
  14. In the second page of the wizard, click PropertyTypes and click Next
  15. In the Available Fields list, double-click PropertyType and click Next
  16. Click Next and click Finish
  17. Delete the 0 in the Default value
  18. Complete the table as follows:
     
    Field Name Data Type Field Size Format Caption Default Value Other Properties
    PropertyID AutoNumber     Property ID   Primary Key
    DateFileCreated Date/Time   dd-mmm-yyyy Date File Created =Date() Input Mask: 00\->L<LL\-0000;0;_
    PropertTypeID       Property Type    
    PropertyCode   10   Prop Code   Indexed: Yes (No Duplicates)
    Address   80        
    PropertyNumber   20   Prop #    
    City         "Silver Spring"  
    State   2 <   "MD" Input Mask: LL
    ZIPCode       ZIP Code    
    Locality            
    Bedrooms Number Byte     1  
    Bathrooms Number Byte     1  
    FinishedBasement Yes/No     Finished Basement?    
    HasMicrowaveOven Yes/No     Has Microwave?    
    HasAlarmSystem Yes/No     Has Alarm?    
    HasWirelessInternet Yes/No     Has Wireless Internet?    
    RentalRate Number Double Fixed Rental Rate    
    Description Memo          
  19. Right-click RentalRate and click Insert Rows
  20. Set the new Field Name to OccupiedVacant and set its Data Type to Lookup Wizard
  21. When the wizard starts, in the first page, select the second radio button and click Next
  22. Click Under Col1 and type Occupied
  23. Press the down arrow key and type Vacant
  24. Click Next and click Finish
  25. Set the Caption to Occupied/Vacant?
  26. Set the Default Value to "Vacant"
  27. Save the table and close it
  28. On the Database toolbar, click the arrow of the New Object button and click AutoForm
  29. Save the form as Properties and change its design as follows:
     
  30. Save the form and switch it to Form View
  31. Create a few records
  32. Close the table
  33. Open the Solas Property Rental database you started in Lesson 17
  34. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View
  35. Set the properties of the first field as follows:
    Field Name: TenantID
    Data Type: AutoNumber
    Caption: Tenant ID
  36. Right-click it and click Primary Key
  37. Set the properties of the second field as follows:
    Field Name: ApplicationDate
    Data Type: Date/Time
    Format: dd-mmm-yyyy
    Input Mask: 99\->L<LL\-0000;0;_
    Caption: Application Date
  38. Set the properties of the third field as follows:
    Field Name: FileNumber
    Field Size: 10
  39. Save the table as Tenants
  40. Set the fourth field's properties as follows:
    Field Name:  HOHName (HOH stands for head of household)
    Required: Yes
  41. Set the third Field Name to HOHTitle and set its Data Type to Lookup Wizard
  42. When the wizard starts, in the first page, select the second radio button and click Next
  43. Click Under Col1 and complete each cell with the following strings: Individual, Roommate, Husband, Wife, Father, Mother, Son, Daughter, and Parent (Miscellaneous)
     
  44. Click Next and click Finish
  45. Set the second field's properties as follows:
    Field Name: HOHPhoneNumber
    Required: Yes
  46. Set the fourth Field Name to HOHMaritalStatus and set its Data Type to Lookup Wizard
  47. When the wizard starts, in the first page, select the second radio button and click Next
  48. Click Under Col1 and complete each cell with the following strings: Single, Married, Divorced, Separated, and Widow
     
  49. Click Next and click Finish
  50. Click the gray box on the left side of HOHName
  51. Press and hold Shift
  52. Click the gray box on the left side of HOHMaritalStatus and release Shift
     
  53. Press Ctrl + C to copy
  54. Click the first empty field (under HOHMaritalStatus and press Ctrl + V to paste
  55. Change the second HOHName to CHOHName (CHOH stands for co-head of household) and set its Required property to No
  56. Change the second HOHTitle to CHOHTitle
  57. Change the second HOHContactNumber to CHOHPhoneNumber and set its Required property to No
  58. Change the second HOHMaritalStatus to CHOHMaritalStatus
  59. Click the first empty cell under Field Name and type TenantsRelationship
  60. Set its Data Type to Lookup Wizard
  61. When the wizard starts, in the first page, select the second radio button and click Next
  62. Click Under Col1 and complete it as follows: Married, Dating, Roommates, and Related
     
  63. Click Next and click Finish
  64. Set the next empty fields properties as follows:
    Field Name: NumberOfAdults
    Data Type: Number
    Field Size: Byte
    Caption: Adults
    Required: Yes
  65. Set the next empty fields properties as follows:
    Field Name: NumberOfChildren
    Data Type: Number
    Field Size: Byte
    Caption: Children
    Required: Yes
  66. Set the next empty fields properties as follows:
    Field Name: Requests
    Field Size: 150
  67. Right-click the first empty field and click Build
  68. In the dialog box, double-click Notes
     
  69. Save the table and switch it to Datasheet View
  70. Create a few sample tenants
  71. Close the table
  72. In the Database window, click Queries
  73. To create a new query, on the main menu, click Insert -> Query
  74. In the New Query dialog box, make sure Design View is selected and click OK
  75. In the Show Table dialog box, double-click PropertyTypes and Properties
  76. Click Close
  77. In the PropertyTypes list, double-click PropertyType
  78. In the Properties list, double-click Address, Locality, Bedrooms, Bathrooms, OccupiedVacant, and RentalRate
     
  79. To see the result, switch the query to Datasheet View
     
  80. Save the query as Inventory of Properties and switch it to Design View

Logical Conjunction: The AND Operator

 

Introduction to Logical Junctions

In the previous lessons, we stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test a condition that depends on another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Suppose you get an assignment to create a database used for a company that rents apartments to customers. The operations would consist of registering customers who become tenants and allocating them an apartment based on their needs and the availability.

 

Practical Learning Practical Learning: Introducing the Logical Junctions

  1. To create a list of properties that have 2 bedrooms, in the Criteria box of the Bedrooms column, type 2
     
  2. Switch the query to Datasheet View to see the result
  3. Switch it back to Design View
  4. Delete the 2 under the Bedrooms column
  5. To see a list of available properties, in the Criteria box of the OccupiedVacant column, type "Vacant"
     
  6. Switch the query to Datasheet View to see the result
  7. After viewing the query, switch it back to Design View
  8. Minimize the query

Introduction to Logical Conjunctions

For a property rental company, if a customer who is a husband and father of one comes to the rental office and states that he wants a 2-bedroom property, you would check the listing of the properties and find out that you have quite a few of them. To respond to this request, you must examine two conditions for each apartment:

  • The property has two bedrooms
  • The property is available

When preparing your database prior to seeing the customers, you can start by building one query that lists only the properties that have two bedrooms:

The second condition requires that the property be available. From our database, a property is available if its OccupiedVacant field is set to Vacant:

From these two results, notice that there is no relationship between the fact that a property has 2 bedrooms and its being vacant. To rent a property for our customer, it must have two bedrooms. We can create a Boolean truth table as follows:

The Property has 2 Bedrooms The Property is Vacant Result
True    
False    

The property to rent must have two bedrooms. This excludes the 1, 3, and more-bedroom properties. This means that if the property has a number of bedrooms other than 2, whether it is available or not, it cannot be rented to the current customer:

The Property has 2 Bedrooms The Property is Vacant Result
True   Don't Know
False Regardless False

Once we have a list of properties that have two bedrooms, now, let's consider the available properties. If a property has two bedrooms and it is available, then it can be rented to the current customer:

The Property has 2 Bedrooms The Property is Vacant Result
True True True
False Regardless False

If the property has 1, 3 or more bedrooms but not 2, whether it is available or not, it cannot be rented to the current customer:

The Property has 2 Bedrooms The Property is Vacant Result
True True True
Regardless False False
False Regardless False

In the same way, if both conditions are false (the property has 1 or more than 2 bedrooms and in fact it is not available), the result is false (the property cannot be rented to the current customer):

The Property has 2 Bedrooms The Property is Vacant Result
True True True
Regardless False False
False False False
False Regardless False

This demonstrates that a property can be rented to the current customer only if BOTH conditions are met: The property has two bedrooms AND it is available. This type of condition is referred to as logical conjunction.

Logical Conjunction in Tables, Queries, and Forms

As mentioned in previous lessons and sections, before performing data analysis, first display the table or query in Datasheet View, or the form in Form View. After displaying the object, you can use Filter By Form as we saw earlier. To perform logical conjunction, select the values of two (or more) columns in the same row. For example, from our table of videos, imagine that you want to see the videos that were released in 1994 but only those that are rated PG-13. After displaying the Filter By Form window, in the © Year column, you can select 1994 and, in the Rating column, you select PG-13:

After setting the criteria, you can click the Apply Filter button on the toolbar to see the result:

After viewing the result, to restore the table, you can right-click it and click Remove Filter/Sort. This technique of performing logical conjunction on a table is the same used on a query in Datasheet View.

To perform logical conjunction on a form, after setting the Filter By Form, click each of the desired controls and select the necessary value. For example, the above criteria can be set as follows:

When you do this, the form fires the On Filter event. After setting the criteria, you can right-click the form and click Apply Filter. When you apply the filter, the form fires an On Apply Filter event.

Practical Learning Practical Learning: Applying the AND Operator in a Form

  1. In the Database window, click Forms.
    Right-click Properties and click Save As...
  2. Type AvailableApartments as the name of the copied form and click OK
  3. Right-click the AvailableApartments form and click Design View
  4. Delete the PropertyID text box, the PropertyTypeID, and the OccupiedVacant combo boxes
  5. Design the form as follows:
     
  6. Access its Properties window and click Event
  7. In the Event tab, double-click On Open and click its ellipsis button
  8. Implement the event as follows:
     
    Private Sub Form_Open(Cancel As Integer)
        Filter = "(PropertyTypeID = 1) AND (OccupiedVacant = 'Vacant')"
        FilterOn = True
    End Sub
  9. Return to Microsoft Access and switch the form to Form View to preview it
     
  10. Save and close the form

Logical Conjunction in SQL Statements

To express the logical conjunction, the SQL uses the AND operator. To use it visually when creating a query, after selecting the columns, in the lower section of the window, click the Criteria box corresponding to each column that will be involved in the conjunction. For example, if you want to create a list of movies released in 1994 but rated R, type the appropriate value in the Criteria boxes of the columns. Here is an example:

To manually create a logical conjunction in SQL, type one condition on the left and the other condition on the right sides of the AND keyword using the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject factors are the same we have used so far. The AND keyword is the new one. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case, the WHERE operator resembles the If conditional statement. The Condition1 is the first that would be examined. Remember that, from our discussion earlier, if the first condition is false, the whole statement is rendered false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined. Based on this, the SQL statement used to get a list of movies released in 1994 but rated PG-13 is:

SELECT Title, Director, CopyrightYear, Rating
FROM Videos
WHERE CopyrightYear = "1994" AND Rating = "PG-13";

The equivalent SQL statement of the above query in SQL as written by Microsoft Access is:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (((Videos.CopyrightYear)="1994") AND ((Videos.Rating)="PG-13"));

Practical Learning Practical Learning: Applying the AND Operator in a Query

  1. From the lower left corner, bring back the query
  2. To create a list of available properties that have 2 bedrooms, in the Criteria box of the Bedrooms column, type 2 and leave the "Vacant" value for the OccupiedVacant column:
     
  3. To save the query, on the main menu, click File -> Save As...
  4. Type Available 2-Bedroom Properties and press Enter
  5. Switch the query to Datasheet View to see the result
     
  6. Close the query

Logical Disjunction: The OR Operator

 

Introduction

Suppose a customer who is shopping, but is not ready, for a rental property comes to the office and states that she is considering renting. You show her the catalog of properties with apartments, townhouses, and single-family homes. At first glance, the customer says she cannot rent an apartment. The other two options are the townhouse or the single family. To prepare the new list, you must create a query that considers only these two options. Before building the query, you can state the following:

  • The property is a townhouse
  • The property is a single-family

We can start a truth table as follows:

The Property is a Townhouse The Property is a Single Family Result
     

To continue with this table, we can check each property. If the property is a townhouse, it is considered valid for our customer:

The Property is a Townhouse The Property is a Single Family Result
True Regardless True

It a property is not a townhouse. Then, we consider the next property. If the next property is a single family, it is also valid:

The Property is a Townhouse The Property is a Single Family Result
True Regardless True
Regardless True True

When building this table, we would skip a property only if it is neither a townhouse nor a single family. In Boolean algebra, this means that if both conditions are true, the whole statement is also true. The whole statement is false only if both conditions are false. This can be resumed as follows:

Condition 1 Condition 2 Result
True True True
True False True
False True True
False False False

This type of statement is referred to as logical disjunction. The logical disjunction is expressed in Microsoft Access and in SQL with the OR operator.

Logical Disjunction in Tables, Queries, and Forms

Once again, consider our list of videos:

Imagine that you want to see a list of the movies that either were released in 1994 or are rated PG-13. If a movie was released in 1994, the whole statement is true, regardless of the second condition. If a movie of our collection is rated PG-13, the whole statement is true even if the movie was released in a year other than 1994. The statement is also true if both conditions are met. On the other hand, if a movie was released in a year other than 1994 and it is not rated PG-13, then the whole statement is false.

As done for the logical conjunction, before applying a logical disjunction to a table, a query in Datasheet View or a form in Form View, first display it in Filter By Form:

If doing this on a form, it would fire the On Filter event. While in the Look For tab, click the box under the column that would be used to set the first condition, and select the desired value:

After selecting the value of the first condition, click the Or tab in the lower left section of the window. Click the arrow of the combo box under the column that would be used as the second condition:

After setting the criteria, click the Apply Filter button:

 

Logical Disjunction in SQL Statements

When creating a query in Design View, the window provides two convenient sections for the first and the second conditions. To set the first condition, click the Criteria box corresponding to its column and type the operation. To set the second condition, click the Or box corresponding to its column and enter the necessary condition. Here is an example:

Practical Learning Practical Learning: Applying the OR Operator in a Query

  1. In the Queries section of the Database window, right-click Inventory of Properties and click Design View
  2. To create a list of townhouse and single-family properties, in the Criteria box of PropertyType, type "Townhouse"
  3. Press the down arrow key and, in the Or box of the PropertyType, type "Single Family"
     
  4. To save the query, on the main menu, click File -> Save As...
  5. Type Townhouse and Single Family Homes as the name of the query and press Enter
  6. Switch the query to Datasheet View to see the result
     
  7. Close the query

Other Logical Operators on Queries

 

Range Test: BETWEEN

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use the BETWEEN operator. The BETWEEN operator is usually combined with AND to get a list of records between two values. The basic formula of this operator is:

Expression BETWEEN Start AND End

The Expression placeholder of our formula is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End. As an example, imagine that, from a list of employees, you want to consider only records from EmployeeID = 4 to EmployeeID = 7. You would write the statement in the Criteria field as follows:

The corresponding SQL statement is:

SELECT 	Employees.EmployeeID, Employees.DateHired,
	Employees.FirstName, Employees.LastName
FROM Employees
WHERE (((Employees.EmployeeID) Between 4 And 7));

This would produce:

This or That: IN

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator. The IN operator is a type of various OR operators. It follows this formula:

IN(Expression1, Expression2, Expression_n)

Each Expression factor can be one of the values of a column. This is equivalent to Expression1 OR Expression2 OR Expression3, etc. As an example, from our list of employees, imagine that you want to create a list of employees who either work at the corporate office or in public relations. If you are visually creating the list, in the Criteria field of the Department column, you can type

IN("Corporate", "Public Relations")

Here is an example:

The corresponding SQL statement is:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.Department) In ("Corporate","Public Relations")));

This would produce:

SQL provides many and many other operators that we have not reviewed here.

Practical Learning Practical Learning: Using the IN Operator in a Query

  1. In the Queries section of the Database window, right-click Inventory of Properties and click Design View
  2. To create a list of apartments and townhouses, in the Criteria box of PropertyType, type
    IN (Apartment, Townhouse) and press Enter
      
  3. Switch the query to Datasheet View to see the result
  4. To save the query, on the main menu, click File -> Save As...
  5. Type Apartments and Townhouses as the name of the query and press Enter
  6. Close the query

 


Previous Copyright Yevol, 2007 Next