Classroom Learning Activity 16 Using Access for Queries and Excel for Analysis Starting Filename: UniversityDBcla16.accdb; GradeAnalysis.xIsx Submission Filenames: uniquelD_UniversityDBcla16.accdb; uniqueID_GradeAnalysis.xIsx This activity is to perform a query on the university database and to copy the results of the query into Excel for further analysis. This is similar to the kind of analysis you will be doing in Portfolio 2. Each student must submit their own file for this assignment, but we recommend you work closely with your Portfolio 2 partner on this exercise. Specifically, your goal is to determine "Which Department (do not use MajorID) represented in the university database has the highest average GPA in all courses offered and which Department has the lowest average GPA?". When you finish this CLA, you should have a chart similar to the sketch shown below. 1. Download the Day31_CLA16 files to your PC. Extract and save the files to your M:\\ drive. Rename UniversityDBcla16.accdb as GPA uniquelD_UniversityDBcla16.accdb. Rename GradeAnalysis.xIsx as uniquelD_GradeAnalysis.xlsx. You will submit both files at the end of this exercise. 2. Open the database and prepare a query that will provide the necessary data to compute the average GPA for each department (NOTE: - the actual GPA will be calculated in Excel in step 6). You will need to have counts of As, Bs, Cs, Ds, and Fs grouped by course credit hours and course department. Name the query GradeInformation. [Most solutions have 130 records; Check with your instructor or the student assistant to make sure your query is reasonable before proceeding.] I 3. Open Excel and use Sheet1 to create a documentation page with your name and date. We will copy data from Access into Excel using a simple "Copy Paste" method. Highlight the records in the Access query and copy them into Sheet2 of the Excel workbook. Name this worksheet AccessData. a. NOTE: This method also works when using the AppPortal - you must have both the Excel workbook and the Access database file open at the same time, and then you can copy and paste from Access into Excel. Select the AccessData sheet. Your goal at the end of the CLA is to use this data to produce a chart similar to the sketch above. Tip 1: It is often useful to add columns to the raw data to calculate additional fields based on the information from your query. Remember that to compute a GPA you will need to divide the total grade points by the total credit hours. Grade points are found by multiplying the credit hours of a course by the grade earned (A=4, B=3, C=2, D=1, and F=0, we will ignore +/-). In this case, it will be helpful to add columns to the imported data to compute the total grade points and the total credit hours. (Hint: You can use an IFS function or an exact match look-up table to help calculate grade points.) [COM - B's: Total Gr. Pts=918; Total Cr. Hrs. = 306] 5. Tip 2: It is often useful to use a pivot table to reorganize and summarize the data from your query. Create a pivot table to summarize the total credit points and total credit hours generated in each department. Place this pivot table on a worksheet named PivotTable, located after the Access Data sheet. Please check with your instructor to make sure your pivot table is reasonable before proceeding.] 6. Modify the pivot table to include a calculated field to calculate GPA for each department. Format with two decimals. [COM gpa = 2.43] 432 K Search LO DELL