Part 4 -ACCESS Project InstructionsPoints50.Start Access. Create a blank new access database file on your PC (C:\drive) named18PR_access_LastFirst.accdb, where LastFirst is your actual Lastname and Firstname(For example, if your name is Mary Smith, you will name the file18PR_access_SmithMary)151.Closethe default Table1that gets createdin the above new database file to delete it.(Hint: You can click here to close the table. This will delete the table.You can also right-click on the Table1table name and choose Delete).152.Createa new table using the Table Designbutton. (Hint: On the Createtab, In the Tablesgroup, select Table Design. This will open anew table Table1in Design View)153.Enterthe followingsixField Namesand corresponding Data Typesto the Table1 created above.1 Page 15of 20StepsPart 4 -ACCESS Project InstructionsPoints54.Using the design information below, click on each Field Name, and adjust the Field Propertiesof each of the following four Field Nameshighlighted below.Field NameField PropertiesSpokespersonField Size: 3Required: YesSet this field as the PRIMARY KEYFirstnameField Size: 15LastnameField Size: 25CareerAvailableGenderField Size: 1Hint: a.When you click on each of the field names in the upper half of the screen, their respective Field Propertiesappear in the bottom half of the screen(see image below).b.The Primary Keyattribute is set in the Ribbon area, or by right-clicking on the Field Name and selecting Primary Key(see image below).255.Savethe table with the name: Spokespeople156.Switch toDatasheet Viewof the Spokespeopletableandenterthe following data/records: (Hint: To switch to DatasheetView, clickthe Viewbutton in the Viewsgroup of the Designtab)2ab Page 16of 20StepsPart 4 -ACCESS Project InstructionsPointsSaveandClosethe Spokespeopletable.57.Follow step 53. to create another new Table in design view and enterthe following Field Namesand Data Typesand set the corresponding Field Properties:Field NameData TypeField PropertiesProductIDNumberField Size: Long IntegerRequired: YesSet this field as the PRIMARY KEYProductNameShort TextField Size: 30CategoryShort TextField Size: 2UnitPriceCurrencyCurrency with 2decimal placesUnitCostCurrencyCurrency with 2decimal placesSpokespersonShort TextField Size: 3258.Savethe table with the name: Products159.a.Switchto the Datasheet Viewof the Productstable and enter the followingdata/records:b.SaveandClosethe Productstable.160.Confirm that the Productsand Spokespeopletables are closed. If not,saveandclosethem. 061.a.On the Database Toolstab, in the Relationshipsgroup, click Relationships.b.Createa one-to-many relationshipbetween the Spokespersonfield in the Spokespeopletable and the Spokespersonfield in the Productstable. c.Enforce referential integritybetween the two tables. Closethe Relationships window, savingthe changes.(Refer to the image below for the Relationshipscreated)1 Page 17of 20StepsPart 4 -ACCESS Project InstructionsPoints62.Createa Simple QueryUsing the Query Wizard:a.On the Createtab, In the Queriesgroup, select Query Wizard.b.Createa simple query c.The query should be basedon the Spokespeopletable d.For example, once the Simple Query Wizard dialog box opens, MAKE sure and change the table under Tables/Queries to: Table: Spokespeoplee.Includethe following fields in this order:Firstname, Lastname, Career, Genderf.Savethe title of the query as Music AND Female Queryand click Finish.g.Switch to Design Viewandset the Criteriato select onlythose records with whose Careeris in MusicANDwhose Genderis a F(Female). h.The Genderfield should not appearin the query results (i.e., make sure the Show check box is not checked for the Gender field). Run, Saveand Closethe query.(Refer to the image below for Music AND Female QueryDesign Viewresults)(Refer to the image below for Music AND Female QueryDatasheet Viewresults)263.Using the Query Wizard, createanother simplequery:a.The query should be basedon the Spokespeopletable.b.Includethe following fields in this order:Firstname, Lastname, Careerc.Savethe query as Acting OR Music Query. d.In Design view, set the Criteriato select those records with whose Careeris in ActingORMusic:2 Page 18of 20StepsPart 4 -ACCESS Project InstructionsPointsi)Type Actingon Criterialine under Career, and then ii)Type Music right below it on theor:line.Run, Saveand Closethe query.(Refer to the image below for Acting OR Music QueryDatasheet View results)64.Createanother simple query based onboth theSpokespeopletable and Productstable. a.Start by basing thequeryonthe Spokespeopletable.i)Includethe Careerfield from the Spokespeopletable.b.In thesame window, change the table name under the Tables/Queries fieldto the Productstable.i)IncludeUnitPriceand UnitCostfields from the Productstable. ii)Note: Three fields should now be displayedin the right-most box, under Selected Fields: Career, UnitPrice and UnitCostc.Choose Detailquery, and then save the title of thequery as Totals Query. d.Switch to Design view of the queryand click the Totalsbutton, located on the Designtab, in the Show/Hidegroup. i)Make sure that the Careerfield is set to Group Byin the Totalrow. ii)Set the UnitPricefield to Sumin the Total row, iii)and set the UnitCostfield to Avgin the Total row.e.Runthe query. f.Resizethe fields to show all of the contents (headings/data). Saveand Closethe query.(Refer to the imagebelow for Totals Queryresult)265.Using the Report Wizard, createa new report based on the Spokespeopletable. a.Includeall fields from Spokespeopletable. b.Groupby Career. c.Sortthe records by Firstnamein ascendingorder. d.Use Steppedlayout, 1 Page 19of 20StepsPart 4 -ACCESS Project InstructionsPointse.Landscapeorientation, f.and check to box to adjust field width so that they fit on one page. g.Savethe title of the report as SpokespeopleReportand then click Finish.66.Close the Print Preview mode and then switch to Layout View, if so desired (its easier than Design View). a.Changethetitleof the report to Your NameSpokespeople Report (Where Your Nameis your actual Firstname Lastname, ex: Mary Smith Spokespeople Report). b.Also, changethe report titles fontto Tahoma, c.font colorto Purpleand d.font sizeto 28.Saveand Closethe report. (Refer to the image below for Spokespeople Reportcreated)167.Using the Form Wizard, createa new form based on the Productstable. Include all fieldsfrom Productstable. Use Columnarlayout. Savethe title of the form as ProductsFormand then click Finish.168.With the ProductsFormopened add a new record with the following data(make sure to enter your actual Fullnamefor ProductName):Saveand Closethe ProductsForm.1 Page 20of 20StepsPart 4 -ACCESS Project InstructionsPoints69.Openthe Productstable and Sortit by Categoryfield in ascending(A to Z) order. Savethe change.(Refer to the image below for Productstablesorted)170.You are almost finished!! You have two more steps:a.In Backstage View, Compact and Repair Database, and then b.Closethe database and exit out of Access.071.You may now proceed to your Canvas course to upload/submit the following project solution files saved on your PC:o18PR_ppt_LastFirst.pptxo18PR_word_LastFirst.docxo18PR_excel_LastFirst.xlsxo18PR_access_LastFirst.accdb0TOTAL POINTS25