Reference no: EM131038268
Transfer Student Registration
Project Description:
You were recently hired by your local college to help with registering all transfer students. The college's Transfer Counseling Department is a one-stop location for transfer students to come with questions. They have been working with Excel spreadsheets generated by the Information Technology department, but they are hoping to do more with an Access database. They have had a number of problems, including employees putting information in the wrong fields, putting information in the wrong format, and creating incorrect formulas. They are also hoping for more consistent ways of finding information, as well as being able to generate reports. Your tasks include importing an existing Excel worksheet as a table into your Access database; modifying the table; creating a relationship between two tables; creating queries with calculated fields, functions, and totals; creating a form for input; and creating a report.
Instructions
1 Start Access. Open the downloaded Access database named exploring_acap_grader_h1_College.accdb.
2 Import the exploring_acap_grader_h1_Transfer.xlsx Excel workbook into a table named Transfer Schools. While importing the data, choose StudentID as the primary key field.
Ensure StudentID has a data type of Short Text. Change the StudentID field size to 10 and remove the @ symbol from its Format property. Change the AdmittingSchool field size to 75.
Set the RegistrationFee and TuitionDue fields to have no decimal places.
3 There is nothing to do for this step. Please proceed to the next step.
4 Create a relationship between the Transfer Students and Transfer Schools tables. Enforce referential integrity, but do not enable cascade updates or cascade deletes.
5 There is nothing to do for this step. Please proceed to the next step.
6 Create a new query that lists, in order, each student's first name, last name, major, class, GPA, admission date, tuition due, credits earned, and credits transferred. Name the query as Transfer Credits.
Only include records where the admission date was 8/1/2015.
When you run the query, you must set the tuition due for Diana Sullivan to $1500 and the GPA for Audrey Owen to 3.51.
Hint: This query will show 18 records and 9 fields.
7 Copy the Transfer Credits query to create a new query named Transfer Credit Calculations. Remove the criteria from the admission date. Create a calculated field named LostCredits to determine the number of lost credits.
You can calculate the number of lost credits using the formula:
[CreditsEarned] - [CreditsTransferred]
Hint: This query will show 39 records and 10 fields.
8 There is nothing to do for this step. Please proceed to the next step.
9 There is nothing to do for this step. Please proceed to the next step.
10 Create a new query named Transfer Summary. For each admitting school, find the number of student IDs, the average of credits earned, the average of credits transferred, and the sum of tuition due.
Sort by admitting school in ascending order.
Hint: This query will show 24 records and 5 fields.
11 There is nothing to do for this step. Please proceed to the next step.
12 There is nothing to do for this step. Please proceed to the next step.
13 There is nothing to do for this step. Please proceed to the next step.
14 Using the Report Wizard, create a report to show the class, first name, last name, major, GPA, and lost credits fields from the Transfer Credit Calculations query.
Group by class and sort by last name and then by first name, both in ascending order. Use a stepped layout and landscape page orientation. Name the report Transfer Students Report.
15 There is nothing to do for this step. Please proceed to the next step.
16 There is nothing to do for this step. Please proceed to the next step.
Attachment:- Transfer Student Registration.zip