To Lecture Notes

LSP 121 -- 10/19/10

Review Questions

1. Define these terms:
   Database:   Ans: A file containing one or more tables. Names of Access databases have the extension .accdb.

   Row:  Ans: Contains all the data related to an observation such as a person, airline ticket, transaction, listing, etc.

   Column:   Ans: Contains all of the values in the rows for a specific data item.

   Field:   Ans: A single value in a table. The intersection of a row and a column.

   Filter:   Ans: Using specified criteria to select a subset of the rows in the table.

2. How is Access similar to SPSS?

   Ans: Both Access and SPSS present data in the form of tables. SPSS uses different terminology than Access:

   Access Term	SPSS Term
   Table	Database
   Row	Observation
   Column	Variable
   Field	Data Value

3. What datatypes do we know about for columns?

   Ans: Text and Number. Other useful datatypes are Yes/No, Data/Time, and Currency.

4. How do you create and apply a filter in Access?

   Ans: To obtain the Filter Grid: Home Tab >> Advanced >> Advanced Filter/Sort. To apply a filter or sort: Home Tab >> Advanced >> Apply Filter/Sort. You can also right click above the Filter/Sort grid and select Apply Filter/Sort.

5. What kinds of sorting are available in Access?

   Ans: Ascending, Descending, Not Sorted.

6. What is the result of applying this advanced Filter/Sort?

   Field:	Gender	Age	Name
   Sort:	Ascending	Descending
   Criteria:
   or:

   to this table:

   PersonData
   Name	Gender	Age
   Judy	F	39
   Michael	M	29
   Alice	F	32
   Megan	F	39
   Pat	M	41
   Alan	M	32
   Gretchen	F	52

   This is the example database persons2.accdb.

   Ans: The rows are sorted by gender. Within the rows with gender F, the rows are sorted by descending age.

7. What is the result of applying this advanced Filter/Sort?

   Field:	Gender	Age	Name
   Sort:		Descending	Ascending
   Criteria:	"F"
   or:

   to the preceding table?

   Ans: All rows with gender F are selected. Those rows are sorted by descending age. For those rows tied with the same age, they are sorted by ascending name.

8. How would you obtain the rows of all persons with age
   less than 30?

   Ans:

   Field:	Age
   Sort:
   Criteria:	<30
   or:

   greater than 30?

   Ans:

   Field:	Age
   Sort:
   Criteria:	>30
   or:

   between 35 and 45?

   Ans:

   Field:	Age
   Sort:
   Criteria:	>35 and <45
   or:

   less than 30 or greater than 40?

   Ans:

   Field:	Age
   Sort:
   Criteria:	<30
   or:	>40

Primary Keys

• A primary key is a field that uniquely identifies a row as in this PersonData table:

  PersonData
  Name	Gender	Age
  Judy	F	27
  Michael	M	29
  Alice	F	32

  Name is the primary key.

  mpor

• For directions on how to set a primary key in Access, see the Access Turorial, Section G.

• If the values of the primary key are not unique, Access will show this error message:
  The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

• See Rule 1 of the Three Database Rules in the Access Tutorial.

• Sometimes, a primary key can be a combination of two or more fields as in this table.

  PersonData
  LastName	FirstName	Gender	Age
  Chen	Judy	F	27
  Cooper	Alice	M	59
  Smith	Alice	F	32
  Suarez	Michael	M	29
  Smith	Stan	M	63

  Here LastName and FirstName together form the primary key. Here is the database:

  persons3.accdb database.

• To designate multiple fields for a primary key, don't forget to hold the down control key as you right click and select each column of the Primary Key.

• For each of these tables, identify a possible primary key:
  AirlineTickets, Passports, TelevisionShowListings.

Searching

• Searching a table means find the first occurrence of a field value in a table.

• In the PersonData table of the students.accdb database, search for the student with name Bill.

• For directions on how to search in Access, see the Access Turorial, Section D.

Importing Data into a Table

• Importing data into Access means taking non-Access data and creating an Access table that contains that data.

• See the Access Tutorial, Section H, for details on Importing.

• Example:   Import a table named NewPersonData2 intp persons4.accdb from this Excel file persons.xls.

• Do Activity 9.

• Finish Activity 8.