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 Paradise Valley Community College. You will create an Access database from scratch that includes:

Tables

• • Students
  • Sports

Queries

• • Student Athlete Scholarships: Which student athletes have a scholarship?
  • Summer Soccer Training: Which student athletes are on the Soccer team and are required to train over summer?
  • Student Athletes in Health Sciences: What student athletes are in the Health Sciences field of interest?
  • Baseball or Softball Student Athletes: What is the field of interest for baseball or softball student athletes?

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. 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. Ensure the Student ID is the primary key and close 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 Sport as the Primary Key. Name the new table Sports. Open the Sports table to verify there are 6 records.
5. Create a relationship using the Sports and Students tables using the Sport 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. Create a new query using Query Design that answers the question: Which student athletes 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 there are 20 records. Save the query as Student Athlete Scholarships. Close the query.
7. Create a new query using Query Design that answers the question: Which students are on the Soccer team and are required to train over summer? Include the following fields from the Students table: First Name, Last Name, Sport. Include the Summer Training field from the Sports table. Include criteria to indicate those students that have a sport of soccer, and summer training (yes). Sort the query ascending by the Last name field. Run the query to verify there are 12 records. Save the query as Summer Soccer Training, and close it.
8. 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 31 records. Save and close the query.
9. Create a new simple query using the Query Wizard that answers the question: What is the field of interest for baseball or softball student athletes? Include the following fields from the Students table: First Name, Last Name, Field of Interest and Sport. Name the query Baseball or Softball Student Athletes and modify the query design. Add criteria to the Sport field to include softball or baseball only. Run the query to verify there are 14 records. Save and close the query.
10. Using the Form Wizard, create a From 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
    • Summer Training: No
    • GPA Requirement: 3
11. In Design view, change the Summer Training label and textbox so that it is about 2”. Resize the GPA Requirement 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 from Access Practice 5. Resize the image and form header if necessary to ensure both the title and image display. Save the Students Form and close the form.
13. Create a report using the report wizard using 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, the 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. In Design view, apply organic theme with Blue colors, and Arial font. Insert the same image of the Puma from Access Practice 5 into the Report Header. Resize the image and header if necessary to ensure both the title and image display. Save and close the report.
14. 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.