{"id":47744,"date":"2022-04-01T21:43:11","date_gmt":"2022-04-01T21:43:11","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/exp19_access_app_capassessment-transfer-student-advising-1-2\/"},"modified":"2022-04-01T21:43:11","modified_gmt":"2022-04-01T21:43:11","slug":"exp19_access_app_capassessment-transfer-student-advising-1-2","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/exp19_access_app_capassessment-transfer-student-advising-1-2\/","title":{"rendered":"Exp19_access_app_capassessment – transfer student advising 1.2"},"content":{"rendered":"
\n

Exp19_Access_App_CapAssessment – Transfer Student Advising 1.2<\/strong><\/p>\n

\u00a0\u00a0<\/p>\n

Project Description:<\/strong><\/h2>\n

You were recently hired by your local college to help with registering all transfer students. The college?s Transfer Advising 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; creating a report; and backing up the database.<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n

To start, you have been provided with a database \u00a0\u00a0the Information Technology department created. The database has one table and \u00a0\u00a0one form. You will be importing an Excel spreadsheet into a table and \u00a0\u00a0creating a primary key.
\u00a0<\/p>\n

\u00a0\u00a0Start Access. Download and open the file named aApp_Cap1_Advising.accdb<\/em>. Grader has automatically added your \u00a0\u00a0last name to the beginning of the filename.<\/p>\n

\u00a0<\/p>\n

Import \u00a0\u00a0the aApp_Cap1_Transfer.xlsx<\/em> Excel \u00a0\u00a0workbook into a new table named Transfer Schools<\/strong>. \u00a0\u00a0While importing the data, ensure that StudentID has a data type of Short \u00a0\u00a0Text, and select StudentID as the primary key.<\/p>\n

\u00a0<\/p>\n

Now \u00a0\u00a0that you have imported the data from the spreadsheet, you will modify the \u00a0\u00a0field properties in the Transfer Schools table and demonstrate sorting.
\u00a0<\/p>\n

\u00a0\u00a0Open the Transfer Schools table in Design view. Set the StudentID field size \u00a0\u00a0to 10<\/strong>. Remove the @ symbol from the \u00a0\u00a0StudentID format property. Change the AdmittingSchool field size to 75<\/strong>. Change the RegistrationFee and \u00a0\u00a0TuitionDue fields to have 0<\/strong> \u00a0\u00a0decimal places.
\u00a0<\/p>\n

\u00a0\u00a0Switch to Datasheet view, saving the changes that you’ve made. Resize the \u00a0\u00a0AdmittingSchool column by double-clicking on the border between \u00a0\u00a0AdmittingSchool and AdmissionDate. Sort the Transfer Schools table on the \u00a0\u00a0CreditsTransferred field in ascending order. Save and close the table.<\/p>\n

\u00a0<\/p>\n

Now \u00a0\u00a0that the table is imported and modified, you will create a relationship \u00a0\u00a0between the Transfer Schools and Transfer Students tables.
\u00a0<\/p>\n

\u00a0\u00a0Add the Transfer Schools and Transfer Students tables to the Relationships \u00a0\u00a0window. Create a one-to-one relationship between the StudentID field in the \u00a0\u00a0Transfer Students table and the StudentID field in the Transfer Schools \u00a0\u00a0table. Enforce referential integrity between the two tables and cascade \u00a0\u00a0updates and deletes. Save the changes and close the Relationships window.<\/p>\n

\u00a0<\/p>\n

\u00a0\u00a0\u00a0You will demonstrate changing information in a form.
\u00a0<\/p>\n

\u00a0\u00a0Open the Transfer Students Data Entry form. Locate the record for Ellen Sullivan<\/em> and change her major to \u00a0\u00a0History<\/strong>. Close the form.<\/p>\n

\u00a0<\/p>\n

Adam \u00a0\u00a0McChesney, an adviser in the center, would like your assistance in helping \u00a0\u00a0him find certain information. You will create a query for him and demonstrate \u00a0\u00a0how he can change information.
\u00a0<\/p>\n

\u00a0\u00a0Create a new query using Design view. This query will access fields from both \u00a0\u00a0the Transfer Schools and Transfer Students tables. From the Transfer Students \u00a0\u00a0table, add the FirstName, LastName, Major, Class, and GPA fields. From the \u00a0\u00a0Transfer Schools table, add the AdmissionDate, TuitionDue, CreditsEarned, and \u00a0\u00a0CreditsTransferred fields.
\u00a0<\/p>\n

\u00a0\u00a0Save the query as Transfer Credits<\/strong>. \u00a0\u00a0Set the criteria in the AdmissionDate field to 1\/1\/2021<\/strong>. \u00a0\u00a0Run the query (19 records will display). Enter the TuitionDue for Bianca Bain<\/em> as $2200<\/strong> and the GPA for Edgar Conway<\/em> as 3.65<\/strong>. \u00a0\u00a0Save and close the query.<\/p>\n

\u00a0<\/p>\n

Now \u00a0\u00a0that you have created the query, you will create a second query for Adam that \u00a0\u00a0will calculate the number of credits students lost upon transfer, the tuition \u00a0\u00a0payments for which they will be responsible (assuming four payments per \u00a0\u00a0semester), and the due date of the first payment.
\u00a0<\/p>\n

\u00a0\u00a0Create a copy of the Transfer Credits query. Name the copy Transfer Calculations<\/strong>. Open the new query in Design view. \u00a0\u00a0Remove the criteria from the AdmissionDate field. Create a calculated field \u00a0\u00a0in the first empty field cell of the query named LostCredits<\/strong> \u00a0\u00a0that subtracts CreditsTransferred from CreditsEarned. Create another \u00a0\u00a0calculated field named TuitionPayments<\/strong> \u00a0\u00a0that uses the payment function and determines tuition paid in four \u00a0\u00a0installments. The student\u2019s tuition payment and a 2.5% interest \u00a0\u00a0rate should be used in the function. Use 0<\/strong> \u00a0\u00a0for the future_value and type arguments. Ensure that the payment appears as a \u00a0\u00a0positive number.
\u00a0<\/p>\n

\u00a0\u00a0Format the TuitionPayments calculated field as Currency. Create another \u00a0\u00a0calculated field named FirstPayment<\/strong> \u00a0\u00a0after the TuitionPayments field. To calculate the due date, add 30<\/strong> to their AdmissionDate. Run the query \u00a0\u00a0and verify that the three calculated fields have valid data. Add a total row \u00a0\u00a0to the datasheet. Sum the TuitionDue column and average the TuitionPayment column. \u00a0\u00a0Save and close the query.<\/p>\n

\u00a0<\/p>\n

\u00a0Cala Hajjar, the \u00a0\u00a0director of the center, needs to summarize information about the transfer \u00a0\u00a0students for the 2020\u20132021 academic year to present to the \u00a0\u00a0College\u2019s Board of Trustees. You will create \u00a0\u00a0a totals query for her to summarize the number of transfer students, average \u00a0\u00a0number of credits earned and transferred, and total tuition earned by \u00a0\u00a0transfer institution.
\u00a0<\/p>\n

\u00a0\u00a0Create a new query in Design view. Add the Transfer Schools table. Add the \u00a0\u00a0AdmittingSchool, StudentID, CreditsEarned, CreditsTransferred, and TuitionDue \u00a0\u00a0fields. Sort the query by AdmittingSchool in ascending order.
\u00a0<\/p>\n

\u00a0\u00a0Show the Total row. Group by AdmittingSchool and show the count of StudentID, \u00a0\u00a0the average of CreditsEarned, the average of CreditsTransferred, and the sum \u00a0\u00a0of TuitionDue. Format both average fields as Standard. Change the caption for \u00a0\u00a0the StudentID field to NumStudents<\/strong>, \u00a0\u00a0the caption for the CreditsEarned average to AvgCreditsEarned<\/strong>, \u00a0\u00a0the caption for the CreditsTransferred average to AvgCreditsTransferred<\/strong>, \u00a0\u00a0and the caption for the sum of TuitionDue to TotalTuition<\/strong>.
\u00a0<\/p>\n

\u00a0\u00a0Run the query. Save the query as Transfer Summary<\/strong>. \u00a0\u00a0Close the query.<\/p>\n

\u00a0<\/p>\n

Hideo \u00a0\u00a0Sasaki, the department\u2019s administrative assistant, will handle \u00a0\u00a0data entry. He has asked you to simplify the way he inputs information into \u00a0\u00a0the new table. You will create a form based on the new Transfer Schools \u00a0\u00a0table.
\u00a0<\/p>\n

\u00a0\u00a0Create a Split Form using the Transfer Schools table as the source. Change \u00a0\u00a0the height of the AdmittingSchool field to be approximately half the current \u00a0\u00a0height.Switch to design view and then remove the layout from all the labels \u00a0\u00a0and fields. Shrink each field so it is approximately as large as it needs to \u00a0\u00a0be.
\u00a0<\/p>\n

\u00a0\u00a0Switch to layout view and then click record 123455<\/em> in the bottom half of the split form. Make sure all fields \u00a0\u00a0are still visible in the top half of the form. If not, adjust the controls so \u00a0\u00a0all values are visible. Move the CreditsTransferred field so it is to the \u00a0\u00a0right of the CreditsEarned field on the same row.
\u00a0\u00a0Change the title of the form to Transfer Schools \u00a0\u00a0Overview<\/strong>. \u00a0\u00a0Save the form as Transfer Schools Form<\/strong>. \u00a0\u00a0Save and close the form.<\/p>\n

\u00a0<\/p>\n

Cala \u00a0\u00a0is hoping you can create a more print-friendly version of the query you \u00a0\u00a0created earlier for her to distribute to the Board of Trustees. You will \u00a0\u00a0create a report based on the Transfer Calculations query.
\u00a0<\/p>\n

\u00a0\u00a0Create a report using the Report Wizard. Add the Major, FirstName, LastName, \u00a0\u00a0Class, GPA, and LostCredits fields from the Transfer Calculations query. Do \u00a0\u00a0not add any grouping or sorting. Ensure that the report is in Landscape \u00a0\u00a0orientation. Save the report as Transfer Students \u00a0\u00a0Report<\/strong> \u00a0\u00a0and view the report in Layout view.<\/p>\n

\u00a0<\/p>\n

Now \u00a0\u00a0that you have included the fields Cala has asked for, you will work to format \u00a0\u00a0the report to make the information more obvious.
\u00a0<\/p>\n

\u00a0\u00a0Apply the attached Wisp theme. Group the report by the Major field. Sort the \u00a0\u00a0records within each group by LastName then by FirstName, both in ascending \u00a0\u00a0order. Adjust the text boxes so the values for the Major field are completely \u00a0\u00a0visible. Switch to Print Preview mode and verify that the report is only one \u00a0\u00a0page wide (Note: it may be a number of pages long).<\/p>\n

\u00a0<\/p>\n

Close \u00a0\u00a0all database objects. Close the database and then exit Access. Submit the \u00a0\u00a0database as directed.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Exp19_Access_App_CapAssessment – Transfer Student Advising 1.2 \u00a0\u00a0 Project Description: You were recently hired by your local college to help with registering all transfer students. The college?s Transfer Advising 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-47744","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/47744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=47744"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/47744\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=47744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=47744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=47744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}