CIS221 Access Project Instructions 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, inputting 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; creating a report; and backing up the database. Set Up the Database File and Import an Excel Worksheet To start, you have been provided with a database the Information Technology department created. The database has one table and one form. You will be importing an Excel spreadsheet into a table and creating a primary key. 1. Open College and save the database as College_LastFirst. 2. Import the Transfer Excel workbook into a new table named Transfer Schools. While importing the data, ensure that StudentID has a data type of Short Text, and select StudentID as the primary key. Modify a Table Now that you have imported the data from the spreadsheet, you will modify the field properties in the Transfer Schools table and demonstrate sorting. 1. Open the Transfer Schools table in Design view. 2. Set the StudentiD field size to 10. 3. Remove the @symbol from the Studentin format property. 4. Change the Admitting School field size to 75. 5. Change the Registration Fee and TuitionDue fields to have 0 decimal places. 6. Switch to Datasheet view. Resize the AdmittingSchool column by double-clicking on the border between Admitting School and AdmissionDate. 7. Sort the Transfer Schools table on the Credits Transferred field in ascending order. h. Save and close the table. Create Relationships Now that the table is imported and modified, you will create a relationship between the Transfer Schools and Transfer Students tables. 1. Add the Transfer Schools and Transfer Students tables to the Relationships window. 2. Create a one-to-one relationship between the StudentId field in the Transfer Students table and the StudentID field in the Transfer Schools table. Enforce referential integrity between the two tables and cascade updates. 3. Save the changes and close the Relationships window. Modify Data in a Form You will demonstrate changing information in a form. 1. Open the Transfer Students Data Entry form. 2. Change the major for Cornelius Kavanagh to Elementary Education. Close the form Create a Query Rey Rivera, a counselor in the center, would like your assistance in helping him find certain information. You will create a query for him and demonstrate how he can change information. 1. Create a new query using Design view. This query will access fields from both the Transfer Schools and Transfer Students tables. From the Transfer Students table, add the FirstName, LastName, Major, Class, and GPA fields. From the Transfer Schools table, add the AdmissionDate, Tuition Due, CreditsEarned, and Credits Transferred fields. 2. Save the query as Transfer Credits. 3. Set the criteria in the Admission Date field to 8/1/2018. Run the query (19 records will display) 4. Type $1500 in the TuitionDue field for Diana Sullivan and type 3.51 as the GPA for Audrey Owen 5. Save and close the query. Create Calculated fields 1. Now that you have created the query, you will create a second query for Rey that will calculate the number of credits students lost upon transfer, the tuition payments for which they will be responsible (assuming three payments per semester), and the due date of the first payment. 2. Create a copy of the Transfer Credits query. Name the copy Transfer Credit Calculations. Open the new query in Design view. 3. Remove the criteria from the AdmissionDate field. 4. Create a calculated field in the first empty field cell of the query named LostCredits that sub-tracts Credits Transferred from CreditsEarned. 5. Create another calculated field named Tuition Payments 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 student's tuition payment. Use 0 for the future_value and type arguments. Ensure that the payment appears as a positive number. 6. Format the Tuition Payments calculated field as Currency 7. Create another calculated field named FirstPayment after the Tuition Payments field. To calculate the due date, add 30 to their AdmissionDate. Run the query and verify that the three calculated fields have valid data. 8. Add a total row to the datasheet. Sum the TuitionDue column and average the Tuition Payment column. Save and close the query. Create a Totals Query Cala Hajjar, the director of the center, needs to summarize information about the transfer students for the 2018-2019 academic year to present to the College's Board of Trustees. You will create a totals query for her to summarize the number of transfer students, average number of credits earned and transferred, and total tuition earned by transfer institution. 1. Create a new query in Design view. Add the Transfer Schools table. 2. Add the AdmittingSchool, StudentID. Credits Earned. Credits Transferred, and TuitionDue fields. 3. Sort the query by AdmittingSchool in ascending order. 4. Show the Total row. Group by AdmittingSchool and show the count of StudentID, the average of Credits Earned, the average of Credits Transferred, and the sum of Tuition Due. 5. Format both average fields as Standard. 6. Change the caption for the StudentID field to NumStudents, the caption for the CreditsEarned average to AvgCreditsEarned, the caption for the Credits Transferred average to AvgCredits Transferred, and the caption for the sum of Tuition Due to Total Tuition. 7. Run the query 8. Save the query as Transfer Summary 9. Close the query Create a Form Hideo Sasaki, the department's administrative assistant, will handle data entry. He has asked you to simplify the way he inputs information into the new table. You will create a form based on the new Transfer Schools table. 1. Create a Split Form using the Transfer Schools table as the source. 2. Change the height of the AdmittingSchool field to be approximately half the current height 3. Remove the layout from all the labels and fields. Shrink each field so it is approximately as large as it needs to be. 4. Click record 123455 in the bottom half of the split form. Make sure all values are still visible in the top half of the form. If not, adjust the controls so all values are visible. 5. Move the credits Transferred field so it is to the right of the CreditsEarned field on the same row. 6. Change the format of the Tuition Due field so the font size is 18 and the font color is Red (last row, second column in the Standard Colors section). Resize the field if necessary so the entire value displays. 7. Change the fill color of the StudentID field to be Yellow (last row, fourth column in the Standard Colors section). 8. Save the form as Transfer Schools Form. Save and close the form. Create a Report Cala is hoping you can create a more print-friendly version of the query you created earlier for her to distribute to the Board of Trustees. You will create a report based on the Transfer Credit Calculations query. 1. 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 that the report is in Landscape orientation 2. Save the report as Transfer Students Report and view the report in Layout view. Format a Report Now that you have included the fields Cala has asked for, you will work to format the report to make the information more obvious. 1. Apply the Wisp theme (last row, first column) to this object only. 2. Group the report by the Class field. Sort the records within each group by LastName then by FirstName, both in ascending order. 3. Change the font size of the Class field to 16. 4. Adjust the text boxes so the values for the Major field are completely visible. 5. Switch to Print Preview mode and verify that the report is only one page wide (Note: It may be a number of pages long). 6. Export the results as a PDF document using the file name Transfer_LastFirst. 7. Save and close the report. Close and Submit the Database You want to backup the database and submit all of your files. 1. Create a backup of the database. Accept the default name for the backup 2. Close all database objects and exit Access. Based on your instructor's directions, submit the following: College_LastFirst College_LastFirst_CurrentDate Transfer_LastFirst CIS221 Access Project Instructions 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, inputting 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; creating a report; and backing up the database. Set Up the Database File and Import an Excel Worksheet To start, you have been provided with a database the Information Technology department created. The database has one table and one form. You will be importing an Excel spreadsheet into a table and creating a primary key. 1. Open College and save the database as College_LastFirst. 2. Import the Transfer Excel workbook into a new table named Transfer Schools. While importing the data, ensure that StudentID has a data type of Short Text, and select StudentID as the primary key. Modify a Table Now that you have imported the data from the spreadsheet, you will modify the field properties in the Transfer Schools table and demonstrate sorting. 1. Open the Transfer Schools table in Design view. 2. Set the StudentiD field size to 10. 3. Remove the @symbol from the Studentin format property. 4. Change the Admitting School field size to 75. 5. Change the Registration Fee and TuitionDue fields to have 0 decimal places. 6. Switch to Datasheet view. Resize the AdmittingSchool column by double-clicking on the border between Admitting School and AdmissionDate. 7. Sort the Transfer Schools table on the Credits Transferred field in ascending order. h. Save and close the table. Create Relationships Now that the table is imported and modified, you will create a relationship between the Transfer Schools and Transfer Students tables. 1. Add the Transfer Schools and Transfer Students tables to the Relationships window. 2. Create a one-to-one relationship between the StudentId field in the Transfer Students table and the StudentID field in the Transfer Schools table. Enforce referential integrity between the two tables and cascade updates. 3. Save the changes and close the Relationships window. Modify Data in a Form You will demonstrate changing information in a form. 1. Open the Transfer Students Data Entry form. 2. Change the major for Cornelius Kavanagh to Elementary Education. Close the form Create a Query Rey Rivera, a counselor in the center, would like your assistance in helping him find certain information. You will create a query for him and demonstrate how he can change information. 1. Create a new query using Design view. This query will access fields from both the Transfer Schools and Transfer Students tables. From the Transfer Students table, add the FirstName, LastName, Major, Class, and GPA fields. From the Transfer Schools table, add the AdmissionDate, Tuition Due, CreditsEarned, and Credits Transferred fields. 2. Save the query as Transfer Credits. 3. Set the criteria in the Admission Date field to 8/1/2018. Run the query (19 records will display) 4. Type $1500 in the TuitionDue field for Diana Sullivan and type 3.51 as the GPA for Audrey Owen 5. Save and close the query. Create Calculated fields 1. Now that you have created the query, you will create a second query for Rey that will calculate the number of credits students lost upon transfer, the tuition payments for which they will be responsible (assuming three payments per semester), and the due date of the first payment. 2. Create a copy of the Transfer Credits query. Name the copy Transfer Credit Calculations. Open the new query in Design view. 3. Remove the criteria from the AdmissionDate field. 4. Create a calculated field in the first empty field cell of the query named LostCredits that sub-tracts Credits Transferred from CreditsEarned. 5. Create another calculated field named Tuition Payments 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 student's tuition payment. Use 0 for the future_value and type arguments. Ensure that the payment appears as a positive number. 6. Format the Tuition Payments calculated field as Currency 7. Create another calculated field named FirstPayment after the Tuition Payments field. To calculate the due date, add 30 to their AdmissionDate. Run the query and verify that the three calculated fields have valid data. 8. Add a total row to the datasheet. Sum the TuitionDue column and average the Tuition Payment column. Save and close the query. Create a Totals Query Cala Hajjar, the director of the center, needs to summarize information about the transfer students for the 2018-2019 academic year to present to the College's Board of Trustees. You will create a totals query for her to summarize the number of transfer students, average number of credits earned and transferred, and total tuition earned by transfer institution. 1. Create a new query in Design view. Add the Transfer Schools table. 2. Add the AdmittingSchool, StudentID. Credits Earned. Credits Transferred, and TuitionDue fields. 3. Sort the query by AdmittingSchool in ascending order. 4. Show the Total row. Group by AdmittingSchool and show the count of StudentID, the average of Credits Earned, the average of Credits Transferred, and the sum of Tuition Due. 5. Format both average fields as Standard. 6. Change the caption for the StudentID field to NumStudents, the caption for the CreditsEarned average to AvgCreditsEarned, the caption for the Credits Transferred average to AvgCredits Transferred, and the caption for the sum of Tuition Due to Total Tuition. 7. Run the query 8. Save the query as Transfer Summary 9. Close the query Create a Form Hideo Sasaki, the department's administrative assistant, will handle data entry. He has asked you to simplify the way he inputs information into the new table. You will create a form based on the new Transfer Schools table. 1. Create a Split Form using the Transfer Schools table as the source. 2. Change the height of the AdmittingSchool field to be approximately half the current height 3. Remove the layout from all the labels and fields. Shrink each field so it is approximately as large as it needs to be. 4. Click record 123455 in the bottom half of the split form. Make sure all values are still visible in the top half of the form. If not, adjust the controls so all values are visible. 5. Move the credits Transferred field so it is to the right of the CreditsEarned field on the same row. 6. Change the format of the Tuition Due field so the font size is 18 and the font color is Red (last row, second column in the Standard Colors section). Resize the field if necessary so the entire value displays. 7. Change the fill color of the StudentID field to be Yellow (last row, fourth column in the Standard Colors section). 8. Save the form as Transfer Schools Form. Save and close the form. Create a Report Cala is hoping you can create a more print-friendly version of the query you created earlier for her to distribute to the Board of Trustees. You will create a report based on the Transfer Credit Calculations query. 1. 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 that the report is in Landscape orientation 2. Save the report as Transfer Students Report and view the report in Layout view. Format a Report Now that you have included the fields Cala has asked for, you will work to format the report to make the information more obvious. 1. Apply the Wisp theme (last row, first column) to this object only. 2. Group the report by the Class field. Sort the records within each group by LastName then by FirstName, both in ascending order. 3. Change the font size of the Class field to 16. 4. Adjust the text boxes so the values for the Major field are completely visible. 5. Switch to Print Preview mode and verify that the report is only one page wide (Note: It may be a number of pages long). 6. Export the results as a PDF document using the file name Transfer_LastFirst. 7. Save and close the report. Close and Submit the Database You want to backup the database and submit all of your files. 1. Create a backup of the database. Accept the default name for the backup 2. Close all database objects and exit Access. Based on your instructor's directions, submit the following: College_LastFirst College_LastFirst_CurrentDate Transfer_LastFirst