ACCESS
Assignments
Text Information
The text for this portion of the
class is “The O’Leary Series, Office Access 2003”.
General Information
The Chapter Learning
Activities below are for you to learn the MS Access skills required to complete
the assignment and designed to be pre-work for the lab. These are not to
be turned in and you can ignore any place they require you to print.
The Step by Step exercises
below are the materials that must be turned in throughout the semester.
These are found at the end of the chapter starting on the page listed below
e.g. (AC1.78) and have a red bar on top of the page. Always be
sure your forms and printouts look correct. Adjust the spacing and alignment
properly; make sure there are no spelling/typing errors in the data, and use
appropriate clip art where required. To see common errors and the corresponding
point deductions for each lab click here.
Finally, On Your Own exercises
for each chapter are designed to give you a chance to practice your skills
without step by step instructions. They can be found at the back of the
chapter starting on the page listed for each lab e.g. (AC1. 81) and have a blue
bar on the top of the page. This is similar to the comprehensive case you
will be working on in Lab 5. You will have limited time for Lab 5 case an it is a similar format to these exercises
Assignments
Prior to class you should read the
chapter and complete the Chapter Learning Activities (in blue) which are
embedded in the chapters. These assignments will not be turned in, however, completion of these assignments will provide
you with the necessary knowledge to complete the graded assignments. In
the lab or at home, you will complete and then turn in the assignments called
out in the instructions that come from the Yellow “Step-by-step Lab
Exercises”. These assignments appear in the back of each chapter, so
bring your book to class.
Download the necessary databases
before class to a flash drive or directly from the website in the lab
(preferred). In addition, print and bring with you the Assignment Details
page so you know which “Step-by-step Lab Exercise” assignment that you should
turn in. We will start these assignments together, in the lab, but you may need
additional time on your own to complete them.
Assignments are due on (see
syllabus). Each assignment is worth (see syllabus) points.
It is recommended that you try to
complete each project or assignment (up to where the instructions tell you to
close the database and exit Access) in one sitting, especially if you are using
lab computers. Note: Save a softcopy of ALL of your work. You will be
responsible for your own work.
Assume you are
completing these assignments for your boss and make them look
professional. Attach a coversheet
containing your name, section, date the assignment, and staple all the pages
together before turning in. You must complete all your own work; copies
will not be accepted.
The Step by Step
Exercises below are color coded. The coding schemes are as follows:
Lab Instructions
(Black)
Data
(Green)
Print
Steps (Red)
Hints
and or databases for use in the lab (Blue)
Exercise
Databases
When using a lab computer, we
recommend that you work directly from the hard drive by transferring your
downloaded files from the website or flash drive to the desktop. When you
have completed the assignment, save the file to a floppy disk or flash drive,
and then also email the files to yourself as a backup. Floppy disks are
notoriously unreliable! Remember to save you work and then delete your
files from the desktop before you leave as they may not be there when you
return, and we want to keep the lab computers clean. If it has not been
said enough, SAVE YOUR WORK!!!
Guidelines to Follow
- You
may not be able to complete lab assignments in the class time
allotted. If this is the case, you must complete the assignment on
your own time and turn it in on the due date (see syllabus for due dates).
The computer lab is available in the business building (to be announced in
class).
- Always
work off the desktop or hard drive. Do not start working from your
diskette. It will slow your work down, and there’s a possibility of
losing your information.
- Save
a softcopy of all projects that you will turn in. (The best way to
do this is by emailing yourself the information as an attachment. i.e.
Yahoo! or any email with sufficient space)
- Do
your own work! Copying others work can result in an automatic
failure for the assignment and most likely the entire course.
- Read
each chapter and attempt the hands-on activities before you begin working
or the assignment will not make sense (unless you have previous knowledge
of working with MS Access).
- Homework
is due at (see syllabus)
- Please
format all work so it complete and readable – point deductions occur for
truncated fields, word wraps, reports on multiple pages horizontally, etc.
Lab 1 – Creating a Database
PRE-WORK ASSIGNMENT
(Chapter Learning Activities): pgs
I.1-I.26, AC1.1-AC1.62 - White pages with Blue graphics inside the
chapter
POST LAB PRACTICE (On Your own): Expense Tracking Database (AC1.82) - Yellow pages at the end of the chapter with Blue bar
TURN-IN ASSIGNMENT (Step
by Step Exercise): Adventure Travel
In this assignment you
will create a new database, create a table, enter, edit and delete table data,
modify fields, change column widths, and preview information, and close and
open and existing database and table. Follow the directions below to
complete the lab.
ADVENTURE TRAVEL
You have just been hired by Adventure Travel
a.
Create a database named ‘Adventure Travel’. Design a table using the following
field information and copy repetitive text where possible:
|
Field
Data |
Type |
Description |
Field
Size |
|
Package |
Text |
Unique
three-digit package number |
3 |
|
Destination |
Text |
|
20 |
|
Length |
Text |
Enter
as # days, # nights |
20 |
|
Description |
Text |
Package
details (25 words or fewer) |
25 |
|
Accomodations |
Text |
Name
of hotel, motel, or other lodging |
20 |
|
Contact |
Text |
Name
of contact person |
20 |
|
Phone |
Text |
Enter
contact phone number as ###-###-#### (e.g., 800-555-5555) |
15 |
PRINT the “table design
view” after completion (PRINT 1) **
Hint: see Print Table Design View (using print screen) at the
bottom of this document
b.
Make the Package field the primary key field.
c.
Change the Contact and Accommodations field size to 30.
d.
Change the size of the Description field to 50 and its description to 15 words
or fewer.
e.
Save the table as ‘Packages’
PRINT the “entire table
design” after making the changes in steps b, c & d (PRINT 2) Hint: see Print Entire Table Design (using MS Access) at the bottom of
this document
f.
Enter the following records into the table in Table Datasheet view:
|
Record 1 |
Record 2 |
Record 3 |
|
010 |
053 |
112 |
|
|
Circle
C Ranch |
|
|
2
days, 1 night |
7
days, 6 nights |
5
days, 6 nights |
|
Lodging
and lift tickets |
Activities
arranged by ranch staff |
Landmarks
and galleries |
|
|
Ranch
House |
Washington
Hotel |
|
Linda
Fairlane |
Rebecca
Lewis |
Frank
Miran |
|
800-555-2525 |
602-555-0932 |
212-555-0048 |
g.
Add the following records into the table in Data Entry:
|
Record 1 |
Record 2 |
|
119 |
052 |
|
|
|
|
3
days, 2 nights |
2
days, 1 nights |
|
Accommodations
and show tickets |
Accomodations
and park passes |
|
Grand
Hotel & Casino |
Mega
Motel |
|
Brian
Thompson |
Carol
Davis |
|
800-555-7521 |
800-555-1122 |
h. Redisplay all the records in the
table.
PRINT the TABLE DATASHEET view after making the changes in steps f,
g, &h (PRINT
3)
i.
Adjust the column widths using Best Fit.
j. Edit the record for Package #119
to replace Brain Thompson’s name with your name.
k. Change the description for
PRINT the TABLE DATASHEET view after making the changes in steps I ,j & k (PRINT 4)
l. Delete record 053.
m. Change the
page orientation to landscape. Change the left and right margins to ˝-inch.
n. Preview and PRINT the table, then save and close it. (PRINT 5)
Lab 2 – Modifying a Table and Creating a Form
PRE-WORK ASSIGNMENT
(Chapter Learning Activities): pgs AC2.1-2.46 - White pages with Blue graphics inside the chapter
Required
Files/Databases: ac02_Employees
(database)
POST LAB PRACTICE (On Your own): Employee Database Update (AC2.64) - Yellow pages at the end of the chapter with Blue bar
TURN-IN ASSIGNMENT (Step by Step Exercise): LearnSoft
Product Development Database (AC2.60) -
Yellow pages at the end of the chapter with Red bar
Required
Files/Databases: ac02_LearnSoft
(database)
In this assignment you
will navigate large tables, customizing and inserting tables, hiding and
redisplaying fields, finding and replacing data, sorting records, formatting
the datasheet, creating and using forms, previewing and printing a form,
identifying object dependencies and setting database and object properties, and
documenting a database. Follow the directions below to complete the lab.
LEARNSOFT PRODUCT DEVELOPMENT
DATABASE
LearnSoft
Inc., which develops computer curriculums for grade K-8, has just hired you to
update and maintain their software database. Some of the tasks your
manager asks your to accomplish involve correcting some
known errors and applying some validation rules. She would also like you
to create a form, shown here, which will make it easier to enter new software
titles.
a.
Open the database file named ‘ac02_LearnSoft’ and the table named ‘Software’.
b. The
name of the CalcIt series has been changed to SolveIt. Use the Replace command to find the two records with
this title and change them.
c. The program called ReadWrite
was never released and has been replaced by separate reading and writing
programs. Find and delete this record.
d.
Switch to Design view and add a validation rule and text to the Grade Level
field so that it only allows an entry of K-2, 3-5, or 6-8 (Hint: In the
Validation Rule setting type: “K-2” or “3-5” or “6-8”).
PRINT the “table design view” after making the changes in steps b, c
& d (PRINT 1)
e. Add a field above Release Date to specify the name of
the project manager for each title:
Field name: Project Manager
Data type: Text
Description: PM name
Field size:
20
PRINT the “entire table
design” after making the changes
required in step e (PRINT 2)
f. Return to datasheet view and hide the Title through
Key Topic columns. Next, you need to update the table by filling in the new
field for each record. Each program has a unique two-digit prefix that
identifies its project manager. Using first Malcolm Duggan and other names of
your choice, complete the Project Manager field for
each record. For example, Malcolm managed products with the 24 prefix. Unhide
and Best Fit the columns when you are
done.
PRINT the datasheet view with all the records updated in step f
(PRINT 3)
g. Create a columnar form using the Form Wizard. Use the
Software table and include all the fields in their current order. Choose the
columnar layout and Blends style. Use the name LearnSoft
Programs for the form.
h. Use the form to enter a new record for a software
program called Around the World, Product Code 78-0220, which is currently in
development for grades 3-5 to introduce students to international cultures.
Enter your name as the Project Manager and today’s date as the Release Date.
Preview and PRINT the form for this new record (PRINT 4). Example
below
|
Release
Date |
3/5/2001 |
|
Product
Code |
77-0101 |
|
Title |
Remember
When |
|
Subject |
History |
|
Grade
Level |
K-2 |
|
Key
Topics |
Famous
people and major event |
|
Project
Manager |
Ron
Bowers |
|
Release
Dates |
6/30/1999 |
|
Product
Code |
77-0102 |
|
Title |
Remember
When |
|
Subject |
History |
|
Grade
Level |
3-5 |
|
Key
Topics |
|
|
Project
Manager |
Ron
Bowers |
|
Release
Dates |
9/19/2000 |
|
Product
Code |
77-0103 |
|
Title |
Remember
When |
|
Subject |
History |
|
Grade
Level |
6-8 |
|
Key
Topics |
World
History |
|
Project
Manager |
Ron
Bowers |
|
Release
Dates |
12/20/2001 |
|
Product
Code |
78-220 |
|
Title |
Around
the World |
|
Subject |
International
Cultural Studies |
|
Grade
Level |
3-5 |
|
Key
Topics |
People,
Places, and Cultures |
|
Project
Manager |
Student
Name |
|
Release
Dates |
5/25/2005 |
Add
your name to the file and object documentation.
i. PRINT the database or file documentation containing
your name (see in figures 2.54(AC2.45) and 2.55(AC2.45) in chapter) (PRINT 5)
Note: Lab 2 is used for Lab 3, Save the Lab 2 database on your flash drive or attach it to your email. Without Lab 2 You are unable to proceed to Lab 3.
Lab 3 – Analyzing Data and Creating Reports
PRE-WORK ASSIGNMENT
(Chapter Learning Activities): pgs AC3.1-AC3.57 - White pages with Blue graphics inside the chapter
Required
Files/Databases: ac03_Personnel
POST LAB PRACTICE (On Your own): EMP Expense Account Report (AC3.71) - Yellow pages at the back of the chapter with Blue bar
TURN-IN ASSIGNMENT (Step
by Step Exercise): LearnSoft Product Database
(AC3.68) - Yellow pages at the
end of the chapter with Red bar
Required
Files/Databases: ac02_LearnSoft
(database)
In this assignment you
will filter table records, create and modify a query, move columns, query two
tables, create reports from tables and queries, modify a report design, select
and move, and size controls, change page margins, preview and print a report,
and compact and backup a database. Follow the directions below to
complete the lab:
LEARNSOFT PRODUCT DATABASE
You are responsible for
updating, maintaining, and using LearnSoft’s database
(Step-by-Step Lab 2). You are frequently asked to provide reports that shown
the company’s curriculum software products from different vantage points and
for different purposes. The most recent request was from product development,
asking for a report that shows the science and spelling titles released prior
to the year 2002. They plan on using this report to gauge how many titles on
those topics will need to be updated in the near future. The report you create
for them will look similar to the report shown here.
a. Open the database named ‘ac02_LearnSoft’ that you
modified in Step-by-Step of Lab 2.
b. Use the Query Wizard to create a query based on the
Software table. Include the following fields in this order: Title, Subject, Grade
Level, Release Date. Name the query ‘Outdated Science
and Spelling’.
Display and PRINT the “design
view” of the query from step b
after saving (PRINT 1)
c. In Query Design view, enter Science in the first
Subject criteria cell and Spelling in the Second (Or) criteria cell. Enter
<12/31/02 in both the Release Date criteria cells. Run the query and review
the resulting datasheet.
PRINT the query result from step c using the datasheet view (PRINT
2)
d. Use the Report Wizard to create a report based on the
Outdated Science and Spelling query you just saved. Include all the fields in
the order listed. Select Release Date as the first sort field and Subject as
the second sort field. Select the Tabular layout, landscape orientation, and Corporate style. Name the report ‘Pre-2002 Science and
Spelling Software’.
PRINT the Report (PRINT 3)
e.
Center the Report Header Control at the top of the page.
f. Reduce the size of the Subject controls and move them
so they are centered between the Release Date and Title controls. Change the
left and right page margins to .75”.
PRINT the “design view” of the report after steps e, & f (PRINT 4)
g. Preview and then PRINT the report. (PRINT 5)
Lab 4 – Working with Multiple Tables
PRE-WORK ASSIGNMENT (Chapter
Learning Activities): pgs
AC4.1-AC4.56 - White pages with Blue graphics inside the
chapter
Required
Files/Databases: ac04_Club
Employees(database) ac04_pay
(spreadsheet)
POST LAB PRACTICE (On Your own): WriteOn! Database
(AC4.72) - Yellow pages at the
back of the chapter with Blue bar
TURN-IN ASSIGNMENT (Step
by Step Exercise): LearnSoft Product Database
(AC4.70) - Yellow pages at the
back of the chapter with Red bar
Requited
Files/Databases: ac04_LearnSoft
(database) ac04_sales
(spreadsheet)
In this assignment you
will create a table by importing data from another application, create
relationships between tables, create a multi-table query, add a criteria field
to a query, and format and print a query. Follow the directions below to
complete the lab.
LEARNSOFT PRODUCT DATABASE
The database you created
for LearnSoft Company currently contains a table that
lists the educational software titles produced by the company as well as the
subject of each package and the designer who is responsible for developing it.
You have created several queries and reports based on this table, which has
helped the marketing and development managers immensely. After hearing about
the success of this database, the sales manager requested that you add tables
that contain data about packages that have been purchased so that queries can
be run that show how the packages are selling and what the total monthly,
quarterly, and annual purchase amounts are. You decide to start this process by
creating a simple order table with product name, order date, and quantity purchased
data. Then by adding a unit cost field to the existing Software Development
table, you can use both tables to run a query that calculates the total sales
per package to date. When you are finished, you will have a new database table
and query printout that looks similar to this shown here.
a.
Rename the ac04_LearnSoft database LearnSoft Titles. Open the Software table. Enter your name
as the Developer in the last record, and then save and close the table.
b.
Use the Table Wizard to create a new table with the following fields from the
Order Details Sample Table:
OrderID,
ProductID,
DateSold,
Quantity
c.
Name the table Software Sales, and let Access
assign the primary key. Do not assign a relationship between this new table and
the existing Software Development table. Specify that you want to modify the
table design, and then finish the Wizard.
d.
Change the data type of the OrderID field from
AutoNumber to Text and change its field size to 5. Because multiple records
will be entered for some orders (for multiple software packages purchased on
the same order), each record will not necessarily have a unique OrderID, so you also need to remove the primary key from
this field.
e.
Test the DateSold input mask by selecting the Short
Date mask in the Input Mask Wizard and entering numbers both with and without
punctuation and of correct and incorrect lengths in the Try It box. Accept the
rest of the input mask defaults and exit the Wizard.
Print the “table design”
after making the changes in steps d & e (Print 1)
f.
Delete the ProductID field and insert a lookup field
in its place. Specify that you want to use values from another table, select
Software as the table you want to use as the source of these values, and select
Product Code and Title as the fields containing the values. Accept the default
Hide Key Column selection and the displayed column width. Name the lookup
column Software Package, finish the Wizard,
and save the table when prompted to do so.
Print the “table design”
after making the changes (Print 2)
g.
Make all fields required.
h. Save the table
design and switch to Datasheet view.
i. Enter an order
number of 00200, select the Write It II
software package, and enter a DateSold of 06/05/2005 and a quantity of 4. Since this order
was for a school’s third grade computer lab, there were two more software
packages purchased on the same order. Therefore, you need to enter two more records with
the same order number: one for four Read It II packages and another for four Spell It II packages. Enter the same DateSold
date for both. four Spell
It II packages. Enter the same DateSold date for
both.
j.
Download and open ac04_sales worksheet(ac04_sales.xls)
and enter values for Order Id, Date Sold, Quantity. Use varying quantities for
each order. When you are finished save it. Now switch
to the database, close all tables. Choose File/Get External Data/Import.
If necessary, select the location containing your data files. From the Files of
Type drop-down list box, select Microsoft Excel. Select ac04_sales.xls file and click Import. Make sure that order is selected, if not select order and click on to Next. Select ‘First Row
Contains Column Headings’ then click Next. Select in
an Existing Table. Select Software Sales
from option’s drop-down list. Click Next and then Click
Finish. Open the Software Sales datasheet view: When you are finished,
adjust the column widths to fit the displayed data.
k.
Add a final record with your first and last initials followed by 999 as the OrderID and today’s
date as the DateSold. You can select whatever
software package and quantity you like.
l.
You are now ready to add a field to the Software table that will contain the
cost of each software package. Open this table and switch to Design view. Add a
field called Price with a data type of Currency. Save the design changes
and switch back to Datasheet view. Enter prices for each of the software
packages that were ordered thus far (the ones you selected in the Software
Sales table, which you can see by expanding the subdatasheet).
Print the datasheet view
after completing steps g, h, i, j, k & l (Print
3)
m.
Although you have not finished entering all the software packages prices or
orders, you decide to see whether you can use the combined table data to
produce the sales-to-date query that the sales manager requested. Use the
Simple Query Wizard to create a new query. Include the Software Package and
Quantity fields from the Software Sales table, and the Developer and Price
fields from the Software table. Select Summary as the type of query you would
like to create, and in the Summary Options box, select the Sum option for the Quantity
field. Name the query Software
Package Sales. Specify that you want to modify the query design and
finish the Wizard.
n.
In the query’s Design view, change the Sum of Quantity: Quantity calculated
field name to Total Packages
Sold: Quantity. Then in the blank field column to the right, enter
another calculated field: Total Sales:
[Quantity]*[Price]. Assign the Currency property to the new field. Run
the query.
Print the query “design
view” after making changes in steps m & n (Print 4)
o. Run
the query. Adjust the column widths to fit the displayed data.
Apply
formatting of your choice and then print the query result. (Print 5)
Special Instructions
for Labs (as referenced above)
Print
Table Design View (using print screen)
1. Display
the design view for a table
2. Open
a Powerpoint or Word document and set the orientation
to landscape
3. Press
the Function Key and Print Screen button simultaneously
4. Position
the cursor on the Powerpoint or Word document and press
Ctrl and V or Edit then Paste to paste the screen image into the document
5. Resize
and format as appropriate so that it can be read by the grader
Print
Entire Table Design (using MS Access Function)
1. On
the Tools menu, point to Analyze, and then click Documenter.
2. Click
the Tables tab and select the table that you want to document and click OK
3. Once
the preview of the design view is displayed, check the length of the document
(total number of pages). If you are working in the school lab, and the
document is 3 pages or less click Print 
from
the toolbar. If you are in the school lab, and the document is greater
than 4 pages, on the File menu, select and click Print and print first
pages 1-3, then print again from pages 4-n.