75 Points

This assignment uses the Watch-Practice-Apply approach.

b"In a Flash"
Video
Tutorial
aPractice and Online Help accessAssignment to Submit for Grade
1. Watch...
"In a Flash" Video Tutorials are short video presentations that you view to see the skills performed and explained prior to trying them yourself. This is 10 minutes well invested!
2. Practice...
Experiment with the skills taught in the "In a Flash" Video Tutorial, making use of Microsoft Office Help for skills that may be unclear.
3. Apply...
After viewing the "In a Flash" Video Tutorial and experimenting, work live in Excel 2007 following step-by-step instructions to creating your own file to submit for a grade.

We have broken the important aspects of Microsoft Access into four modules:

  1. An Overview of Databases
  2. Designing Database Tables
  3. Working with Data in Tables and Forms
  4. Creating and Using Queries and Reports
Module 1 provides an overview of Access and how it is used. It also provides a look at an example database that is very similar to the one you will submit for a grade. Modules 2-5 each includes an "In a Flash" Video, a practice session, and instructions for the file you will submit for your grade.

1

Using Microsoft Access Databases

"In a Flash" Video Tutorial
Access 1. An Overview of Databases

 tutorials require headphones or speakers

Use the Video Tutorial to learn about
• An Overview of Databases
The Navigation Pane
Tables
• Forms
• Queries
• Reports

and to see a completed assignment file!

2

Designing Database Tables

"In a Flash" Video Tutorial
Access 2. Designing Database Tables

Use the Video Tutorial to learn about
• Defining Fields
• Primary Keys
• Table Relationships
and more!

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

access

 


21 Points

Assignment to Submit for Grade

Prior to beginning, come up with a real or fictitious student organization name (something other than what was used in the example). Also come up with some form of fund-raising drive for the student organization. Your organization and find-raiser should be unique and different than other students in the class.

  1. Create a new database file in Access, and name it after your school network username.
  2. [3 pts] Create a Members table as was done in the "In a Flash" Videoial. Include the following fields:
    • last_name - text field
    • first_name - text field
    • username - text field
    • phone - text field
    • date_joined - Date/Time field
  3. [1 pt] Set the username field to be the primary key.
  4. Set the format for the date_joined field to Short Date.
  5. [2 pts] Apply an Input Mask of the appropriate variety to the phone ((___) ___-____)) and date_joined (__/__/____) fields. Use the Input mask tool or type the following in the Input Mask textbox:
    • for phone type !(999) 000-0000;;_
    • for data type 99/99/0000;0;_
  6. Close the Members table and name it Members.
  7. [3 pts] Create a second table to store records of fund-raising donations. Include the following fields:
    • Donation_ID# - AutoNumber field
    • Amount - currency field
    • Donor_lname - text field
    • Donor_fname - text field
    • Donor_phone - text field
    • Solicitor_username - text-field
  8. [1 pt] Set Donation_ID# to be the primary key.
  9. [1 pt] Apply an input mask to Donor_phone.
  10. Close the table and name it after your fund-raiser name (i.e. "Car Wash" or "Candy Drive", etc.)
  11. [3 pts] Create a third table to store the officer's of the organization. Include two fields:
    • Title - text field
    • member_username - text field
  12. [1 pt] Set Title to be the primary key.
  13. Close the table and name it Officers.
  14. [3 pts] Create a relationship between the Members table and the table that will store fund-raiser records. Use the username field to bridge the two. It should be a one-to-many relationship, with Referential Integrity enforced.
  15. [3 pts] Create another relationship between the Officers and Members tables following the same specifications as in the previous step.

Close the database. You'll use it again in later steps.

   
3

Working with Datasheets and Forms

"In a Flash" Video Tutorial
Access 3. Working with Datasheets and Forms

Use the Video Tutorial to learn about
• Datasheets
• Subdatasheets and Lookup Tables
• Sort and Filter
• Calculating
• Using Forms
• Designing Forms

and more!

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

access

 


24 Points

Assignment to Submit for Grade
Continue working with the database you started in the previous step.

  1. [3 pts] Add 20 real or fictitious members to your Members table. Include yourself as one of the members.
  2. [2 pts] Sort the records in the Members table alphabetically by last name.
  3. [3 pts] Change the Title field in the Officers table to use a Lookup Table
    • Use the lookup field to provide five titles of your choice for officers (ie. president, secretary, czar, king, queen, whatever). Type the values, rather than looking them up in a table. .
  4. [2 pts] Assign five members to be officers in your Officers table. Give yourself the highest ranking title.
  5. Leave the fund-raiser table empty for now.
  6. [3 pt] Create a form to be used to input records into your fund-raiser table. The form should include all fields except for the ID#.
  7. [2 pts] Add a title to the form that includes the name of the fund-raising activity.
  8. [2 pts] Change the title to a large bold font.
  9. [3 pts] Insert a small and appropriate logo to decorate the form. You can acquire an image from the Web by right-clicking and saving.
  10. Save the form using the name of your fund-raising activity (ie. "candy-drive entry form").
  11. [4 pts] Use the form to enter fictitious data for 20 donation records. Include yourself as a donor. Note: you will need to use Solicitor_usernames that exist in your Members table.
   
4

Working with Queries and Reports

"In a Flash" Video Tutorial
Access 4. Working with Queries and Reports

Use the Video Tutorial to learn about
• Simple Queries

Querying Joined Tables
• Calculating Values
• Reports

and more!

Practice
Turn to Microsoft Office Help for assistance as you experiment with software features.

access

 


30 Points

Assignment to Submit for Grade
Continue working with the database you started in the previous steps.

  1. [6 pts] Create a select query that displays only the top 10 donations from the fund-raiser table. Include the fields Amount, Donor_lname, Donor_fname. Provide an appropriate name for the query when saving.
  2. [7 pts]Create another select query that lists the officer titles, first names, last names, and phone numbers, of only the officers of your organization. List them in alphabetical order by last name. Provide an appropriate name for the query when saving.
  3. [8 pts] Create another select query that lists the top 5 members that solicited the most money in the fund-raiser. Include a calculated field that tells how much each of the 5 members earned (as was done in the "In a Flash" Video), and their first and last names. Provide an appropriate name for the query when saving. Hint: In the design view of a query there is a tool on the ribbon called "Return" that allows you to specify how many records to return.
  4. [5 pts] Create a report that displays the data generated by the query described in the last step; the one that shows the top 5 members who earned the most money. Include all the fields used in that query. Use any design of your choosing that presents the data in a logical attractive manner. Use the title "Top 5 Members"
  5. [2 pts] Add the small image that you used on your form to the top of the report.
  6. [2 pts] Change the column headings from the database field names to "Total Earned", "Last Name" and "First name".
  7. Close and save the report.
  8. BE SURE TO CLOSE ACCESS PRIOR TO SUBMITTING YOUR ASSIGNMENT FILE.