New Perspectives Access 2019 | Modules 9-12: SAM Critical Thinking Capstone Project 1c Midwest Executive Professionals Completing a Database Application     GETTING STARTED Open the file NP_AC19_CT_CS9-12c_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought5

Share With

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

Midwest Executive Professionals

Completing a Database Application

• GETTING STARTED

• Open the file NP_AC19_CT_CS9-12c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_CT_CS9-12c_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_CS9-12c_StatesProvs.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 Research Department of Midwest Executive Professionals as a research analyst. You 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 complete a database application by building advanced queries, macros, modules, and navigation forms.
   
   Create a new Make Table query and complete the following tasks:
   1. Add all of the fields from the JobSeekers table to the query.
   2. You need to create a backup copy of the table at this point in time for use by another department. Use JobSeekersBackup as the new table name.
   3. Run the query to paste 400 records into the new table.
   4. Save the query with the name MakeJobSeekersBackup and then close it.
2. Create a new Append query and complete the following tasks:
   1. Add all of the fields from the JobSeekersImport table to the query. This data was previously imported from an Excel spreadsheet maintained by a remote office and needs to be added to the JobSeekersBackup table.
   2. Modify the query to append the data to the JobSeekersBackup table.
   3. Run the query to append 115 records.
   4. Save the query with the name AppendJobSeekers and then close it.
3. Create a new Delete query and complete the following tasks:
   1. Add all of the fields from the Applications table to the query.
   2. You need to delete some of the older records from this table. Add criteria to select all of the records with an ApplicationDate field value prior to 8/1/2021.
   3. Run the query, which will delete two rows.
   4. Save the query with the name DeleteApplications and then close it.
4. Create a new Update query and complete the following tasks:
   1. Add the FollowupDate field from the Applications table to the query.
   2. You need to update all of the records in the Applications table to include a date in the FollowupDate field. Modify the query to update the records with the value 11/1/2021 in the FollowupDate field.
   3. Run the query, which will update 21 rows.
   4. Save the query with the name UpdateFollowupDate and then close it.
5. Create a copy of the HighSalaries query using CompaniesWithoutJobs as the new query name. Open the CompaniesWithoutJobs query and complete the following tasks:
   1. Delete the criteria row that selects only those records with a StartingSalary field value greater than or equal to 50000.
   2. Modify the query to include all records from the Companies table.
   3. Modify the query to select all records in which the JobTitle field is blank.
      The query should return two records, as shown in Figure 1. Close the query.

 Figure 1: Final CompaniesWithoutJobs Query in Datasheet View

6. Create a new macro as follows:
   1. Add an action to open the CompanyInfo report in Print Preview.
   2. Add a second action to open the JobSalaries report in Print Preview.
   3. Save the macro using OpenReports as the macro name. Close the macro.
7. Create a copy of the OpenForms macro using OpenQueries as the name, and then complete the following tasks:
   1. Delete the three OpenForm macro actions.
   2. Add an action to open the HighSalaries query.
   3. Add a second action to open the JobCrosstab query.
   4. Add a macro comment above the first OpenQuery macro action using These queries were created for the President for the comment text.
      
      The OpenQueries macro should look like Figure 2 when in Design View. Save and close the OpenQueries macro.

 Figure 2: Final OpenQueries Macro in Design View

8. Open the OpenForms macro, and then modify the macro to open the JobsEntry form second. Save and close the OpenForms macro.
9. Open the JobsEntry form and insert the comment 'Highlights jobs in the energy industry as a new second line in the procedure just above the If statement within the Visual Basic code.
   
   Save and close the Visual Basic Editor window, and then save and close the JobsEntry form.
10. Create a standard module, save it using CustomFunctions as the name, and then create a new function within it by entering the following code (which is also shown in Figure 3):
    
    Function Demand(SalaryValue, IndustryValue)
    If SalaryValue > 50000 Then
    Demand = 3
    ElseIf SalaryValue > 60000 Or IndustryValue = "Energy" Then
    Demand = 4
    ElseIf SalaryValue > 60000 And IndustryValue = "Energy" Then
    Demand = 5
    End If
    End Function
    
    Save and close the CustomFunctions module.

 Figure 3: Final Demand Function in the CustomFunctions Standard Module

11. Open the JobsEntry form, and then insert the following code just above the End Sub statement in the Form_Current procedure within the Visual Basic code:
    
    If StartingSalary.Value > 60000 Then
    HighWageLabel.Visible = True
    Else
    HighWageLabel.Visible = False
    End If
    
    Save and close the Visual Basic Editor window and the JobsEntry form.
12. Open the JobSeekerEntry form, and then add a VBA event procedure to the Close command button with the following code:
    
    Private Sub Close_Click()
    DoCmd.Close
    End Sub
    
    Save and close the Visual Basic Editor window, and then save and close the JobSeekerEntry form.
13. Create a linked table to the Support_AC19_CT_CS9-12c_StatesProvs.xlsx Excel file (located in the default folder) as follows:
    1. The first row should contain column headings.
    2. Use StatesProvs (which is the default name) for the linked table name.
        
14. Create a Navigation form with the following instructions:
    1. Use the Horizontal tabs layout.
    2. Add the JobSalaries report as the first tab, and then update the text in the tab to "Job Salaries" by adding a space between the words.
    3. Add the JobSeekerEntry form as the second tab, and then update the text in the tab to "Job Seeker Entry" by adding a space between each word.
    4. Add the CompanyEntry form as the third tab, and then update the text in the tab to "Company Entry" by adding a space between the words.
    5. Save the form using Nav as the form name.
15. With the Nav form open, move the Company Entry tab to be the first tab.
    
    Save and close the Nav form. The final Nav form is shown in Form view in Figure 4.

 Figure 4: Final Nav Form in Form View

16. Change the Access options as follows:
    1. Use Midwest Executive Professionals as the application title.
    2. Set the Nav form as the opening Display form for the database.
    3. Do not close and reopen the database.

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 websi