Transfer Student Registration

Project Description:

You were recently hired by your local college to help with registering all transfer students. The colleges 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:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Start Access. Open the downloaded Access file named exploring_acap_grader_h1_College.	0
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 the StudentID format property. Change the AdmittingSchool field size to 75. Change the RegistrationFee and TuitionDue fields to have 0 decimal places. Hint: To import the workbook, on the EXTERNAL DATA tab, in the Import & Link group, click Excel. Use the Field Properties pane to change the field sizes and decimal properties.	4
3	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. Hint: To apply Best Fit, select the columns and double-click any column heading's right border. To sort the table, click in the column and on the HOME tab, in the Sort & Filter group, click Ascending.	2
4	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.	6
5	Open the Transfer Students Data Entry form. Change the major for Cornelius Kavanaugh to Elementary Education and close the form.	3
6	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 as Transfer 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. Hint: On the CREATE tab, in the Queries group, click Query Design. In the Query Design grid, click the Criteria row for the AdmissionDate field and enter 8/1/2015. To run the query, on the DESIGN tab, in the Results group, click Run.	5
7	Switch to Design view and save the Transfer Credits query as Transfer 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. Hint: On the FILE tab, click Save As, and then click Save Object As. To create the calculated field, click in the first blank field and enter LostCredits: [CreditsEarned]-[CreditsTransferred].	4
8	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. Hint: Right-click the first empty field next to LostCredits and click Build. The calculated field should be TuitionPayments: Pmt(0.025/3,3,-[Transfer Schools]![TuitionDue],0,0)	4
9	Create another calculated field named DueDate after TuitionPayments that calculates the due date by adding 30 to the AdmissionDate. Run the query. Add a total row to the query. Average the GPA column and sum the LostCredits column. Save and close the query. Hint: The calculated field should be DueDate: [AdmissionDate]+30. To add the total row, on the HOME tab, in the Records group, click Totals. Click in the respective cells and then click the down arrow to average and sum the columns.	4
10	Create a new query using Design View. From the Transfer Schools table, add the AdmittingSchool, StudentID, CreditsEarned, CreditsTransferred, and TuitionDue fields. Sort the query by AdmittingSchool in ascending order. Display the Total row and group by AdmittingSchool. Show the count of StudentID, the average CreditsEarned and CreditsTransferred, and the sum of TuitionDue. Hint: To sort the query, click in the Sort row of AdmittingSchool and then click the arrow. To display the Total row, on the DESIGN tab, in the Show/Hide group, click Totals. Click in the Group By row to apply the count, average, and sum commands.	5
11	Format both average fields as Standard. Change the caption for the StudentID field to NumStudents, the caption for the CreditsEarned average to AvgCreditsEarned, the caption for the CreditsTransferred average to AvgCreditsTransferred, and the caption for TuitionDue to TotalTuition. Run the query. Apply Best Fit to all columns. Save the query as Transfer Summary and close it. Hint: To change the captions, on the DESIGN tab, in the Show/Hide group, click Property Sheet. To apply Best Fit, select the columns and double-click any column heading's right border.	3
12	Create a Split Form using the Transfer Schools table as the source. Change the height of the AdmittingSchool field to 0.25". Remove the layout. Adjust the width of the StudentID field to 0.5903", the AdmittingSchool field to 3.05", the AdmissionDate field to 0.8", the CreditsEarned and CreditsTransferred fields to 0.25", the RegistrationFee field to 0.425", and the TuitionDue field to 0.8". Hint: With the Transfer Schools table selected in the Navigation Pane, on the CREATE tab, in the Forms group, click More Forms and then click Split Form. Use the Property Sheet to change the height and width of the fields. To remove the layout, switch to Design View, select a field, on the ARRANGE tab, in the Rows & Columns group, click Select Layout, then in the Table group, click Remove Layout.	4
13	Change the CreditsTransferred label's Top property to 1.4167" and its Left property to 2.25". Change the CreditsTransferred field's Top property to 1.4167" and its Left property to 4". Change the format of the TuitionDue field so the font is 18 and the font color is Red. Change the fill color of the StudentID field to be Yellow. Save the form as Transfer Schools Form and close it. Hint: Use the Property Sheet to change the position of the label and field. Use the tools in the Text Formatting group on the HOME tab to change the font and fill formatting.	2
14	Create a report using the Report Wizard. Add the Class, FirstName, LastName, Major, GPA, and LostCredits fields from the Transfer Credit Calculations query. Do not add any grouping or sorting. Ensure the report is in Landscape orientation. Save the report as Transfer Students Report and view it in Layout view.	3
15	Apply the Wisp theme to only the report. Group the report by the Class field. Sort the records within each group by LastName and then by FirstName, both in ascending order. Change the font size of the Class field to 16. Save and close the report. Hint: On the DESIGN tab, in the Themes group, click Themes. To group and sort, on the DESIGN tab, in the Grouping & Totals group, click Group & Sort. To change the font size, on the HOME tab, in the Text Formatting group, click the Font Size arrow.	3
16	Close all database objects. Close the database and then exit Access. Submit the database as directed.	0
	Total Points	52