"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!

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

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

31 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. [4 pts] Create a Members table as was done in the "In a Flash" Video Tutorial. Include the following fields:
• last_name - text field
• first_name - text field
• username - text field
• phone - text field
• date_joined - Date/Time field
3. [2 pt] Set the username field to be the primary key.
4. Set the format for the date_joined field to Short Date.
5. [3 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. [4 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
Note that in a given record, the donor is a person that donates money. The solicitor is the person who solicited this donation -- i.e. the person who told the donor about the fund-raiser and convinced the donor to give money.
8. [2 pt] Set Donation_ID# to be the primary key.
9. [2 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. [4 pts] Create a third table to store the officer's of the organization. Include two fields:
• Title - text field
• member_username - text field
12. [2 pt] Set Title to be the primary key.
13. Close the table and name it Officers.
14. [4 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. [4 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.

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

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

33 Points

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

16. [4 pts] Add 20 real or fictitious members to your Members table. Include yourself as one of the members.
17. [3 pts] Sort the records in the Members table alphabetically by last name.
18. [4 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. .
19. [3 pts] Assign five members to be officers in your Officers table. Give yourself the highest ranking title.
20. Leave the fund-raiser table empty for now.
21. [4 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#.
22. [3 pts] Add a title to the form that includes the name of the fund-raising activity.
23. [3 pts] Change the title to a large bold font.
24. [4 pts] Insert a small and appropriate logo to decorate the form. You can acquire an image from the Web by right-clicking and saving.
25. Save the form using the name of your fund-raising activity (ie. "candy-drive entry form").
26. [5 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. Make sure to include some solicitors more than once (i.e. on multiple donors)

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

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

36 Points

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

27. [7 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. The query should always display only the top 10 donations, even if new records are added to the table. Provide an appropriate name for the query when saving.
28. [8 pts] Create another select query that lists the officer titles, first names, last names, and phone numbers, of only the officers of your organization. The query criteria should specify that the returned records are listed in alphabetical order by last name. Provide an appropriate name for the query when saving.
29. [9 pts] Create another select query that lists the top 5 members that solicited the most money in the fund-raiser. (Note: these are the top solicitors, not the top donors!) 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.
30. [6 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"
31. [3 pts] Add the small image that you used on your form to the top of the report.
32. [3 pts] Change the column headings from the database field names to "Total Earned", "Last Name" and "First name".
33. Close and save the report.
34. BE SURE TO CLOSE ACCESS PRIOR TO SUBMITTING YOUR ASSIGNMENT FILE.