Midwest Executive Professionals IMPROVE A DATABASE BY CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS - GETTING STARTED . Open the file IL_AC19_CS1-4a_FirstLastName Waacob, available for download from the SAM website. Save the file as IL_AC19 CS1-4a_FirstLastName_2.aced yainging the "1" to a 2" If you do not see the acado ansion in the Save As dialog box, do not type it. The program will add the file exterou automatically. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer Support_AC19_CS-1-4a_Industries.xdsx operi the_Gradingin mble and ensure that your first and last name is dient yed as the firecord in ble. If the table does not contain your name, delete The file and download a new copy from the SAM website og GETTING STARTED Open the file IL_AC19_CS1-4a_FirstLastName_1.accdb, available for download from the SAM website. Save the file as IL_AC19_CS1-4a_FirstLastName_2.accdb by changing the "1" to a 2" a If you do not see the accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for automatically. To complete this SAM Project, you will also need to download and save the following data files from the SAM vebsite onto your computer: Support_AC19_CS- ries.xlsx Open the Grading Info Table table and ensure that our first and last name is display as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROIECT STEPS awest Exec Professionals is a firm that connects professional job seekers with job openings. You wote Research Department of Midwest Executive Professionals as a research analystou are developing an Access database application to help manage unnanles, lobs applicants, and job placements facilitated by your ISL Tcord in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Midwest Executive Professionals is a firm that connects professional job seekers with job openings. You work in the Research Department dwest Executive Professionals as a research analyst. You are ce ass database anblication to help manage and analyze companies, jobs, applicants, and job placemeated by your company. In Design View of the Applications table, modify the table with the following instructions: Add a new field after the Application Date field named Followupdate with a Date/Time data type b. Use Application is to rename the ID field. a. 2 Save and close the Application table In Datasheet View of the Jobseekers to complete the following instructions: Delete the record for Richard Mary coSeekerID 14). Edit John to be Jonathan for JobSeeedD 10 . Enter a new record into the table using the information in Table 1. Close the Job Seekers table. A Table 1: New Record for JobSeekers Table JobSeekerID First Name LastName Phone (AutoNumber will automatically number this field) Leonardo Eagan 5551118887 ENGAGE strated Accom 2019 IMG 4: SAM Capstone Proleaf to 3 the Navigation Pane, maka Industries the new name for the Carogones table, Use the import wizard to appendors from the Support ACIO_CS-1 4a_Industries.xlsx to the Do not save the import steps. (Hirteita warning message appears. Cilck Open) Create a new table in Table Design View with the following specifications: 3. 4. In the Navigation Pane, make Industries the new name for the Categories table. Use the import wizard to append the records from the Support_AC19_CS-1- 4a_Industries.xlsx to the Industries table. Do not save the import steps. (Hint: If a warning message appears, Clir 5. Create a new table in Table Design View with the following spacions: a. b. c. d. . Enter Employees as the name of the new table. Add a new field named Employeeld with the Number data type. Cet it as the Primary key field. Add two new fields named EmployeeFirst and Employeelast in the order shown. They should have the Sto Text data type and field size properties of 20. Add a new field named Email with the reportink data type. Set the required roperty to Yes. Add a new field named Salary with the Currency data type. Set the Default value property to 45000 Add a new field named Extension with the Short Text data type. Update the Input Mask property using the input Mask Wizard. Select the Extension input mask and update the field to 9999, so that 9999; is the final input. Accept the default f. 145.16 Add a new field named Extension with the Short Text data type. Update the Input Mask property using the Input Mask Wizard. Select the Extension input mask and update the field to 9999, so that 9999;;_ is the final input. Accept the default in the last window. 6. Save the table and then close it. In Datasheet View of the Job Seekers table, complete the fol g options: Delete the comments field. (Hint: It currently does not have any data.) b. Move the phone field to be that datasheet Sort the records in ascending order by Name. a. C. 7. Save and close the Job Seekers table. Create a new query in Design View using the Companies and tobs tables with the following qutions: Select the Company Name and mustry fields from the Companies table and the Job Title and starting Salary the Jobs table Join the tables using the company field from the Companies table and the Company field from the Jobs table Save the query, using HighSafaris with the High Salanesquery still open in Design View, add criteria to select only those records where the Starting Salary is greater than or equal to 50000, save the que own lure and then close it riyu ery in Uatasheet View Sob Title - Starting $15.000.00 TH Company Naine Industry Mestnancial Grey Insurance Care Madison Web Service Computer Hardware Software Omaha Mutual Reah Financialyteresearch One Mutual Financial Analyst Maison Web Services Computer and Mason we Service Computer Des Moinancial Group Ma Web Services Computer Hardware Des Moines Financial Group is a Maw Computer Hawa wa MW Computer Ou Mutu Antich Director Tale Facility Servo port Director Boro tulee valth Prom Analyst Engine 000.00 0.000.00 $65.000.00 100.00 .. 13.00 91.00 ed Tech VA Business Developer DO Use Simple Query Wizard to create query with the following options: Use the Job Seekers and Action b Select all four fields from the Job Seekers table and the Application Date field from the Applications table. Create a detail query thathuery 9. Use the Simple Query Wizard to create a new query with the following options: a. Use the Job Seekers and Applications tables. b. Select all four fields from the Job Seekers table and the Anglication Date field from the Applications table. Create a detail query: d. Use ApplicationDates as lutte for the query, C. Save and close the query. 10. In Design View of the Application Listing query, modify the query with the following options: criteria to select all records with Administrative Assistant or User Support Director as the Job Title Sort the records in ascending order by LastName, and then by FirstName. Run the query to display it in Datasheet View as shown in Figure 2, and then save Illustrated Access 2019 | Modules 1-4: SAM Capstone Project la di Figure 2: ApplicationListing Query in Datasheet View o. MO WI UL Sport der AAH WISE 1 In Design View of the DirectorSeptember query, add criteria using wildcard characters to select all records with Director anywhere in the abitie field and an Application date greater than or equal to 9/1/2018. Run the query to display it in Datasheet View as shown in Figure, and then save and close-it. Figure 3: DirectorSeptember Query in Datasheet View y lo display in Datasheet View as shown in Figure 3, and then save and close it. All Figure 3: DirectorSeptember Query in Datasheet View Lynch Othe oble uw Spot Coro Guest Utor They 0.00 w cromoler pl dem . 12. Use the Form Wizard to create a new form with the following options: a Select all of the fields from the Jobs table Choose a Coluninar layout room Accessibility investigate 10 211 Diane David Timothy an WM lones Mitcheson go Dickinson 7 JABAR User Support Derecho Director of Employee Program User Supporterer User Support User Support to Director opel PORTS Decoratiewe Director of M wan WI W203 64,000.00 Omheter s.200.00 Madison Web Ser S6.000.00 Omaha Mutual Rearch 36.600.00Mb Rath 561.000 66 Omah Mutual rich 360,000.00 Mason we Services S. Ma Web Services S0.00 MW w MS ener Christine MI boyle 12. Use the Form Wizard to create a form the following options: Select all of the fields from the Jobs table. Choose a Columnar layout. Use JobsEntry as the title: b Save and close to JobsEntry form 13. a illustrated Access 2019 | Modules 1-4: SAM Capstone Project la Use the Report Wizard to create a new out with the following options: Select all of the fields from the Companies table b. Group the records by Industry Sort the records in ascending order by Company Name. d. Use a Stepped layout and Portrait orientation Use Company Listing as the title. C. e. Preview, save, and close the report 14. noen the Jo SeekerEntry form in Form venter Jacob as the FirstName value for the ord to replace Jack, and then add a new record in the main form with the information in Table 2. Toble 2: New Record for JobSeekerEntry Form Job SeekerID (Auto Number field will automatically increment) Camilla First Name 5 all lable 2: New Record for JobSeekerEntry Form JobSeekerID FirstName LastName (AutoNumber field will automatically increment) Camilla Escobar 15. Open the JobseekerEntry form in Layout View and modity it with the following options: . Right-align the text in the JobSeekerlo, FirstName, and LastName tabels. b. Edit the FirstName laberrad First Name and use Last rename the LastName label. Delete the close commandon in he Form Footer section 16. With the Job Seekrentry forni still open in Layout View, modify it with the following Cons: Add the phone field to the form and position it just below the Last Name tabel. change the font color for the phone bol to Automatic (black) Apply a quick style, Colored Outline - Blue, Accent 1 (first ist row second 2nd column of Theme Styles gallery) to the Close command button in the Form Header Section JobSeekerEntry Close JobSeekers Jobseekerid 1 First Name Jacob Last Name Boggs Phone 5557222275 Application obi Application - Followupa. 8/1/2018 (New) Search Record 1 1 1 Record: K 1 of 515 Na Feter Search 17. Open the JobListing report in Layout View and add cost formating so that any Starting Salary value greater than or equal to $70,000 ormatted using Green 3 background color (7th column, 4th row in the standard Colors pallete). Save the JobListing report. 18. With the JobListing report still open in Layout View, modify it with the following options: Change the background color of the Starting Salary label in die Page Header section to the same coloreen 3 (7th column 4th row in the Standard Colors palette) Group the two labels in the haport Header section together in a tabular layout. Save and display the report in Report View as shown in Figure 5. and then close it. a. b CENGAGE Starting Stary Job Listing Sorted by Company and then by Job Title Company Name Industry Job The Madison Web Services Computer Hardware & Software Director of Employee Health Director of Yol Auto integrated Tepes Ard 585,000.00 $91.000.CO 65,000.00 Siner that Dessin SO 0.000 ch A. A A Font 15 21 Paragraph SIGN Voir 11 4 Styles 11 12 13 14 15 a. 19. In Layout View of the Job Seekertisting repo y it with the collowing options: Change the Theres to Green b. Change the Theme Fonts to klin Get Display the report in Report View as shown in Figure 6, and then save and close it Josed Job Seekers kerto First Mario Cole Maids Abbott Ada AR 200 5554320055 5553179204 DOOR 201 80 13 500 00 AC 2007 557221700 Tor TIES 30 27 551001 C 435 CON room Midwest Executive Professionals IMPROVE A DATABASE BY CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS - GETTING STARTED . Open the file IL_AC19_CS1-4a_FirstLastName Waacob, available for download from the SAM website. Save the file as IL_AC19 CS1-4a_FirstLastName_2.aced yainging the "1" to a 2" If you do not see the acado ansion in the Save As dialog box, do not type it. The program will add the file exterou automatically. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer Support_AC19_CS-1-4a_Industries.xdsx operi the_Gradingin mble and ensure that your first and last name is dient yed as the firecord in ble. If the table does not contain your name, delete The file and download a new copy from the SAM website og GETTING STARTED Open the file IL_AC19_CS1-4a_FirstLastName_1.accdb, available for download from the SAM website. Save the file as IL_AC19_CS1-4a_FirstLastName_2.accdb by changing the "1" to a 2" a If you do not see the accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for automatically. To complete this SAM Project, you will also need to download and save the following data files from the SAM vebsite onto your computer: Support_AC19_CS- ries.xlsx Open the Grading Info Table table and ensure that our first and last name is display as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROIECT STEPS awest Exec Professionals is a firm that connects professional job seekers with job openings. You wote Research Department of Midwest Executive Professionals as a research analystou are developing an Access database application to help manage unnanles, lobs applicants, and job placements facilitated by your ISL Tcord in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Midwest Executive Professionals is a firm that connects professional job seekers with job openings. You work in the Research Department dwest Executive Professionals as a research analyst. You are ce ass database anblication to help manage and analyze companies, jobs, applicants, and job placemeated by your company. In Design View of the Applications table, modify the table with the following instructions: Add a new field after the Application Date field named Followupdate with a Date/Time data type b. Use Application is to rename the ID field. a. 2 Save and close the Application table In Datasheet View of the Jobseekers to complete the following instructions: Delete the record for Richard Mary coSeekerID 14). Edit John to be Jonathan for JobSeeedD 10 . Enter a new record into the table using the information in Table 1. Close the Job Seekers table. A Table 1: New Record for JobSeekers Table JobSeekerID First Name LastName Phone (AutoNumber will automatically number this field) Leonardo Eagan 5551118887 ENGAGE strated Accom 2019 IMG 4: SAM Capstone Proleaf to 3 the Navigation Pane, maka Industries the new name for the Carogones table, Use the import wizard to appendors from the Support ACIO_CS-1 4a_Industries.xlsx to the Do not save the import steps. (Hirteita warning message appears. Cilck Open) Create a new table in Table Design View with the following specifications: 3. 4. In the Navigation Pane, make Industries the new name for the Categories table. Use the import wizard to append the records from the Support_AC19_CS-1- 4a_Industries.xlsx to the Industries table. Do not save the import steps. (Hint: If a warning message appears, Clir 5. Create a new table in Table Design View with the following spacions: a. b. c. d. . Enter Employees as the name of the new table. Add a new field named Employeeld with the Number data type. Cet it as the Primary key field. Add two new fields named EmployeeFirst and Employeelast in the order shown. They should have the Sto Text data type and field size properties of 20. Add a new field named Email with the reportink data type. Set the required roperty to Yes. Add a new field named Salary with the Currency data type. Set the Default value property to 45000 Add a new field named Extension with the Short Text data type. Update the Input Mask property using the input Mask Wizard. Select the Extension input mask and update the field to 9999, so that 9999; is the final input. Accept the default f. 145.16 Add a new field named Extension with the Short Text data type. Update the Input Mask property using the Input Mask Wizard. Select the Extension input mask and update the field to 9999, so that 9999;;_ is the final input. Accept the default in the last window. 6. Save the table and then close it. In Datasheet View of the Job Seekers table, complete the fol g options: Delete the comments field. (Hint: It currently does not have any data.) b. Move the phone field to be that datasheet Sort the records in ascending order by Name. a. C. 7. Save and close the Job Seekers table. Create a new query in Design View using the Companies and tobs tables with the following qutions: Select the Company Name and mustry fields from the Companies table and the Job Title and starting Salary the Jobs table Join the tables using the company field from the Companies table and the Company field from the Jobs table Save the query, using HighSafaris with the High Salanesquery still open in Design View, add criteria to select only those records where the Starting Salary is greater than or equal to 50000, save the que own lure and then close it riyu ery in Uatasheet View Sob Title - Starting $15.000.00 TH Company Naine Industry Mestnancial Grey Insurance Care Madison Web Service Computer Hardware Software Omaha Mutual Reah Financialyteresearch One Mutual Financial Analyst Maison Web Services Computer and Mason we Service Computer Des Moinancial Group Ma Web Services Computer Hardware Des Moines Financial Group is a Maw Computer Hawa wa MW Computer Ou Mutu Antich Director Tale Facility Servo port Director Boro tulee valth Prom Analyst Engine 000.00 0.000.00 $65.000.00 100.00 .. 13.00 91.00 ed Tech VA Business Developer DO Use Simple Query Wizard to create query with the following options: Use the Job Seekers and Action b Select all four fields from the Job Seekers table and the Application Date field from the Applications table. Create a detail query thathuery 9. Use the Simple Query Wizard to create a new query with the following options: a. Use the Job Seekers and Applications tables. b. Select all four fields from the Job Seekers table and the Anglication Date field from the Applications table. Create a detail query: d. Use ApplicationDates as lutte for the query, C. Save and close the query. 10. In Design View of the Application Listing query, modify the query with the following options: criteria to select all records with Administrative Assistant or User Support Director as the Job Title Sort the records in ascending order by LastName, and then by FirstName. Run the query to display it in Datasheet View as shown in Figure 2, and then save Illustrated Access 2019 | Modules 1-4: SAM Capstone Project la di Figure 2: ApplicationListing Query in Datasheet View o. MO WI UL Sport der AAH WISE 1 In Design View of the DirectorSeptember query, add criteria using wildcard characters to select all records with Director anywhere in the abitie field and an Application date greater than or equal to 9/1/2018. Run the query to display it in Datasheet View as shown in Figure, and then save and close-it. Figure 3: DirectorSeptember Query in Datasheet View y lo display in Datasheet View as shown in Figure 3, and then save and close it. All Figure 3: DirectorSeptember Query in Datasheet View Lynch Othe oble uw Spot Coro Guest Utor They 0.00 w cromoler pl dem . 12. Use the Form Wizard to create a new form with the following options: a Select all of the fields from the Jobs table Choose a Coluninar layout room Accessibility investigate 10 211 Diane David Timothy an WM lones Mitcheson go Dickinson 7 JABAR User Support Derecho Director of Employee Program User Supporterer User Support User Support to Director opel PORTS Decoratiewe Director of M wan WI W203 64,000.00 Omheter s.200.00 Madison Web Ser S6.000.00 Omaha Mutual Rearch 36.600.00Mb Rath 561.000 66 Omah Mutual rich 360,000.00 Mason we Services S. Ma Web Services S0.00 MW w MS ener Christine MI boyle 12. Use the Form Wizard to create a form the following options: Select all of the fields from the Jobs table. Choose a Columnar layout. Use JobsEntry as the title: b Save and close to JobsEntry form 13. a illustrated Access 2019 | Modules 1-4: SAM Capstone Project la Use the Report Wizard to create a new out with the following options: Select all of the fields from the Companies table b. Group the records by Industry Sort the records in ascending order by Company Name. d. Use a Stepped layout and Portrait orientation Use Company Listing as the title. C. e. Preview, save, and close the report 14. noen the Jo SeekerEntry form in Form venter Jacob as the FirstName value for the ord to replace Jack, and then add a new record in the main form with the information in Table 2. Toble 2: New Record for JobSeekerEntry Form Job SeekerID (Auto Number field will automatically increment) Camilla First Name 5 all lable 2: New Record for JobSeekerEntry Form JobSeekerID FirstName LastName (AutoNumber field will automatically increment) Camilla Escobar 15. Open the JobseekerEntry form in Layout View and modity it with the following options: . Right-align the text in the JobSeekerlo, FirstName, and LastName tabels. b. Edit the FirstName laberrad First Name and use Last rename the LastName label. Delete the close commandon in he Form Footer section 16. With the Job Seekrentry forni still open in Layout View, modify it with the following Cons: Add the phone field to the form and position it just below the Last Name tabel. change the font color for the phone bol to Automatic (black) Apply a quick style, Colored Outline - Blue, Accent 1 (first ist row second 2nd column of Theme Styles gallery) to the Close command button in the Form Header Section JobSeekerEntry Close JobSeekers Jobseekerid 1 First Name Jacob Last Name Boggs Phone 5557222275 Application obi Application - Followupa. 8/1/2018 (New) Search Record 1 1 1 Record: K 1 of 515 Na Feter Search 17. Open the JobListing report in Layout View and add cost formating so that any Starting Salary value greater than or equal to $70,000 ormatted using Green 3 background color (7th column, 4th row in the standard Colors pallete). Save the JobListing report. 18. With the JobListing report still open in Layout View, modify it with the following options: Change the background color of the Starting Salary label in die Page Header section to the same coloreen 3 (7th column 4th row in the Standard Colors palette) Group the two labels in the haport Header section together in a tabular layout. Save and display the report in Report View as shown in Figure 5. and then close it. a. b CENGAGE Starting Stary Job Listing Sorted by Company and then by Job Title Company Name Industry Job The Madison Web Services Computer Hardware & Software Director of Employee Health Director of Yol Auto integrated Tepes Ard 585,000.00 $91.000.CO 65,000.00 Siner that Dessin SO 0.000 ch A. A A Font 15 21 Paragraph SIGN Voir 11 4 Styles 11 12 13 14 15 a. 19. In Layout View of the Job Seekertisting repo y it with the collowing options: Change the Theres to Green b. Change the Theme Fonts to klin Get Display the report in Report View as shown in Figure 6, and then save and close it Josed Job Seekers kerto First Mario Cole Maids Abbott Ada AR 200 5554320055 5553179204 DOOR 201 80 13 500 00 AC 2007 557221700 Tor TIES 30 27 551001 C 435 CON room