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
- 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. Ensure the StudentID is the primary key and close the Students table. Save and close the table when completed.
|
StudentID |
Short Text |
|
First Name |
Short Text |
|
Last Name |
Short Text |
|
Field of Interest |
Short Text |
|
|
Short Text |
|
Sport |
Short Text |
|
GPA |
Number |
|
Graduation Year |
Short Text |
|
Faculty ID |
Number |
|
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. 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 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.
- 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.
- 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.
- 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.
- 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, Las