New Perspectives Access 2019 | Modules 9-12: SAM Capstone Project 1a Midwest Executive Professionals Completing a Database Application   GETTING STARTED Open the file NP_AC19_CS9-12a_FirstLastName_1

Subject:MS AccessPrice:24.99 Bought6

Share With

New Perspectives Access 2019 | Modules 9-12: SAM Capstone Project 1a

Midwest Executive Professionals

Completing a Database Application

GETTING STARTED

• Open the file NP_AC19_CS9-12a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_CS9-12a_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_CS9-12a_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 query in Design View and complete the following:
    
   1. Add all of the fields from the JobSeekers table to the query grid. You need to create a backup copy of the table at this point in time for use by another department.
   2. Change the query into a Make Table query using 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 query in Design View and complete the following:
    
   1. Add all of the fields from the JobSeekersImport table to the query grid. This data was previously imported from an Excel spreadsheet maintained by a remote office and needs to be added to the JobSeekersBackup table.
   2. Change the query into an Append query 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 query in Design View and complete the following:
    
   1. Add all of the fields from the Applications table to the query grid. You need to delete some of the older records from this table.
   2. Add criteria to select all of the records with an ApplicationDate prior to 8/1/2021.
   3. Change the query into a Delete query.
   4. Run the query which will delete two rows.
   5. Save the query with the name DeleteApplications and then close it.
4. Create a new query in Design View and complete the following:
    
   1. Add the FollowupDate field from the Applications table to the query grid. You need to update all of the records in the Applications table to include a date in the FollowupDate field.
   2. Change the query into an Update query.
   3. Enter 11/1/2021 as the Update To value for the FollowupDate field.
   4. Run the query which will update 21 rows.
   5. Save the query with the name UpdateFollowupDate and then close it.
5. Copy and paste the HighSalaries query using CompaniesWithoutJobs as the new query name. Open the CompaniesWithoutJobs query in Design View and complete the following:
    
   1. Delete the criteria row that selects only those records with a StartingSalary greater than 50000.
   2. Change the join properties to include all records from the Companies table.
   3. Add Is Null criteria to the JobTitle field.
   4. Open the query in Datasheet View to observe two records as shown in Figure 1, then save and close the CompaniesWithoutJobs query.

Figure 1: Final CompaniesWithoutJobs Query in Datasheet View

6. Create a new macro in Design View with the following information:
    
   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.
   4. Run the macro, and then close both reports as well as Macro Design View.
7. Copy the OpenForms macro, and paste it as a new macro using OpenQueries as the name. Then complete the following:
    
   1. Open the OpenQueries macro in Design View.
   2. Delete the three OpenForm macro actions.
   3. Add an OpenQuery macro action using HighSalaries for the Query Name argument.
   4. Add a second OpenQuery macro action using JobCrosstab for the Query Name argument.
   5. Add a macro comment above the first OpenQuery macro action using These queries were created for the President for the comment text.
      
      The final Design View for the OpenQueries macro should look like Figure 2.
      Save and close the OpenQueries macro.

Figure 2: Final OpenQueries Macro in Design View

8. Open the OpenForms macro in Design View and complete the following:
    
   1. Move the macro action that opens the JobsEntry form to be the second macro action.
   2. Collapse all macro actions then save and close the OpenForms macro.
9. Open the JobsEntry form in Design View and complete the following:
    
   1. Open the Visual Basic Editor window for the form.
   2. Insert the comment 'Highlights jobs in the energy industry as a new second line in the procedure just above the If statement.
   3. Save and close the Visual Basic Editor window then save and close the JobsEntry form.
10. Create a standard module and save it using CustomFunctions as the name, and then create a new function within it by entering the code shown below, 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 in Design View and complete the following:
     
    1. Open the Visual Basic Editor window for the form.
    2. Insert the following code just above the End Sub statement in the Form_Current procedure:
       
       If StartingSalary.Value > 60000 Then
           HighWageLabel.Visible = True
       Else
            HighWageLabel.Visible = False
       End If
        
    3. Save and close the Visual Basic Editor window then save and open the JobsEntry form in Form View.
    4. Navigate through several records to test your updated procedure then close the JobsEntry form.
12. Open the JobSeekerEntry form in Design View and complete the following:
     
    1. Add a VBA event procedure to the Close command button with the following code:
       
       Private Sub Close_Click()
       DoCmd.Close
       End Sub
        
    2. Save and close the Visual Basic Editor window, save and open the JobSeekerEntry form in Form View, then click the Close command button to test the procedure.
13. Create a linked table to the Support_AC19_CS9-12a_StatesProvs.xlsx Excel file located in the default folder using the following information:
     
    1. The first row contains column headings.
    2. Use StatesProvs (which is the default) for the linked table name.
    3. Do not save the import steps.
14. Create a Navigation form with the following instructions:
     
    1. Use a Horizontal tabs layout.
    2. Add the JobSalaries report as the first tab, and update the text in the tab to Job Salaries by adding a space.
    3. Add the JobSeekerEntry form as the second tab, and update the text in the tab to Job Seeker Entry by adding two spaces.
    4. Add the CompanyEntry form as the third tab, and update the text in the tab to Company Entry by adding a space.
    5. Save the form with the name Nav and open it in Layout View.
15. With the Nav form open in Layout view, move the Company Entry tab to the first tab position on the far left.
    
    Save the Nav form, switch it to Form View, and close it. 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 website to submit your completed project.