New Perspectives Access 2019 | Modules 5-8: SAM Critical Thinking Capstone Project 1c Midwest Executive Professionals IMPROVING TABLES, QUERIES, FORMS, AND REPORTS GETTING STARTED Open the file NP_AC19_CT_CS5-8c_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought4

Share With

New Perspectives Access 2019 | Modules 5-8: SAM Critical Thinking Capstone Project 1c

Midwest Executive Professionals

IMPROVING TABLES, QUERIES, FORMS, AND REPORTS

• GETTING STARTED

• Open the file NP_AC19_CT_CS5-8c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_CT_CS5-8c_FirstLastName_2.accdb by changing the “1” to a “2”.
  • 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 you 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_CT_CS5-8c_StateProvs.csv
• Open the _GradingInfoTable table and ensure that your first and last name is displayed 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.

• PROJECT STEPS

1. Midwest Executive Professionals is a firm that connects professional job seekers with job openings. You work in the Research Department of Midwest Executive Professionals as a research analyst and are developing an Access database application to help manage and analyze companies, jobs, applicants, and job placements facilitated by your company. In this project, you will improve an existing database by modifying tables, queries, forms, and reports.
   
   Open the JobSeekers table and complete the following tasks:
   1. After the Phone field, add a new field named Email with the Hyperlink data type.
   2. Add a second new field named Comments with the Long Text data type.
      
      Save and close the JobSeekers table.
2. Open the Jobs table and complete the following tasks:
   1. Set Salary as the caption for the StartingSalary field.
   2. Set >=Date() as the validation rule for the PostingDate field.
   3. Set Date must be on or after today as the validation text for the PostingDate field.
      
      Save and close the Jobs table. If prompted to test data integrity, click Yes.
3. Open the Companies table and complete the following tasks:
   1. Use the Lookup Wizard to look up values for the Industry field.
   2. The lookup field should get its values from the Industries table.
   3. Sort the records in ascending order by the Industry field.
   4. Widen the Industry field so that all of the values in the list are visible.
   5. Enable data integrity on the relationship, and use Industry as the label for the lookup field.
   6. Save the table, if prompted.
   7. Choose "Investment Banking & Asset Management" as the Industry field value for CompanyID 2 (Des Moines Financial Group).
   8. Close the Companies table.
4. Open the ApplicationListing query and complete the following tasks:
   1. Add >=[Enter minimum salary] as parameter criteria to the StartingSalary field.
   2. Run the query, using 60000 when prompted. 
      
      Save and close the ApplicationListing query.
5. Open the IndustryAnalysis query and complete the following tasks:
   1. Add a total row to the query.
   2. Group the results by the Industry field, count the CompanyName field, and average the StartingSalary field.
   3. Widen each column to see all data and field names, as shown in Figure 1, using the following measurements: Change the Industry column field width to 35, the CountOfCompanyName column field width to 22, and the AvgOfStartingSalary column field width to 19.
      
      Save and close the IndustryAnalysis query.

 Figure 1: Final IndustryAnalysis Query in Datasheet View

 

 

6. Create a new Crosstab query, without using the Query Wizard, and complete the following tasks:
    
   1. Add the Industry field from the Companies table and add the JobTitle and StartingSalary fields from the Jobs table to the query.
   2. Set the Industry field label as the column heading and the JobTitle field label as the row heading.
   3. In the Value area, average the StartingSalary field.
   4. Save the query, using JobCrosstab as the query name.
   5. Widen each column to see all data and field names, as shown in Figure 2. 
      Save and close the JobCrosstab query.

 Figure 2: Final JobCrosstab Query in Datasheet View

 

 

7. Open the JobSeekerEntry form and complete the following tasks:
   1. Add a combo box to the Form Header section at about the 4" mark on the horizontal ruler using the Combo Box Wizard.
   2. The combo box should find a record.
   3. Include the LastName and FirstName fields in the combo box list.
   4. Use FIND as the label for the new combo box.
   5. Save the form.
   6. Use the new combo box to find the "Thompson, Christina" record, and then close the JobSeekerEntry form.
8. Open the JobsEntry form and complete the following tasks:
   1. Add a subform control below the Posting Date label.
   2. The subform should contain all the fields from the ApplicationDates query.
   3. In the JobID field, display the ApplicationDates field value for each record in the Jobs table.
   4. Use the default name for the subform.
      
      Save the JobsEntry form, open it in Form View, and then close it.
9. Open the CompanyEntry form and complete the following tasks:
   1. Right-align the right edges of the labels in the Detail section.
   2. Right-align the content within the labels in the Detail section.
      
      Save the CompanyEntry form.
10. With the CompanyEntry form still open, change the record source to the CompaniesSortedByName query.
11. With the CompanyEntry form still open, modify the tab order so that the CompanyID field is the first item in the Detail section.
    
    Save and close the form. The form should match Figure 3.

 Figure 3: Final CompanyEntry Form in Form View

 

 

12. Open the JobListing report, and then add a horizontal line above the text box in the Report Footer section that sums the StartingSalary field values. The line should be just above and approximately the same width as the text box. The line will be positioned between the Report Footer section bar and the text box. Save the JobListing report.
13. With the JobListing report still open, complete the following tasks:
    1. Add the PostingDate field to the left side of the Detail section.
    2. Delete the PostingDate label.
    3. Position the left edge of the PostingDate text box at about the 1" mark on the horizontal ruler.
    4. Add a label to the Report Footer section at about the 1" mark on the horizontal ruler.
    5. Use Internal Use Only as the caption for the label.
       
       Save the JobListing report.
14. With the JobListing report still open, sort the records in ascending order on the JobTitle field under the existing CompanyName grouping field. Save and close the report. The JobListing report should match Figure 4 when viewed in Print Preview.

 Figure 4: Final JobListing Report in Print Preview

 

 

15. Open the JobSeekerListing report and complete the following tasks:
    1. Expand the Report Footer section by about 0.5".
    2. Add a text box to the Report Footer section at about the 1" mark on the horizontal ruler and use the expression =Count([LastName]) as the control source for the text box.
    3. Delete the new label in the Report Footer section.
       
       Save the JobSeekerListing report, preview it, and then close it.
16. Make a copy of the CompanyListing report using the name CompanyInfo for the new report. 
17. Open the CompanyInfo report and change the record source to the CompaniesSortedByName query. Save the CompanyInfo report.
18. With the CompanyInfo report still open, add page numbers to the center of the Page Footer section using the "N of M" format. Save the CompanyInfo report, preview it, and then close it.
19. Open the JobSalaries report and create a chart at about the 0.5" mark on the horizontal ruler in the Report Footer section with the following details:
    1. Include the StartingSalary and CompanyName fields from the JobSalaryIndustry query.
    2. Use a Bar Chart.
    3. Use the CompanyName field as the axis and the sum of the StartingSalary field as the data.
    4. Do not link the report and the chart.
    5. Use Salary Total By Industry as the title for the chart, and do not display a legend.
    6. Resize the chart to be about 3" tall and 8" wide.
       
       Save the JobSalaries report, preview it to ensure it looks like Figure 5, and then close it.

 Figure 5: Final JobSalaries Report in Print Preview

 

 

20. Export the Companies table as an HTML document to the default folder with the default file name, using the following instructions:
    1. Do not include formatting and layout.
    2. Save the export steps using Export-Companies to HTML as the name.
    3. Do not include an export description.
21. Export the CompanyListing report as an HTML document to the default folder with the default file name, using the following instructions:
    1. Do not select an HTML template.
    2. Use default encoding.
    3. Save the export steps using Export-CompanyListing to HTML as the name.
    4. Do not include an export description.
22. Import the file Support_AC19_CT_CS5-8_StateProvs.csv as a new table, using the following instructions:
    1. The file should be in a comma-delimited format.
    2. The first row should contain field names.
    3. Do not modify the fields.
    4. The StateAbbrev field should be the primary key.
    5. Use StatesProvs as the new table name.
    6. Do not save the import steps.
23. Export the Jobs table in XML format to the default folder with the default file name, using the following instructions:
    1. Export the Data XML and Schema XSD files.
    2. Save the export steps using Export-Jobs to XML as the name.
    3. Do not include an export description.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.