Home

Introduction to 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 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, to see the result, you can click the Apply Filter button on the toolbar:

Notice that the string on the status bar displays Filtered.

This technique of filtering by form can also be applied in the exact same way on a query.

To filter by form on a form, you can use the main menu or the button as mentioned above, or you can right-click the form and click Filter By 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:

After selecting the value, you can right-click the form and click Apply Filter/Sort to see the result.

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". 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:
 

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 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   Property Code   Indexed: Yes (No Duplicates)
    Address   80        
    PropertyNumber   20   Property #    
    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 switch it to Datasheet View
  28. Create a few records
  29. Close the table
  30. To create a new table, in the Tables section of the Database window, double-click Create Table In Design View
  31. Set the properties of the first field as follows:
    Field Name: TenantID
    Data Type: AutoNumber
    Caption: Tenant ID
  32. Right-click it and click Primary Key
  33. 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
  34. Set the properties of the third field as follows:
    Field Name: FileNumber
    Field Size: 10
  35. Save the table as Tenants
  36. Set the second field's properties as follows:
    Field Name:  HOHName (HOH stands for head of household)
    Required: Yes
  37. Set the third Field Name to HOHTitle and set its Data Type to Lookup Wizard
  38. When the wizard starts, in the first page, select the second radio button and click Next
  39. Click Under Col1 and complete each cell with the following strings: Individual, Roommate, Husband, Wife, Father, Mother, Son, Daughter, and Parent (Miscellaneous)
     
  40. Click Next and click Finish
  41. Set the second field's properties as follows:
    Field Name: HOHPhoneNumber
    Required: Yes
  42. Set the fourth Field Name to HOHMaritalStatus and set its Data Type to Lookup Wizard
  43. When the wizard starts, in the first page, select the second radio button and click Next
  44. Click Under Col1 and complete each cell with the following strings: Single, Married, Divorced, Separated, and Widow
     
  45. Click Next and click Finish
  46. Click the gray box on the left side of HOHName
  47. Press and hold Shift
  48. Click the gray box on the left side of HOHMaritalStatus and release Shift
     
  49. Press Ctrl + C to copy
  50. Click the first empty field (under HOHMaritalStatus and press Ctrl + V to paste
  51. Change the second HOHName to CHOHName (CHOH stands for co-head of household) and set its Required property to No
  52. Change the second HOHTitle to CHOHTitle
  53. Change the second HOHContactNumber to CHOHPhoneNumber and set its Required property to No
  54. Change the second HOHMaritalStatus to CHOHMaritalStatus
  55. Click the first empty cell under Field Name and type TenantsRelationship
  56. Set its Data Type to Lookup Wizard
  57. When the wizard starts, in the first page, select the second radio button and click Next
  58. Click Under Col1 and complete it as follows: Married, Dating, Roommates, and Related
     
  59. Click Next and click Finish
  60. Set the next empty fields properties as follows:
    Field Name: NumberOfAdults
    Data Type: Number
    Field Size: Byte
    Caption: Adults
    Required: Yes
  61. Set the next empty fields properties as follows:
    Field Name: NumberOfChildren
    Data Type: Number
    Field Size: Byte
    Caption: Children
    Required: Yes
  62. Set the next empty fields properties as follows:
    Field Name: Requests
    Field Size: 150
  63. Right-click the first empty field and click Build
  64. In the dialog box, double-click Notes
     
  65. Save the table and switch it to Datasheet View
  66. Create a few sample tenants
  67. Close the table
  68. In the Database window, click Queries
  69. To create a new query, on the main menu, click Insert -> Query
  70. In the New Query dialog box, make sure Design View is selected and click OK
  71. In the Show Table dialog box, double-click PropertyTypes and Properties
  72. Click Close
  73. In the PropertyTypes list, double-click PropertyType
  74. In the Properties list, double-click Address, Locality, Bedrooms, Bathrooms, OccupiedVacant, and RentalRate
     
  75. To see the result, switch the query to Datasheet View
     
  76. Save the query as Inventory of Properties and switch it to Design View
 
 
Previous Copyright © 2005-2016, FunctionX Next