Access Cram Kit – Miami

This is a FREE DEMO of the actual Access Cram Kit.

If you have already purchased the complete Access Cram Kit, click here.

To obtain the complete Access Cram Kit, click the button below:

Instructions

Download the Access Cram Kit ZIP file by clicking the link below. It contains 3 files:

• AcademyInfo.accdb
• DataAnalysis.xlsx
• FogMachineToYou.accdb

We'll be starting with the FogMachineToYou.accdb file, then moving on to AcademyInfo.accdb.

Click the link below to download the Access Cram Kit ZIP file!

Module 1 - Query (Design View #1)

1. Open the FogMachineToYou database. This details a database for a local college fog machine delivery business.
2. Modify the Q3_Commission query, and alter it according to the following steps:
   • Create a calculated field to determine the 8.5% commission that each FogMachineToYou campus ambassador will receive on each ContractAmount. Name this field to Commission.
   • Format this new field as a currency.
   • Save the table. (This ensures that the calculated field displays.)
   • Remove the Duration field.
   • Create a new calculated field that subtracts the Commission field from the ContractAmount field. Name this new calculated field CompanyEarnings.
   • Run the query. You should get 14 records.
   • Resize columns if necessary to yield the best fit.
   • Save and close the query.

Module 2 - Relationships

1. Open AcademyInfo.accdb. This is a database of all Crammer Academy coding student statistics.
2. The relationship between Language and Curriculum has not been made yet. Create a "1 to Many" relationship between them with Referential Integrity and Cascade Updates.

Module 3 - Query (Design View #2)

Complete the following queries in the AcademyInfo.accdb file.

You must complete each of the below query using Query Design mode, not SQL. Make sure that for each query, you resize columns to the best fit.

1. Q1_InputLanguage
   • List all classes curriculum (CurriculumID, CurriculumNumber, CreditHours) from a user-inputted language (the user should be prompted to enter a value for language after running the query).Sort numerically in ascending order by CurriculumNumber.Run the query and enter "PY" as the language. You should get 8 records.Save the query as Q1_InputLanguage.Q2_InputStudentYear
   • Display all student information (StudentID, First, Last, LanguageID) where the user enters all or part of the student's graduation year.Run the query. If you enter 25, you should get 56 records.Save the query as Q2_InputStudentYear.
2. Q2_InputStudentYear
   • Display all student information (StudentID, First, Last, LanguageID) where the user enters all or part of the student's graduation year.
   • Run the query. If you enter 25, you should get 56 records.
   • Save the query as Q2_InputStudentYear.

Module 4 - Query (SQL)

Complete the following queries in the AcademyInfo.accdb file.

You must complete each of the below query using SQL, not Design View. Make sure that for each query, you resize columns to the best fit.

1. Q11_SQL_Classes2022S2
   • Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.
   • Run the query. You should get 31 records.
   • Save the query as Q11_SQL_Classes2022S2.
2. Q12_SQL_OhioAccessStudents
   • Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.
   • Run the query. You should get 11 records.
   • Save the query as Q12_SQL_OhioAccessStudents.

Module 5 - Data Analysis

#1: Provide an analysis and summary that compares the average number of students enrolled for classes taught by Conor Horn and Naomi Russell vs. all other professors. (It is important to note that the number of students enrolled is different than the MaxEnrollment field in the Class table.) To calculate the average actual enrollment, you must first determine the total number of students enrolled for Conor Horn and Naomi Russell classes and all other professors' classes, and then calculate the average across all Conor Horn and Naomi Russell classes and the average across all other professors' classes.