Challenge It

In this challenge activity, you will complete a project that incorporates many of the key skills learned in the Access Unit. For this project, you are a Database Administrator responsible for managing data on student athletes at South Puget Sound Community College. You will create an Access database from scratch that includes:

Tables

• • Students
  • Sports

Queries

• • Student Scholarships: Which student athletes have a scholarship?
  • Tennis Training: Which student athletes are on the Tennis team?
  • Student Athletes in Health Sciences: What student athletes are in the Health Sciences field of interest?

Forms

• • Sports
  • Students

Reports

• • Student Listing

1. Open Access and select Blank Desktop Database. Save the database in your data files folder, and name it Lastname_Firstname_Access_Challenge and create the database.
2. Create a new table titled Students, with the following fields and data types. Ensure the StudentID is the primary key and close the Students table. Save and close the table when completed.

3. Import the Excel spreadsheet data titled Access_Challenge_Import1 and append it to the Students table. 52 records should import into the Students table. Be sure to resolve any import errors before continuing.
4. Import the Excel spreadsheet data titled Access_Challenge_Import2 into a new table in the current database. Ensure the first row contains column headings is checked, keep the default field imports, and assign Sports as the Primary Key. Name the new table Sports. Open the Sports table to verify there are 6 records. Once you have verified the table is correct, save and close any open tables. 
5. Create a relationship using the Sports and Students tables using the Sports field to join the two tables. Enforce referential integrity and select both cascade options. Save and Close the relationships window, resolving any error or warning messages.
6. In the Student table, add yourself as the last record, filling in all of the required fields. You can make up the data for everything except sports and scholarship. For sports, type in “tennis” as your sport. For scholarship, check the box for yes.
7. Create a new query using Query Design that answers the question: Which students have a scholarship? Include all fields from the Students table, include criteria to indicate those students that have a scholarship, and sort the query ascending by Last Name. Do not display the StudentID or GPA fields in the query. Run the query to verify the query pulls 29 records. Save the query as Student Scholarships. Close the query.
8. Create a new query using Query Design that answers the question: Which students are on the tennis team? Include the following fields from the Students table: First Name, Last Name, Sport. Include criteria to indicate those students that have a sport of tennis. Sort the query ascending by the Last name field. Run the query. Save the query as Tennis Training, and close it.
9. Create a new simple query using the Query Wizard that answers the question: What student athletes are in the Health Sciences field of interest? Include all fields from the Students table, Detail format, name the query Student Athletes in Health Sciences, and modify the query design. Add criteria to only include those students with a Field of Interest in Health Sciences. Run the query to verify there are 22 records. Save and close the query.
10. Using the Form Wizard, create a Form based off of the Sports table. Include all of the Fields from the Sports table, tabular format, and name the form Sports. Open the form to view or enter information. Add a new record to the form with the following information:
    • Sport: Cross Country
11. In Design view, resize the Sport label and control so that you can see the entire label. Apply the organic theme with Blue colors, and Arial font. Save and close the form.
12. Using the Form Tool, create a Form based off of the Students table. Apply the organic theme with Blue colors, and Arial font. In Design View, change title in the form header to Student Athletes, and add the Puma image (right click and save as an image).
    
    
13. Resize the image and form header if necessary to ensure both the title and image display. Save the Students Form and close the form.
14. Create a report using the report wizard from the Students table. Include the following fields: First Name, Last Name, Field of Interest, Sport and Scholarship. Add a grouping level based on Field of Interest. Sort ascending by last name, then first name. Apply the Stepped layout, portrait orientation and adjust the field width so all fields fit on a page. Title the report Student Listing and preview the report.
15. In Design view, apply organic theme with Blue colors, and Arial font. Insert the same image of the Puma from above into the Report Header. Resize the image and header if necessary to ensure both the title and image display. Save and close the report.
16. Save and close all database objects, and take note of where your database is stored. Close out of Access and submit your entire database per your instructor’s instructions.