Homework answers / question archive / New Perspectives Access 2019 | Modules 1-4: SAM Critical Thinking Capstone Project 1c Midwest Executive Professionals IMPROVE A DATABASE BY CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS     GETTING STARTED Open the file NP_AC19_CT_CS1-4c_FirstLastName_1

New Perspectives Access 2019 | Modules 1-4: SAM Critical Thinking Capstone Project 1c Midwest Executive Professionals IMPROVE A DATABASE BY CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS     GETTING STARTED Open the file NP_AC19_CT_CS1-4c_FirstLastName_1

MS Access

Share With

---

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

Midwest Executive Professionals

IMPROVE A DATABASE BY CREATING AND MODIFYING TABLES, QUERIES, FORMS, AND REPORTS

 

 

• GETTING STARTED

• Open the file NP_AC19_CT_CS1-4c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_CT_CS1-4c_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_CS-1-4c_Industries.xlsx
• 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 firm's Research Department as a research analyst. You are developing an Access database application to help manage and analyze companies, jobs, applicants, and job placements facilitated by Midwest Executive Professionals.
   
   Modify the Applications table to meet the following requirements:
   1. A field named FollowupDate with the Date/Time data type should be located after the ApplicationDate field.
   2. The ID field should use ApplicationID as the name.
      
      Save and close the Applications table.
2. Complete the following tasks in the JobSeekers table:
   1. Delete the record for Richard Marlay (JobSeekerID 14).
   2. In the record for JobSeekerID 10, change John to Jonathan.
   3. Enter a new record into the table using the information in Table 1.
      
      Close the JobSeekers table.

 

JobSeekerID	(AutoNumber will automatically number this field)
FirstName	Leonardo
LastName	Eagan
Phone	5551118887

 

3. In the Navigation Pane, make Industries the new name for the Categories table.
4. Append the records from the file Support_AC19_CT_CS-1-4c_Industries.xlsx to the Industries table. Do not save the import steps. (Hint: If a warning message appears, click Open.)
5. Create a new table with the following specifications:
   1. The table should have Employees as the name.
   2. The table should have a field named EmployeeID with the Number data type. The EmployeeID field should be set as the primary key field. If necessary, remove the automatically generated ID field.
   3. The table should also contain fields named EmployeeFirst and EmployeeLast in the order shown. They both should have the Short Text data type, and the Field Size property for both should be 20.
   4. A field named Email with the Hyperlink data type should follow the EmployeeLast field and have the Required property set to Yes.
   5. Following the Email field should be a field named Salary with the Currency data type. The Default Value property for the Salary field should be 45000.
   6. The last field in the table should be named Extension and use 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.
      
      Save the table, and then close it.
6. Complete the following tasks in the JobSeekers table:
   1. Delete the Comments field. (Hint: It currently does not have any data.)
   2. Move the Phone field to be the last field in the datasheet.
   3. Sort the records in ascending order by the LastName field.
      
      Save and close the JobSeekers table.
7. Create a new query using the Companies and Jobs tables with the following specifications:
   1. The query should include the CompanyName and Industry fields from the Companies table and the JobTitle and StartingSalary fields from the Jobs table.
   2. The tables should be joined using the CompanyID field from the Companies table and the Company field from the Jobs table.
   3. Save the query, using HighSalaries as the name.
8. Add criteria to the HighSalaries query to select only those records where the StartingSalary is greater than or equal to 50000, and then save the query. The query results should match Figure 1. Close the query.

 

 

9. Create a new query with the following specifications:
   1. Select all four fields from the JobSeekers table, and then select the ApplicationDate field from the Applications table.
   2. Create a detail query.
   3. Save the query using ApplicationDates as the title for the query, and then close the query.
10. Modify the ApplicationListing query to meet the following requirements:
    1. The query should select all records with Administrative Assistant or User Support Director in the JobTitle field.
    2. The records should be sorted in ascending order first by LastName and then by FirstName.
       
       Run the query to display it in Datasheet View as shown in Figure 2, and then save and close it.

 

 

11. Add criteria to the DirectorSeptember query to select all records that contain Director anywhere in the JobTitle field and have an ApplicationDate field value greater than or equal to 9/1/2018. Run the query to display it in Datasheet View as shown in Figure 3, and then save and close it.

 

 

12. Create a new form that meets the following requirements:
    1. The form should include all fields from the Jobs table.
    2. The form should use the Columnar layout.
    3. The form should use JobsEntry as the title.
       
       Save and close the JobsEntry form.
13. Create a new report that meets the following requirements:
    1. The report should include all fields from the Companies table.
    2. The records should be grouped by the Industry field.
    3. The records should be sorted in ascending order by the CompanyName field.
    4. The report should use the Stepped layout and the Portrait orientation.
    5. The report should use CompanyListing as the title.
       
       Save and close the CompanyListing report.
14. In the JobSeekerEntry form, enter Jacob as the FirstName value for the first record to replace Jack, and then add a new record in the main form with the information in Table 2. Save the form.

 

JobSeekerID	(AutoNumber field will automatically increment)
FirstName	Camilla
LastName	Escobar

 

15. Modify the JobSeekerEntry form as follows:
    1. Right-align the text in the JobSeekerID, FirstName, and LastName labels.
    2. Change the FirstName label to First Name, and then change the LastName label to Last Name.
    3. Delete the Close command button in the Form Footer section.
       
       Save the form.
16. Continue modifying the JobSeekerEntry form as follows:
    1. Add the Phone field to the form and position it below the Last Name label.
    2. Change the font color for the Phone label to Automatic (black).
    3. Apply the Colored Outline – Blue, Accent 1 theme (1st row, 2nd column in the Theme Styles gallery) to the Close command button in the Form Header section.
       
       The form should appear as shown in Figure 4 in Form View. Save and close it.

 

 

17. Add conditional formatting to the JobListing report so that any StartingSalary field value greater than or equal to $70,000 is formatted using the Green 3 background color (7th column, 4th row in the Standard Colors palette). Save the JobListing report.
18. Further modify the JobListing report with the following options:
    1. Change the background color of the Starting Salary label in the Page Header section to the same color, Green 3 (7th column, 4th row in the Standard Colors palette).
    2. Group the two labels in the Report Header section together in a tabular layout.
       
       Save and display the report in Report View as shown in Figure 5, and then close it.

 

 

19. Modify the JobSeekerListing report to meet the following requirements:
    1. The report should use the Green theme colors.
    2. The report should use the Franklin Gothic theme fonts.
       
       The report should look like Figure 6 when displayed in Report View. Save and close the report.

 

 

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.