{"id":89874,"date":"2022-08-22T12:53:42","date_gmt":"2022-08-22T12:53:42","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/ex19_ac_comp_grader_cap_as-student-loans-1-0\/"},"modified":"2022-08-22T12:53:42","modified_gmt":"2022-08-22T12:53:42","slug":"ex19_ac_comp_grader_cap_as-student-loans-1-0","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/ex19_ac_comp_grader_cap_as-student-loans-1-0\/","title":{"rendered":"Ex19_ac_comp_grader_cap_as – student loans 1.0"},"content":{"rendered":"
\n

\u00a0EX19_AC_COMP_GRADER_CAP_AS – Student Loans 1.0<\/strong><\/p>\n

\u00a0\u00a0<\/p>\n

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

In this project, you will add fields to a table and set data validation rules. You will also import a text file into a database, design advanced queries, and create a navigation form. Additionally, you will use SQL to modify a record source and create an embedded macro to automate opening a report.<\/p>\n

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

Start Access. Open the file named Exp19_Access_AppCapstone_CompAssessment_Student Loans<\/em>.accdb.<\/em> Grader has automatically added your last name to the \u00a0\u00a0beginning of the filename. Save the file to the location where you are \u00a0\u00a0storing your files.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will import a text file into the database so that you do not have to create \u00a0\u00a0it from scratch.
\u00a0<\/p>\n

\u00a0\u00a0Create a table in the database by importing the downloaded tab-delimited text \u00a0\u00a0file named Loans_Text.txt<\/em>. Use the \u00a0\u00a0first row of the file as field names, use ClientID as the primary key, and \u00a0\u00a0then name the table Loans_Text<\/strong>. \u00a0\u00a0Accept all other default options. Do not save the import steps.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a new field in the Colleges table after Phone named Website <\/strong>with the Hyperlink data type<\/strong>. Save the table. In Datasheet view, add the \u00a0\u00a0website www.atlantic.edu<\/strong> to the Atlantic Cape Community College<\/em> record (Record 1). Close the \u00a0\u00a0table.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a new field in the Clients table after CollegeID named StudentPhoto<\/strong> with the Attachment data type<\/strong>. Save the table. In Datasheet view for \u00a0\u00a0Record 1 (Rabiah Hussein<\/em>), add the \u00a0\u00a0downloaded picture file named RHussein.jpg<\/em> \u00a0\u00a0to the StudentPhoto field.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will set a rule that will prevent inaccurate data entry in the table.
\u00a0<\/p>\n

\u00a0\u00a0Set the validation rule on the ExpectedGraduation field to accept the values 2021 <\/strong>or 2022 <\/strong>or 2023<\/strong> (in that order) only. Set the \u00a0\u00a0validation text to read Please enter 2021, \u00a0\u00a02022, or 2023.<\/strong> (include the period).<\/p>\n

\u00a0<\/p>\n

Make \u00a0\u00a0ClientID<\/strong> a lookup field in the \u00a0\u00a0Clients table. Set the lookup to get values from the ClientID field<\/strong> in the Loans_Text \u00a0\u00a0table<\/strong>. Accept all other defaults and save the table. In Datasheet view, \u00a0\u00a0click in any ClientID cell, and click the arrow to view the options. Close the \u00a0\u00a0table.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will copy a query and use it as the basis for a new query.
\u00a0<\/p>\n

\u00a0\u00a0Create a copy of the 2022 Graduates \u00a0\u00a0query<\/strong>. Save the query as Missing Phone<\/strong>.<\/p>\n

\u00a0<\/p>\n

Set \u00a0\u00a0the criteria in the Phone field to find null values. Delete the criteria of 2022<\/em> from the ExpectedGraduation \u00a0\u00a0field. Run the query (two records will display). Save and close the query.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a new query using Design view. From the Clients table, add the ZIP field<\/strong>. Change the query type to Update<\/strong> and set the criteria to update \u00a0\u00a0zip codes of 07003<\/strong> \u00a0\u00a0to 07003-9999<\/strong>. Run the query (two records will \u00a0\u00a0update). Save the query as Zip Code_Update<\/strong> \u00a0\u00a0and close the query. Use the Find command to view the updates in the Clients \u00a0\u00a0table and close the table.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a new query using Design view. From the Clients table, add the FirstName<\/strong>, then LastName<\/strong> fields. From the Loans_Text table, add LoanAmount<\/strong>. Save the query as Large Loans_Make Table<\/strong>.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will modify the query so that when run, a new table is created in the \u00a0\u00a0database.
\u00a0<\/p>\n

\u00a0\u00a0Change the query type to Make Table<\/strong> \u00a0\u00a0and set the criteria to loan amounts that are greater than 25000<\/strong>. Run the query (fours records will be \u00a0\u00a0added). Set the new table name to Large Loans<\/strong>. \u00a0\u00a0Save and close the query. View the changes in the Large Loans table <\/strong>and close the table.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a copy of the 2022 Graduates<\/strong> \u00a0\u00a0query. Save the query as Graduates_Parameter<\/strong>.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will modify the query to prompt the user for input.
\u00a0<\/p>\n

\u00a0\u00a0Set the criteria in the ExpectedGraduation field to display the prompt as Enter the graduation year:<\/strong> and run the query. In the prompt, \u00a0\u00a0enter 2022<\/strong> and click OK<\/strong> to view the results (16 records). Save and close the query.<\/p>\n

\u00a0<\/p>\n

Create \u00a0\u00a0a Navigation form based on the Vertical Tabs, Left template. Drag and drop \u00a0\u00a0the Client Information form<\/strong> onto \u00a0\u00a0the first tab of the form. Drop the Clients \u00a0\u00a0Report by Year<\/strong> onto the second tab. View the form in Form view, click \u00a0\u00a0each of the tabs, and then save the form as Navigator.<\/strong> \u00a0\u00a0Close the form.<\/p>\n

\u00a0<\/p>\n

You will use an SQL statement to \u00a0\u00a0display specific records in a report.
\u00a0<\/p>\n

\u00a0\u00a0Open the Clients Report by Year report<\/strong> \u00a0\u00a0in Design view. Modify the record source of the report using an SQL statement \u00a0\u00a0to select only client records with an ExpectedGraduation<\/strong> \u00a0\u00a0of 2022.<\/strong> (Hint:<\/em> \u00a0\u00a0copy and modify the existing SQL code). Print Preview the report (sixteen \u00a0\u00a0records will display). Save and close the report.<\/p>\n

\u00a0<\/p>\n

You \u00a0\u00a0will add a button to a form that will open a report when clicked.
\u00a0<\/p>\n

\u00a0\u00a0Open the Client Information form<\/strong> \u00a0\u00a0in Design view, and click to add a command button at the intersection of the \u00a0\u00a06-inch mark on the horizontal ruler and the 3-inch mark on the vertical ruler.<\/p>\n

\u00a0<\/p>\n

Set \u00a0\u00a0the command button to open the report named Clients Report by Year<\/strong>. Use the default picture as the button. \u00a0\u00a0Set the name and the caption properties of the button to Open Client Report<\/strong>. Save the form. View the form in Form \u00a0\u00a0view, scroll down if necessary, and click the command button.<\/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":"

\u00a0EX19_AC_COMP_GRADER_CAP_AS – Student Loans 1.0 \u00a0\u00a0 Project Description: In this project, you will add fields to a table and set data validation rules. You will also import a text file into a database, design advanced queries, and create a navigation form. Additionally, you will use SQL to modify a record source and create an embedded […]<\/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-89874","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/89874","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=89874"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/89874\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=89874"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=89874"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=89874"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}