Case Problem 1

Data File needed for this Case Problem: Instruction.accdb

Great Giraffe 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 created the Instruction database to track and view information about the students, faculty, and courses his business offers. The database also contains queries, forms, and reports. Jeremiah wants you to define relationships between the tables and to create some new queries for him. Complete the following steps:

1. Open the Instruction database located in the Access3 > Case1 folder provided with your Data Files, and then click the Enable Content button next to the security warning, if necessary.

2. Modify the first record in the tblFaculty table datasheet so the First Name and Last Name columns contain your first and last names. Close the table.

3. Define a many-to-many relationship between the tblCourse and tblFaculty tables, using the tblTeaching table as the relationship table. Define a one-to-one relationship between the primary tblStudent table and the related tblScholarship table. Select the referential integrity option and the cascade updates option for the relationships.

4. Create a make-table query based on the qryAllPaymentPlanStudents query, selecting all fields from the query and only those records where the City field value is Denver. Use tblCityPaymentPlan as the new table name, store the table in the current database, and then run the query. Save the query as qryMakeCityPaymentPlan, and close the query. Open the tblCityPaymentPlan table and verify the City value is Denver for all records. Close the tblCityPaymentPlan table.

5. Create an append query based on the qryAllPaymentPlanStudents query that selects all fields for only those records where the City field value is Littleton. Append the records to the tblCityPaymentPlan table and run the query. Save the query as qryAppendLittleton, and then close the query. Open the tblCityPaymentPlan table and verify that the City column includes values of both Denver and Littleton. Close the tblCityPaymentPlan table.

6. Create an update query to select all records in the tblCityPaymentPlan table where the value in the City field value is Littleton, and set the remaining balance to 0. Run the query, save it as qryUpdateLittletonToZero, and then close the query. Open the tblCityPaymentPlan table and verify balances for the Littleton students have been reduced to $0. Close the tblCityPaymentPlan table.

7. Create a delete query that deletes all records in the tblCityPaymentPlan table in which the BalanceDue field value is greater $1500. Run the query, save it as qryDeleteMoreThan1500, and then close the query. Open the tblCityPaymentPlan table, resize all columns to their best fit, and then verify there are no records whose BalanceDue field is greater than $1500. Save your layout changes, and then close the tblCityPaymentPlan table.

8. Create a query using a self-join that lists all faculty that have supervisors. To do so, add two copies of the tblFaculty field list to the query design window, and then create a relationship between the FacultyID field in the first list and the SupervisorID field in the second list. The query results should display the FacultyID, FacultyFirst, and FacultyLast fields. (Hint: These should come from the second field list.) The query should also assign the captions Faculty ID, Faculty First Name, and Faculty Last Name, respectively, to these fields. The query results should also include the following fields for the supervisor of each faculty member: FacultyFirst, FacultyLast, and Position. (Hint: These should come from the first field list.) The query should also assign the captions Supervisor First Name, Supervisor Last Name, and Supervisor Position, respectively, to these fields. Sort the records in ascending order by the Faculty ID column. Resize all columns in the datasheet to best fit, save the query as qryFacultySupervisors, and then close the query.

9. Open the tblCityPaymentPlan table in Design view, add an index that allows duplicates for the City field, and then save and close the table.

10. Make a backup copy of the database, compact and repair the database, and then close it.