To Lecture Notes

ISP 121 -- 4/7/08

Reference in Course Documents:   Access Tutorial

Review Questions

1. Define these terms:   Row, Column, Field, Primary Key, Filter
   Ans: Row:   Contains all the data related to an observation
   such as a person, airline ticket, transaction, listing, etc.
   Column:   Contains all of the values in the rows for a specific data item.
   Field:   The intersection of a row and a column.
   Primary Key:   Two or more fields that uniquely identify a row.
   Filter:   Using specified criteria to select a subset of the rows in the table.

2. How do you set a primary key in access? Set the primary key in the PersonData table of the Persons2.mdb database.
   Ans: Go to the Table Design view for the table, right click on the field to be chosen as the primary key and select Primary Key. Hold down the shift key to select more than one field in the primary key.

3. What datatypes do we know about for fields?
   Ans: Text, Number, Yes/No. Other useful datatypes are Data/Time and Currency.

4. What is the result of applying the advanced Filter/Sort

   Field:	Gender	Age	Name
   Sort:		Descending	Ascending
   Criteria:	"F"
   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 Persons3.mdb.

   Ans:
   

   Filtered PersonData
   Name	Gender	Age
   Gretchen	F	52
   Judy	F	39
   Megan	F	39
   Alice	F	32

5. How do you apply a filter in Access?
   Ans: From the main menu select   Records >> Apply Filter/Sort.
   You could also click on the appropriate filter/sort icon on the tool bar.

6. What kinds of filtering are available in Access?
   Ans: Filter by Form, Filter by Selection, Filter by Excluding Selection, Advanced Filter/Sort.

7. What kinds of sorting are available in Access?
   Ans: Ascending, Descending, Not Sorted.

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

   Field:	Age
   Sort:
   Criteria:	<30
   or:

   greater than 30?

   Field:	Age
   Sort:
   Criteria:	>30
   or:

   between 35 and 45?

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

   less than 30 or greater than 40?

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

Importing Data into a Table

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

• Example
  Create a database named Persons2 that contains the table PersonData from this comma separated value file Persons.txt.

• Example
  Create a database named Persons3 that contains the table PersonData from this Excel file Persons.xls.

   

  Review Questions

  1. True or False: The primary key must always be the first column of a table.
     Ans: False, it can be any column of combination of columns.

  2. How to you set the primary key of a table in Access?
     Ans: Go to the Table Design view, right click on the field that you want to set as the primary key and select Primary Key. Hold the shift key down if you want to select more than one column in the primary key.

  3. What is the difference between importing and exporting data?
     Ans: Importing moves data from a non-Access format into an Access table. Exporting moves data from an Access table into a non-Access format.

  4. How do you Import data into an Access table?
     Ans. Open the database you wish to import data into. Now, from the main menu, select
     File >> Get External Data >> Import ...
     Browse to the file you wish to import, then click the Import button. This starts the Import Wizard - follow the directions given by the wizard.

  5. You are importing an Excel spreadsheet into an Access table when you obtain a Type Conversion Error. What is probably wrong?
     Ans: You may have forgot to check the box "First row contains column headings." or the data in some field in the Excel spreedsheet is incorrect.

   

  Activity A2

• Do Activity A2.