To Lecture Notes

LSP 121 -- 10/26/10

Review Questions

1. Why is using Filter usually better than using Find to locate data in an Access table?

   Ans: When using find, the format of the field value must be exact, for example 90.0 instead of 90. If the format is not exact, the value might not be found. However, Find is easier to use than a filter when the filter involves many columns.

2. Give a reason reason why you might prefer the Text datatype over the Number datatype for a column.

   Ans: Values of a field containing Text values can be any string of characters. For example "34544-1002" might be entered as a zip code. This value would not be possible for a Number datatype.

3. Give a reason why you might prefer the Number datatype over the Text datatype for a column.

   Ans: One cannot do arithmetic on Text values. Also, numbers will not sort in numerical order if they are Text values.

4. In the StateTemperatures Example that you used in Activity 2, how would you create a filter to show all of the rows where the average temperature in January was greater than or equal to 88 degrees or less than or equal to 0 degrees?

   Ans: Use this Advanced Filter/Sort:
   

   Field:	January
   Sort:
   Criteria:	>=88
   or:	<=0

5. True or False. When typing a column name into a filter, put the name in double quotes.

   Ans: False, you put values of a Text field in double quotes. You put a column name in square brackets like this [Age] or [PersonData.Age].

6. Define these terms:
   Repeated Groups   Data Anomaly   Data Normalization   Foreign Key   Relationship

   Ans:
   Repeated Groups   A fields that contains more than one data item. Repeated groups are not allowed in a relational database table.

   Data Anomaly A table containing a column that depends entirely on different column that is not the primary key. Each column in a table must depend only on the primary key and no other columns

   Data Normalization   Splitting a table into two or more tables to eliminate either repeated groups or data anomalies.

   Foreign Key   A column in one table that contains a primary key from another table.

   Relationship   Specifies one-many or many-many relationships between two tables by specifying the foreign keys and primary keys involved.

7. Describe the process of normalizing a table with repeated groups.

   Ans: (a) Keep the data that is not part of a repeated group in the original table. (b) Move the repeated group to a new table. (c) Choose primary keys in the original and new tables. Add new id variables, if necessary, for primary keys. (d) Add the primary key of the original table to the new table as a foreign key.

Access Queries

• A query is like a filter with extra capabilities.
  1. Queries can receive names and be saved like tables.

  2. Queries can work with table joins in addition to ordinary tables.

  3. Queries can restrict which columns appear (unlike a filter where all columns remain).

• A query window contains the same information as a filter window: Field, Sort, Criteria. In addition, it contains the table from which each field comes, and a check box indicating whether to show the variable in the final query output table.

• An aggregate query returns summary information, like a sum, average, minimum or maximum, instead of listing individuals.

• Use the Summation Button to change a select query to an aggregate query.

• Practice Queries:
  1. Create the following queries for sports-team.accdb. First set up the appropriate primary keys and relationships.
     1. Which players have a wife named Tricia?

        Ans:
        

        Field:	First Name	Last Name	Relationship	DependentName
        Table:	Athletes	Athletes	Dependents	Dependents
        Sort:
        Show:	Yes	Yes	No	No
        Criteria:			"Wife"	"Tricia"
        or:

     2. What are the phone numbers and phone types Stan Ironman?

        Ans:
        

        Field:	First Name	Last Name	PhoneType	PhoneNumber
        Table:	Athletes	Athletes	AthletePhoneNumbers	AthletePhoneNumbers
        Sort:
        Show:	No	No	Yes	Yes
        Criteria:	"Stan"	"Ironman"
        or:

     3. Who are the children of Leroy Slaughter?

        Ans:
        

        Field:	First Name	Last Name	Relationship	DependentName
        Table:	Athletes	Athletes	Dependents	Dependents
        Sort:
        Show:	No	No	No	Yes
        Criteria:	"Leroy"	"Slaughter"	"Child"
        or:

     4. Who are the players that make more than 1 million dollars?

        Ans:
        

        Field:	First Name	Last Name	Salary
        Table:	Athletes	Athletes	Athletes
        Sort:
        Show:	Yes	Yes	No
        Criteria:			>10000000
        or:

     5. What are the phone numbers of all the dependents of Aaron Fleetfoot?

        Ans:
        

        Field:	First Name	Last Name	PhoneNumber
        Table:	Athletes	Athletes	DependentPhoneNumbers
        Sort:
        Show:	No	No	Yes
        Criteria:	"Aaron"	"Fleetfoot"
        or:

Activity 10

• Work on Activity 10.

• For Activity 10a, turn in your Access database with the queries saved as Query1, Query2, etc.

• The responses to the questions in Activity 10b require three parts:
  1. The Advanced Query/Sort that you used (enter as a Word table).

  2. The table produced by the Advanced Query/Sort (copy from Access and paste into your table).

  3. The actual answer to the question (type into your Word document).