Techniques of Data Analysis
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:
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.
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.
|Field Name||Data Type||Caption||Other Properties|
|PropertyTypeID||AutoNumber||Property Type ID||Primary Key|
|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.|
|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;_|
|PropertyCode||10||Prop Code||Indexed: Yes (No Duplicates)|
|State||2||<||"MD"||Input Mask: LL|
|HasWirelessInternet||Yes/No||Has Wireless Internet?|
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:
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 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:
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:
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:
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):
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.
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.
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"));
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:
We can start a truth table as follows:
To continue with this table, we can check each property. If the property is a townhouse, it is considered valid for our customer:
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:
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:
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.
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:
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:
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:
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.
|Previous||Copyright © Yevol, 2007||Next|