New Perspectives Access 365 | Modules 9-12: SAM Capstone Project A University of Waterloo Co-op Placement

GETTING STARTED

1. Save the file NP_AC365_CS9-12A_FirstLastName_1.accdb as NP_AC365_CS9-12A_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. To complete this Project, you will also need the following files:

Support_AC365_CS9-12A_StatesProvs.xlsx

3. With the file NP_AC365_CS9-12A_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. The University of Waterloo has a variety of co-operative (co-op) degree programs where students are placed in co-op/intern job positions to gain work experience in their field. You work in the University of Waterloo co-op placement department as a senior placement manager. You are developing an Access database application to help manage and analyze companies, co-op jobs, students, and co-op placements. 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:

a. Add all of the fields from the Students 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.

b. Change the query into a Make Table query using StudentsBackup as the new table name.

c. Run the query to paste 400 records into the new table.

d. Save the query with the name MakeStudentsBackup and then close it.

2. Create a new query in Design View and complete the following:

a. Add all of the fields from the StudentsImport 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 StudentsBackup table.

b. Change the query into an Append query to the StudentsBackup table.

c. Run the query to append 115 records.

d. Save the query with the name AppendStudents and then close it.

3. Create a new query in Design View and complete the following:

a. Add all of the fields from the Submissions table to the query grid. You need to delete some of the older records from this table.

b. Add criteria to select all of the records with an ApplicationDate prior to 8/1/2029.

c. Change the query into a Delete query.

d. Run the query which will delete two rows.

e. Save the query with the name DeleteSubmissions and then close it.

4. Create a new query in Design View and complete the following:

a. Add the FollowupDate field from the Submissions table to the query grid. You need to update all of the records in the Submissions table to include a date in the FollowupDate field.

b. Change the query into an Update query.

c. Enter 11/1/2029 as the Update To value for the FollowupDate field.

d. Run the query which will update 21 rows.

e. 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:

a. Delete the criteria row that selects only those records with a StartingSalary greater than 50000.

b. Change the join properties to include all records from the Companies table.

c. Add Is Null criteria to the CoopJobTitle field.

d. 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:

a. Add an action to open the CompanyListing report in Print Preview.

b. Add a second action to open the CoopJobSalaries report in Print Preview.

c. Save the macro using OpenReports as the macro name.

d. 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:

a. Open the OpenQueries macro in Design View.

b. Delete the three OpenForm macro actions.

c. Add an OpenQuery macro action using HighSalaries for the Query Name argument.

d. Add a second OpenQuery macro action using CoopJobCrosstab for the Query Name argument.

e. Add a macro comment above the first OpenQuery macro action using These queries were created for the Director 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:

a. Move the macro action that opens the CoopJobsEntry form to be the second macro action.

b. Collapse all macro actions then save and close the OpenForms macro.

9. Open the CoopJobsEntry form in Design View and complete the following:

a. Open the Visual Basic Editor window for the form.

b. Insert the comment Highlights jobs in the insurance industry as a new second line in the procedure just above the If statement.

c. Save and close the Visual Basic Editor window then save and close the CoopJobsEntry 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 Index(SalaryValue, IndustryValue) If SalaryValue 50000 Then Index = 3 ElseIf SalaryValue 60000 Or IndustryValue = "Insurance" Then Index = 4 ElseIf SalaryValue 60000 And IndustryValue = "Insurance" Then Index = 5 End If End Function Save and close the CustomFunctions module.

Figure 3: Final Index Function in the CustomFunctions Standard Module

11. Open the CoopJobsEntry form in Design View and complete the following:

a. Open the Visual Basic Editor window for the form.

b. Insert the following code just above the End Sub statement in the Form_Current procedure: If StartingSalary.Value 70000 Then HighSalaryLabel.Visible = True Else HighSalaryLabel.Visible = False End If

c. Save and close the Visual Basic Editor window then save and open the CoopJobEntry form in Form View.

d. Navigate through several records to test your updated procedure then close the CoopJobEntry form.

12. Open the CoopJobSeekerEntry form in Design View and complete the following:

a. Add a VBA event procedure to the Close command button with the following code: Private Sub cmdClose_Click() DoCmd.Close End Sub

b. Save and close the Visual Basic Editor window, save and open the CoopJobSeekerEntry form in Form View, then click the Close command button to test the procedure.

13. Create a linked table to the Support_AC365_CS9-12a_StatesProvs.xlsx Excel file located in the default folder using the following information:

a. The first row contains column headings.

b. Use StatesProvs (which is the default) for the linked table name.

c. Do not save the import steps.

14. Create a Navigation form with the following instructions:

a. Use a Vertical tabs left layout.

b. Add the CoopJobSalaries report as the first tab, and update the text in the tab to Coop Job Salaries by adding two spaces.

c. Add the CoopJobSeekerEntry form as the second tab, and update the text in the tab to Coop Job Seeker Entry by adding three spaces.

d. Add the CompanyEntry form as the third tab, and update the text in the tab to Company Entry by adding a space.

e. Expand the width of the tabs so that all text in the tabs are visible/fit.

f. Save the form with the name Main and open it in Layout View.

15. With the Main form open in Layout view, move the Company Entry tab to the first tab position on the top. Save the Main form, switch it to Form View, and close it. The final Main form is shown in Form View in Figure 4.

Figure 4: Final Main Form in Form View

16. Change the Access options as follows:

a. Use UW Coop Placement as the Application Title.

b. Set the Main form as the opening Display Form for the database.

c. 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 website to submit your completed project.