Can it be done
All the files have been submitted
Assignment 3: Access Credit Weight: 25% of your final grade Due Date: after you complete the readings and learning activities for Lessons 8, 9, and 10. Please review the Submitting Assignments page before you begin this assignment. You must download data files to complete each textbook exercise. Refer to the Assignment 3 Dropbox page to locate these files. Instructions: General Each exercise will require you to create or modify one or more files. The assignment instructions state exactly how the file names for the results should be constructed. For example, for Assignment 3, the instructions require the input file Acc1Loans.accdb. Save this file as Acc1Loans_LastFirst.accdb Replace LastFirst with your name, e.g., Acc1Loans_ SmithJane.accdb Each exercise will specify which created or modified files should be submitted for marking. Access Exercise 1: Small Business Loans You are employed as a technical supervisor at a lending firm for small business loans. You will learn how to populate an Access table, work with a form that can be used to add, delete, and modify member loan records, apply a filter to a table, and locate loan officer information through a query. Modify Data in a Table You will open an original database file and save the database with a new name. You will then modify, add, and delete information using tables and forms. Open the Acc1Loans.accdb file and save the database as Acc1Loans_LastFirst.accdb. Open the Loan Officers table in Datasheet view. Update the database with the information below. First Name Last Name Email Address Phone Ext Title John Badman
[email protected] x1757 Loan Officer Stan Dupp
[email protected] x6720 Senior Loan Officer Herb Avore
[email protected] x2487 Loan Officer Polly Esther
[email protected] x8116 Senior Loan Officer Strawberry Fields
[email protected] x3219 Loan Officer Ann Serdifone
[email protected] x5962 Managing Loan Officer Close the Loan Officers table. Open the Loans table in Datasheet view. Add a new record with the following information: OfficerID: 5 MemberID: 15 LoanAmount: 7,000 Term: 36 months InterestRate: 15.41% Payment: $244.07 Grade: D IssueDate: 12/15/21 LoanStatus: Late (31–120 days) Open the Maintain Members form. In record 3 (for Brynn Anderson, MemberID 13), add a new loan to the subform: OfficerID: 5 LoanAmount: 17,000 Term: 36 months InterestRate: 4.35% Payment: $300.45 Grade: B IssueDate: 9/1/21 LoanStatus: Fully Paid Use the Navigation bar to search for MemberID 16 and edit the subform so that the InterestRate is 0.1254 instead of 0.1899 for the LoanID 47. Close the Maintain Members form. Sort a Table and Use Filter By Form You will sort the Loan table and apply a filter to display only loans with an interest rate less than 11% and a term of 36 months. Sort the records in the Loans table by the IssueDate field in descending order (newest to oldest). Use the Filter By Form feature to apply filters to display only loans that have a rate of less than 11% (<0.11) and a term of 36 months. close the table and save the changes. apply a selection filter and sort a query you are interested in quickly filtering the data in the loans, officers, and members query based on a specific loan officer. you will then sort the filtered results to view the loans by loan status. open the loans, officers, and members query in datasheet view. use a selection filter to show only the loans managed by the loan officer whose name is john badman. sort the query by loanstatus in alphabetical order. save and close the query. back up a database and add an application part you will first back up the database to reinforce the importance of backing up stored data. you will also add an application part to the database. create a backup copy of your database, accepting the default file name. add a comments application part, selecting the option one ‘loans’ to many ‘comments’. select the loanstatus field as the field from the loans table and sort this field ascending. name the lookup column status and then click on the create command button. open the comments table. in row 1, enter current in the status column, select 12/23/2021 for the comment date, and enter the comment loan 47 rate changed from 18.99% to 12.54%. click on the click to add column heading and select the short text option. change the field1 column heading to explanation. enter the text entry error in the explanation column in row 1. close the comments table. right-click on the comments table in the navigational pane and rename it interest rate changes. close the database and exit access. submit the acc1loans_lastfirst.accdb file and its backup file for marking. access exercise 2: fantastic foods fantastic foods is an importer of exotic foods from all over the world. you landed a summer internship with the company and discovered that their product lists and the suppliers they buy from are stored in excel workbooks. you offer to help by using your newly gained knowledge of access to create a relational database for them. you will begin by importing the workbooks from excel into a new access database. your manager mentions that she also wants a table that specifies food categories so that it is possible to relate products sold to specific categories in the database. you will create a table from scratch to track categories, create relationships between the tables, and create some baseline queries. create a new database you will examine the data in the excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys so that you can join them in the relationships window. 1. open the acc2suppliers.xlsx excel workbook, examine the data, and then close the workbook. open the acc2products.xlsx excel workbook, examine the data, and then close the workbook. create a new, blank database named acc2foods_lastfirst.accdb. close the new blank table created automatically by access without saving it. import excel data to the suppliers table you will import the excel workbook that contains supplier information into a suppliers table. click the external data tab. click new data source, point to from file in the import & link group and then select excel. navigate to and select the acc2suppliers.xlsx workbook to be imported as a new table in the current database. place a check mark in the first row contains column headings option. set the supplierid field indexed option to yes (no duplicates). select supplierid as the primary key when prompted and accept the table name suppliers. do not save the import steps. open the suppliers table in datasheet view to examine the data. close the table. import excel data to the products table you will import the excel workbook that contains the product information into the products table. import the acc2products.xlsx workbook, set the productid indexed option to yes (no duplicates), and then select productid as the primary key. accept the table name products. in design view, change the field size of the quantityperunit field to 25. set the field size of the productid and categoryid fields to long integer. save the changes. open the table in datasheet view to examine its contents. close the table. create a categories table you will create a new table that will enable fantastic foods to associate each product with a food category in the database. create a new table in design view and save the table as categories. add the following fields in design view and set the properties as specified: · add the primary key field as categoryid with the number data type and number assigned to a new category. (type the period) as the description. set the caption property to category id. · add categoryname with a short text data type and name of food category. (include the period) as the description. change its field size to 15. set the caption property to category name and the required property to yes. · add categorydescription with the long text data type. set the caption property to category description. switch to datasheet view and save the table when prompted. close the table. add data to the categories table you will add eight records to the categories table to give you sample data to test in the database. add the following records to the categories table, and then save and close the table. category id category name category description 1 beverages soft drinks, coffees, teas 2 condiments sauces, relishes, seasonings 3 confections desserts, candies, sweet breads 4 dairy products cheeses 5 grains/cereals breads, pasta, cereal 6 meat/poultry prepared meats 7 produce dried fruit, bean curd 8 seafood seaweed and fish create relationships you will create the relationships between the suppliers, products, and categories tables using the relationships window. add all three tables to the relationships window. join the primary key field in the categories table with its foreign key counterpart in the products table. select the enforce referential integrity and cascade update related fields check boxes. join the primary key field in the suppliers table with its foreign key counterpart in the products table. select the enforce referential integrity and cascade update related fields check boxes. save and close the relationships window. use the query wizard you will use the simple query wizard to create a query of all products that you import in the seafood category. add the productname, supplierid, and categoryid fields from the products table in that order. save the query as seafood products. in design view, add a criterion to include only products with 8 as the categoryid. sort the query results in ascending order by productname. run, save, and close the query. copy and modify a query in design view you want to create a query that displays category names rather than their categoryids. this will show which seafoods, meat, and poultry products are currently imported. you will copy the seafood products query and modify it by deleting a field, then adding an additional table and field. copy the seafood products query and paste it using seafood or meat/poultry as the query name. open the seafood or meat/poultry query in design view and delete the categoryid column. add the categories table to the top pane of the query design window. add the categoryname field to the last column of the design grid and set its criterion to “seafood” or “meat/poultry”. run, save, and close the query. create a multitable query you will create a query that identifies suppliers and their associated products. because there is a relationship between the two tables, you can now pull data from each of them together as usable information. create a query in design view that includes the suppliers and products tables. the query should list the company name, contact name, phone number (in that order), then the product name and the product cost (in that order). sort the query by company name in ascending order, then by product cost in descending order. run, close, and save the query as company by product list. use a total row to summarize data in a query you determine that the data in the company by product list query could be summarized with a total row. you will group the records by company name and count the number of products you buy from each of them. copy the company by product list query and paste it using summary of company by product as the query name. open the summary of company by product query in design view and delete the contactname, phone, and productcost columns. click totals in the show/hide group on the query tools design tab. click in the total row of the productname field, click the arrow, and then select count. the records will be grouped by the company’s name, and the products for each company will be summarized. modify the field name of the second column to product count: productname to make the field name more identifiable. click run in the results group. twenty records should display in the datasheet results. these display the product count for each company that supplies your organization. save and close the query. finalize the database adjust any column widths in the created queries so that all data is displayed in the datasheet results. close the database and exit access. submit acc2foods_lastfirst.accdb for marking. access exercise 3: superior games superior games, a boardgame manufacturer, has hired a new ceo. she has asked for your assistance in providing summaries of data that took place before she started with the company. to help her with her strategic planning, you will create queries to perform data analysis on the sales data. based on your meeting, you plan on creating four queries. one query will find orders with minor delays. another query will summarize company revenue and cost by country. a third query will be used to help evaluate payments made by customers on their orders. the final query will calculate the total sales by sales representative title. database file setup you will open the superior games database and use save as to make a copy of the database. you will add yourself as a sales representative to the employee table. 1. open acc3games.accdb and save the database as acc3games_lastfirst.accdb. open the sales reps table. add yourself as a sales rep. fill in all information, including the repid field. set your title to managing partner, salary to $105,144, and the country field to canada. close the sales reps table. shipping efficiency query you will and="" a="" term="" of="" 36="" months.="" close="" the="" table="" and="" save="" the="" changes.="" apply="" a="" selection="" filter="" and="" sort="" a="" query="" you="" are="" interested="" in="" quickly="" filtering="" the="" data="" in="" the="" loans,="" officers,="" and="" members="" query="" based="" on="" a="" specific="" loan="" officer.="" you="" will="" then="" sort="" the="" filtered="" results="" to="" view="" the="" loans="" by="" loan="" status.="" open="" the="" loans,="" officers,="" and="" members="" query="" in="" datasheet="" view.="" use="" a="" selection="" filter="" to="" show="" only="" the="" loans="" managed="" by="" the="" loan="" officer="" whose="" name="" is="" john="" badman.="" sort="" the="" query="" by="" loanstatus="" in="" alphabetical="" order.="" save="" and="" close="" the="" query.="" back="" up="" a="" database="" and="" add="" an="" application="" part="" you="" will="" first="" back="" up="" the="" database="" to="" reinforce="" the="" importance="" of="" backing="" up="" stored="" data.="" you="" will="" also="" add="" an="" application="" part="" to="" the="" database.="" create="" a="" backup="" copy="" of="" your="" database,="" accepting="" the="" default="" file="" name.="" add="" a="" comments="" application="" part,="" selecting="" the="" option="" one="" ‘loans’="" to="" many="" ‘comments’.="" select="" the="" loanstatus="" field="" as="" the="" field="" from="" the="" loans="" table="" and="" sort="" this="" field="" ascending.="" name="" the="" lookup="" column="" status="" and="" then="" click="" on="" the="" create="" command="" button.="" open="" the="" comments="" table.="" in="" row="" 1,="" enter="" current="" in="" the="" status="" column,="" select="" 12/23/2021="" for="" the="" comment="" date,="" and="" enter="" the="" comment="" loan="" 47="" rate="" changed="" from="" 18.99%="" to="" 12.54%.="" click="" on="" the="" click="" to="" add="" column="" heading="" and="" select="" the="" short="" text="" option.="" change="" the="" field1="" column="" heading="" to="" explanation.="" enter="" the="" text="" entry="" error="" in="" the="" explanation="" column="" in="" row="" 1.="" close="" the="" comments="" table.="" right-click="" on="" the="" comments="" table="" in="" the="" navigational="" pane="" and="" rename="" it="" interest="" rate="" changes.="" close="" the="" database="" and="" exit="" access.="" submit="" the="" acc1loans_lastfirst.accdb="" file="" and="" its="" backup="" file="" for="" marking.="" access="" exercise="" 2:="" fantastic="" foods="" fantastic="" foods="" is="" an="" importer="" of="" exotic="" foods="" from="" all="" over="" the="" world.="" you="" landed="" a="" summer="" internship="" with="" the="" company="" and="" discovered="" that="" their="" product="" lists="" and="" the="" suppliers="" they="" buy="" from="" are="" stored="" in="" excel="" workbooks.="" you="" offer="" to="" help="" by="" using="" your="" newly="" gained="" knowledge="" of="" access="" to="" create="" a="" relational="" database="" for="" them.="" you="" will="" begin="" by="" importing="" the="" workbooks="" from="" excel="" into="" a="" new="" access="" database.="" your="" manager="" mentions="" that="" she="" also="" wants="" a="" table="" that="" specifies="" food="" categories="" so="" that="" it="" is="" possible="" to="" relate="" products="" sold="" to="" specific="" categories="" in="" the="" database.="" you="" will="" create="" a="" table="" from="" scratch="" to="" track="" categories,="" create="" relationships="" between="" the="" tables,="" and="" create="" some="" baseline="" queries.="" create="" a="" new="" database="" you="" will="" examine="" the="" data="" in="" the="" excel="" worksheets="" to="" determine="" which="" fields="" will="" become="" the="" primary="" keys="" in="" each="" table="" and="" which="" fields="" will="" become="" the="" foreign="" keys="" so="" that="" you="" can="" join="" them="" in="" the="" relationships="" window.="" 1.="" open="" the="" acc2suppliers.xlsx="" excel="" workbook,="" examine="" the="" data,="" and="" then="" close="" the="" workbook.="" open="" the="" acc2products.xlsx="" excel="" workbook,="" examine="" the="" data,="" and="" then="" close="" the="" workbook.="" create="" a="" new,="" blank="" database="" named="" acc2foods_lastfirst.accdb.="" close="" the="" new="" blank="" table="" created="" automatically="" by="" access="" without="" saving="" it.="" import="" excel="" data="" to="" the="" suppliers="" table="" you="" will="" import="" the="" excel="" workbook="" that="" contains="" supplier="" information="" into="" a="" suppliers="" table.="" click="" the="" external="" data="" tab.="" click="" new="" data="" source,="" point="" to="" from="" file="" in="" the="" import="" &="" link="" group="" and="" then="" select="" excel.="" navigate="" to="" and="" select="" the="" acc2suppliers.xlsx="" workbook="" to="" be="" imported="" as="" a="" new="" table="" in="" the="" current="" database.="" place="" a="" check="" mark="" in="" the="" first="" row="" contains="" column="" headings="" option.="" set="" the="" supplierid="" field="" indexed="" option="" to="" yes="" (no="" duplicates).="" select="" supplierid="" as="" the="" primary="" key="" when="" prompted="" and="" accept="" the="" table="" name="" suppliers.="" do="" not="" save="" the="" import="" steps.="" open="" the="" suppliers="" table="" in="" datasheet="" view="" to="" examine="" the="" data.="" close="" the="" table.="" import="" excel="" data="" to="" the="" products="" table="" you="" will="" import="" the="" excel="" workbook="" that="" contains="" the="" product="" information="" into="" the="" products="" table.="" import="" the="" acc2products.xlsx="" workbook,="" set="" the="" productid="" indexed="" option="" to="" yes="" (no="" duplicates),="" and="" then="" select="" productid="" as="" the="" primary="" key.="" accept="" the="" table="" name="" products.="" in="" design="" view,="" change="" the="" field="" size="" of="" the="" quantityperunit="" field="" to="" 25.="" set="" the="" field="" size="" of="" the="" productid="" and="" categoryid="" fields="" to="" long="" integer.="" save="" the="" changes.="" open="" the="" table="" in="" datasheet="" view="" to="" examine="" its="" contents.="" close="" the="" table.="" create="" a="" categories="" table="" you="" will="" create="" a="" new="" table="" that="" will="" enable="" fantastic="" foods="" to="" associate="" each="" product="" with="" a="" food="" category="" in="" the="" database.="" create="" a="" new="" table="" in="" design="" view="" and="" save="" the="" table="" as="" categories.="" add="" the="" following="" fields="" in="" design="" view="" and="" set="" the="" properties="" as="" specified:="" ·="" add="" the="" primary="" key="" field="" as="" categoryid="" with="" the="" number="" data="" type="" and="" number="" assigned="" to="" a="" new="" category.="" (type="" the="" period)="" as="" the="" description.="" set="" the="" caption="" property="" to="" category="" id.="" ·="" add="" categoryname="" with="" a="" short="" text="" data="" type="" and="" name="" of="" food="" category.="" (include="" the="" period)="" as="" the="" description.="" change="" its="" field="" size="" to="" 15.="" set="" the="" caption="" property="" to="" category="" name="" and="" the="" required="" property="" to="" yes.="" ·="" add="" categorydescription="" with="" the="" long="" text="" data="" type.="" set="" the="" caption="" property="" to="" category="" description.="" switch="" to="" datasheet="" view="" and="" save="" the="" table="" when="" prompted.="" close="" the="" table.="" add="" data="" to="" the="" categories="" table="" you="" will="" add="" eight="" records="" to="" the="" categories="" table="" to="" give="" you="" sample="" data="" to="" test="" in="" the="" database.="" add="" the="" following="" records="" to="" the="" categories="" table,="" and="" then="" save="" and="" close="" the="" table.="" category="" id="" category="" name="" category="" description="" 1="" beverages="" soft="" drinks,="" coffees,="" teas="" 2="" condiments="" sauces,="" relishes,="" seasonings="" 3="" confections="" desserts,="" candies,="" sweet="" breads="" 4="" dairy="" products="" cheeses="" 5="" grains/cereals="" breads,="" pasta,="" cereal="" 6="" meat/poultry="" prepared="" meats="" 7="" produce="" dried="" fruit,="" bean="" curd="" 8="" seafood="" seaweed="" and="" fish="" create="" relationships="" you="" will="" create="" the="" relationships="" between="" the="" suppliers,="" products,="" and="" categories="" tables="" using="" the="" relationships="" window.="" add="" all="" three="" tables="" to="" the="" relationships="" window.="" join="" the="" primary="" key="" field="" in="" the="" categories="" table="" with="" its="" foreign="" key="" counterpart="" in="" the="" products="" table.="" select="" the="" enforce="" referential="" integrity="" and="" cascade="" update="" related="" fields="" check="" boxes.="" join="" the="" primary="" key="" field="" in="" the="" suppliers="" table="" with="" its="" foreign="" key="" counterpart="" in="" the="" products="" table.="" select="" the="" enforce="" referential="" integrity="" and="" cascade="" update="" related="" fields="" check="" boxes.="" save="" and="" close="" the="" relationships="" window.="" use="" the="" query="" wizard="" you="" will="" use="" the="" simple="" query="" wizard="" to="" create="" a="" query="" of="" all="" products="" that="" you="" import="" in="" the="" seafood="" category.="" add="" the="" productname,="" supplierid,="" and="" categoryid="" fields="" from="" the="" products="" table="" in="" that="" order.="" save="" the="" query="" as="" seafood="" products.="" in="" design="" view,="" add="" a="" criterion="" to="" include="" only="" products="" with="" 8="" as="" the="" categoryid.="" sort="" the="" query="" results="" in="" ascending="" order="" by="" productname.="" run,="" save,="" and="" close="" the="" query.="" copy="" and="" modify="" a="" query="" in="" design="" view="" you="" want="" to="" create="" a="" query="" that="" displays="" category="" names="" rather="" than="" their="" categoryids.="" this="" will="" show="" which="" seafoods,="" meat,="" and="" poultry="" products="" are="" currently="" imported.="" you="" will="" copy="" the="" seafood="" products="" query="" and="" modify="" it="" by="" deleting="" a="" field,="" then="" adding="" an="" additional="" table="" and="" field.="" copy="" the="" seafood="" products="" query="" and="" paste="" it="" using="" seafood="" or="" meat/poultry="" as="" the="" query="" name.="" open="" the="" seafood="" or="" meat/poultry="" query="" in="" design="" view="" and="" delete="" the="" categoryid="" column.="" add="" the="" categories="" table="" to="" the="" top="" pane="" of="" the="" query="" design="" window.="" add="" the="" categoryname="" field="" to="" the="" last="" column="" of="" the="" design="" grid="" and="" set="" its="" criterion="" to="" “seafood”="" or="" “meat/poultry”.="" run,="" save,="" and="" close="" the="" query.="" create="" a="" multitable="" query="" you="" will="" create="" a="" query="" that="" identifies="" suppliers="" and="" their="" associated="" products.="" because="" there="" is="" a="" relationship="" between="" the="" two="" tables,="" you="" can="" now="" pull="" data="" from="" each="" of="" them="" together="" as="" usable="" information.="" create="" a="" query="" in="" design="" view="" that="" includes="" the="" suppliers="" and="" products="" tables.="" the="" query="" should="" list="" the="" company="" name,="" contact="" name,="" phone="" number="" (in="" that="" order),="" then="" the="" product="" name="" and="" the="" product="" cost="" (in="" that="" order).="" sort="" the="" query="" by="" company="" name="" in="" ascending="" order,="" then="" by="" product="" cost="" in="" descending="" order.="" run,="" close,="" and="" save="" the="" query="" as="" company="" by="" product="" list.="" use="" a="" total="" row="" to="" summarize="" data="" in="" a="" query="" you="" determine="" that="" the="" data="" in="" the="" company="" by="" product="" list="" query="" could="" be="" summarized="" with="" a="" total="" row.="" you="" will="" group="" the="" records="" by="" company="" name="" and="" count="" the="" number="" of="" products="" you="" buy="" from="" each="" of="" them.="" copy="" the="" company="" by="" product="" list="" query="" and="" paste="" it="" using="" summary="" of="" company="" by="" product="" as="" the="" query="" name.="" open="" the="" summary="" of="" company="" by="" product="" query="" in="" design="" view="" and="" delete="" the="" contactname,="" phone,="" and="" productcost="" columns.="" click="" totals="" in="" the="" show/hide="" group="" on="" the="" query="" tools="" design="" tab.="" click="" in="" the="" total="" row="" of="" the="" productname="" field,="" click="" the="" arrow,="" and="" then="" select="" count.="" the="" records="" will="" be="" grouped="" by="" the="" company’s="" name,="" and="" the="" products="" for="" each="" company="" will="" be="" summarized.="" modify="" the="" field="" name="" of="" the="" second="" column="" to="" product="" count:="" productname="" to="" make="" the="" field="" name="" more="" identifiable.="" click="" run="" in="" the="" results="" group.="" twenty="" records="" should="" display="" in="" the="" datasheet="" results.="" these="" display="" the="" product="" count="" for="" each="" company="" that="" supplies="" your="" organization.="" save="" and="" close="" the="" query.="" finalize="" the="" database="" adjust="" any="" column="" widths="" in="" the="" created="" queries="" so="" that="" all="" data="" is="" displayed="" in="" the="" datasheet="" results.="" close="" the="" database="" and="" exit="" access.="" submit="" acc2foods_lastfirst.accdb="" for="" marking.="" access="" exercise="" 3:="" superior="" games="" superior="" games,="" a="" boardgame="" manufacturer,="" has="" hired="" a="" new="" ceo.="" she="" has="" asked="" for="" your="" assistance="" in="" providing="" summaries="" of="" data="" that="" took="" place="" before="" she="" started="" with="" the="" company.="" to="" help="" her="" with="" her="" strategic="" planning,="" you="" will="" create="" queries="" to="" perform="" data="" analysis="" on="" the="" sales="" data.="" based="" on="" your="" meeting,="" you="" plan="" on="" creating="" four="" queries.="" one="" query="" will="" find="" orders="" with="" minor="" delays.="" another="" query="" will="" summarize="" company="" revenue="" and="" cost="" by="" country.="" a="" third="" query="" will="" be="" used="" to="" help="" evaluate="" payments="" made="" by="" customers="" on="" their="" orders.="" the="" final="" query="" will="" calculate="" the="" total="" sales="" by="" sales="" representative="" title.="" database="" file="" setup="" you="" will="" open="" the="" superior="" games="" database="" and="" use="" save="" as="" to="" make="" a="" copy="" of="" the="" database.="" you="" will="" add="" yourself="" as="" a="" sales="" representative="" to="" the="" employee="" table.="" 1.="" open="" acc3games.accdb="" and="" save="" the="" database="" as="" acc3games_lastfirst.accdb.="" open="" the="" sales="" reps="" table.="" add="" yourself="" as="" a="" sales="" rep.="" fill="" in="" all="" information,="" including="" the="" repid="" field.="" set="" your="" title="" to="" managing="" partner,="" salary="" to="" $105,144,="" and="" the="" country="" field="" to="" canada.="" close="" the="" sales="" reps="" table.="" shipping="" efficiency="" query="" you="">