Transfer Student Registration
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.
For the purpose of grading the project you are required to perform the following tasks:
Start Access. Open the downloaded Access file namedexploring_acap_grader_h1_College.
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 the StudentID format property. Change the AdmittingSchool field size to 75. Change the RegistrationFee and TuitionDue fields to have 0 decimal places.
Switch to Datasheet View and apply Best Fit to all columns. Sort the table on the CreditsTransferred field in ascending order, then save and close the table.
Create a one-to-one relationship between the StudentID fields in the Transfer Students (primary) and Transfer Schools (related) tables. Enforce referential integrity between the two tables. Save the changes and close the Relationships window.
Open the Transfer Students Data Entry form. Change the major for Cornelius Kavanaugh to Elementary Educationand close the form.
Create a new query using Design view. From the Transfer Students table, add the FirstName, LastName, Major, Class, and GPA fields, in that order. From the Transfer Schools table, add the AdmissionDate, TuitionDue, CreditsEarned, and CreditsTransferred fields, in that order. Save the query asTransfer Credits. Set the criteria in the AdmissionDate field to 8/1/2015. Run the query. Enter the TuitionDue for Diana Sullivan to $1500 and the GPA for Audrey Owen as 3.51. Save the query.
Switch to Design view and save the Transfer Credits query asTransfer Credit Calculations. Remove the criteria from the AdmissionDate field. Create a calculated field in the first empty cell of the query named LostCredits that subtracts CreditsTransferred from CreditsEarned.
Create another calculated field named TuitionPayments that determines tuition paid in three installments. Using the Pmt function, replace the rate argument with 0.025/3, the num_periods argument with 3, and the present_value argument with the TuitionDue. Use 0 for the future_value and type arguments. Ensure the payment appears as a positive number. Format the field as Currency.