Excel,Access, PowerPoint

Software Capstone Access Assignment Page 1 of 2

Access Assignment Jeremiah Garver is the operations manager at Great Giraffe, a career school in Denver, Colorado. Great Giraffe

offers part-time and full-time courses in areas of study that are in high demand by industries in the area,

including data science, digital marketing, and bookkeeping. Jeremiah uses Access to maintain information

about the courses offered by Great Giraffe, the students who enroll at the school, and the payment

information for students. He needs your help in creating queries to answer his questions.

1. Open the file Capstone_Access_DataFile.docx and then save it as Capstone_Access_YourName. Click

the Enable Content button next to the security warning, if necessary.

2. Open the tables, query, form, and report to become familiar with the database.

3. In the Student table, find the record for StudentID ART5210, and then change the Street value to 417

Barclay Avenue and the Zip to 80202.

4. In the Student table, find the record for StudentID ESP1734, and then delete the record. (Hint: Delete

the related records in the Registration subdatasheet first.) Close the Student table.

5. Create a query that lists students who are on a payment plan for any of the courses offered by Great

Giraffe. List only the StudentID, FirstName, and LastName fields for the students in your results, and

sort the results in ascending order by the LastName field. Save the query as AllPaymentPlanStudents,

run the query, and then close it.

6. In the Navigation Pane, copy the AllPaymentPlanStudents query, and then rename the copied query as

LittletonPaymentPlanStudents.

7. Modify the LittletonPaymentPlanStudents query so that it only displays those students from the city of

Littleton. The City field should not appear in the query datasheet. Save and run the query, and then

close it.

8. Create a query that lists students who are taking one of the Computer Science courses offered. (Hint:

Instead of looking at the individual sections, look at the Title of the courses.) In the query results,

display only the StudentID, FirstName, LastName, and Phone of the students in the courses. Sort the

results by StudentID in ascending order. Save the query as CompSciStudents and run the query.

9. Use the CompSciStudents query datasheet to update the Student table by using (303) 555-0042 for the

Phone value for Wendy Bradshaw.

10. Change the size of the text in the CompSciStudents query datasheet to use a 14-point font size. Resize

columns, as necessary, so that all field values and column headings are visible.

Software Capstone Access Assignment Page 2 of 2

11. Change the alternate row color in the CompSciStudents query datasheet to the Theme Color named

Green, Accent 6, Lighter 80%, and then save and close the query.

12. Create a query that lists the InstanceID, Title, StartDate, HoursPerWeek, and Cost fields (all fields in the

Course table) for courses that begin anytime in the first three months of 2021. Save the query as

FirstQuarterClassOptions, run the query, and then close it.

13. Create a query that lists the total outstanding balances for students on a payment plan and for those

students that are not on a payment plan. Show in the query results only the sum of the balance due,

grouped by PaymentPlan. Name the summation column Balances. Run the query, resize all columns in

the datasheet to their best fit, save the query as TotalBalancesByPlan, and then close it.

14. Compact and repair the Capstone_Access_YourName database, and then close it.

15. Submit the Capstone_Access_YourName database to Blackboard.