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
- 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.
- Create a new table titled Students, with the following fields and data types. Save and close the table when completed.
|
StudentID |
Short Text |
|
First Name |
Short Text |
|
Last Name |
Short Text |
|
Field of Interest |
Short Text |
|
Sport |
Short Text |
|
GPA |
Number |
|
Anticipated Graduation Year |
Short Text |
|
Scholarship |
Yes/No |
- 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.
- 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.
- 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.
- 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.
- 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,