Study Guide for Test 2

Monday Nov 2, 2006 W101 Thompson Lecture Hall

Topics for Test 2

·  Excel Project 3

·  Excel Web

·  Access Project 1

·  Access Project 2

·  Access Project 3

·  Assignments 4, 5, 6 and 7

Excel Project 3

·  Using the Fill Handle to create a series (EX 152)

·  Increase Column Widths (EX 155)

·  Copy a Range of Cells (EX 157)

·  Insert a Row (EX 159)

·  Displaying the System Date (EX 165) or =Now() function

·  Absolute versus Relative Addressing (EX 168)

o  =$A$4*B2 The first entry is absolute and will not change when the formula is copied, but the B2 will change. For example, if the formula currently is in Cell D2 and is copied down to D3, it will become =$A$4*B3.

·  If Function will not be asked (EX 170)

·  Formatting Numbers (EX 177)

·  Drawing a 3-D Pie Chart on a separate sheet

Excel Web

·  Know the difference between a Static and Dynamic Web page (EX225)

Designing a Database

·  Please see Course Documents/ Lecture Notes and Hints/ Rules for Designing a Database

Access Project 1

·  Creating a Table (AC 15)

·  Adding Records to a Table 9AC 22)

·  Using Queries (AC 35)

o  Know using Wizard and Design View

·  Using a Form to View Data (AC 38)

·  Creating a Report (AC 42)

·  Designing a Database (AC 50)

Access Project 2 Querying a Database

·  Create Query using Design Window (AC 68)

·  Entering Criteria (AC 75)

·  Using AND, Using OR (AC 84)

·  Sorting Data in a Query (AC 86)

·  Omitting Duplicates (AC 87)

·  Joining Tables (AC 90)

·  Using Calculated Fields (AC 96)

·  Grouping (AC 1102)

·  Crosstab Queries will not be asked

Access Project 3

·  Add Change and Delete Records (AC 116)

·  Filters will not be asked

·  Changing the Structure (AC 126)

o  Insert a new field

o  Delete a field

·  Using an Update Query (AC 136)

·  Validation Rules and Text (AC 139)

o  Required fields (AC 140)

o  Default Value (AC 141)

o  Update Restrictions (Ac 145)

·  Lookup Fields will not be asked

·  Referential Integrity (AC150)

o  Update and Deleting Records and Restrictions

Assignment 4 Ex P212 Ex 1, EX p 240 Ex 2

Assignment 5 AC p56 Ex 1, AC 61 Ex 3

Assignment 6 AC p109 Ex 1, AC p110, Ex 2

·  Please see examples below

Assignment 7 AC p168 Ex 1, AC p 169 Ex 2

Criteria Questions

Assume the database contains these fields in the Address Table.

LastName, FirstName, Street, City, Zip, AreaCode, PhoneNumber, YearsOfService

1. Show the query that will print the last name, first Name, AreaCode and Phone Number of all those who are in the 716 Area Code.

Field:
Table: Address / Address / Address / Address
Totals:
Sort:
Show: / / /
Criteria:
Or:


2. Show the query that will print the Last Name, First Name and Phone Number for all those whose Zip codes start with 14.

Field:
Table: Address / Address / Address / Address
Totals:
Sort: / Ascending
Show: / / /
Criteria:
Or:

3. Show the query that will print out only last names and first names where the years of service is less than 10. Group them by Zip code.

Field:
Table: Address / Address / Address / Address
Totals:
Sort:
Show: / / /
Criteria:
Or:

4. Show how to print the Average Years Of Service, Grouped by Area Code.

Field:
Table: Address / Address / Address / Address
Totals:
Sort:
Show: / / /
Criteria:
Or:

5. Explain what the following query will print. Please assume that all Show boxes have been checked.

Field: LastName / FirstName / Phone / YearsOfService
Table: Address / Address / Address / Address
Totals:
Sort: / Ascending
Show: / / /
Criteria: / 673* OR 672* / <= 5
Or: