New Perspectives Access 365 | Modules 5-8: SAM Capstone Project A University of Waterloo Co-op Placement
Improving Tables, Queries, Forms, and Reports
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_CS5-8A_FirstLastName_1.accdb as NP_AC365_CS5-8A_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_CS5-8A_StateProvs.csv
3. With the file NP_AC365_CS5-8A_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 improve an existing database by modifying tables, queries, forms, and reports. Open the Students table in Design View and complete the following:
a. After the existing Phone field add a new field named Email with a Hyperlink data type.
b. Add a second new field named Comments with a Long Text data type. Save and close the Students table.
2. Open the CoopJobs table in Design View and complete the following:
a. Add Salary as the caption property to the StartingSalary field.
b. Add =Date() as the Validation Rule property for the PostingDate field.
c. Add Date must be today or later as the Field Validation Text property for the PostingDate field. Save and close the CoopJobs table. If prompted to test data integrity click Yes.
3. Open the Companies table in Design View and complete the following:
a. Use the Lookup Wizard to lookup values for the Industry field.
b. The lookup field should get its values from the Industries table.
c. Select the Industry field and sort the records in ascending order by the Industry field.
d. Widen the Industry field to get the best fit for all of the values in the list.
e. Enable data integrity with Restrict Delete on the relationship and use Industry as the label for the lookup field.
f. Save the table if prompted. Close the Companies table.
4. Open the SubmissionListing query in Design View and complete the following:
a. Add =[Enter minimum salary] as parameter criteria to the StartingSalary field.
b. Run the query using 70000 when prompted. Save and close the SubmissionListing query.
5. Open the IndustrySalaries query in Design View and complete the following:
a. Add the Total row to the query grid.
b. Group By the Industry field, Count the CompanyName field, and Avg (average) the StartingSalary field.
c. Switch to Datasheet View and widen each column to see all data and field names as shown in Figure 1 with the following measurements: Change the Industry column field width to 35, the CountOfCompanyName to 22, and the AvgOfStartingSalary to 19. Save and close the IndustrySalaries query.
Figure 1: Final IndustrySalaries Query in Datasheet View
6. Create a new query in Design View and complete the following:
a. Add the Companies and CoopJobs tables.
b. Add the Industry field from the Companies table and the CoopJobTitle and StartingSalary field from the CoopJobs table to the query grid.
c. Change the query from a Select query to a Crosstab query.
d. Put the Industry field in the Column Heading area and the CoopJobTitle field in the Row Heading area.
e. In the Value area, Avg (Average) the StartingSalary field.
f. Use CoopJobCrosstab to name and save the query.
g. Switch to Datasheet View and widen each column to see all data and field names as shown in Figure 2. Save and close the CoopJobCrosstab query.
Figure 2: Final JobCrosstab Query in Datasheet View
7. Open the CoopJobseekerEntry form in Design View and complete the following:
a. Add a combo box to the Form Header section at about the 4" mark on the horizontal ruler using the Combo Box Wizard.
b. The combo box should find a record.
c. Select the LastName and then FirstName fields for the combo box list.
d. Use FIND as the label for the new combo box.
e. Save the form and open it in Form View.
f. Use the new combo box to find the Baker, Camila record. Close the CoopJobseekerEntry form.
8. Open the CoopJobsEntry form in Design View and complete the following:
a. Add a subform control below the Posting Date label using the Subform Wizard.
b. The subform should contain all the fields from the SubmissionDates query.
c. Show SubmissionDates for each record in CoopJobs using CoopJobID.
d. Use the default name for the subform. Save the CoopJobsEntry form, open it in Form View, and then close it.
9. Open the CompanyEntry form in Design View and complete the following:
a. Right-align the right edges of the labels in the Detail section.
b. Right-align the content within the labels in the Detail section. Save the CompanyEntry form.
10. With the CompanyEntry form still open in Design View, change the Record Source to the CompaniesSortedByName query.
11. With the CompanyEntry form still open in Design View, change the tab order in the Detail section so that CompanyID is the first item in the list. Save the form and switch to Form View as shown in Figure 3, and then close the CompanyEntry form.
Figure 3: Final CompanyEntry Form in Form View
12. Open the CoopJobListing report in Design View and add a horizontal line above the text box in the Report Footer section that sums the StartingSalary. 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 and be approximately 1.7" wide that is the width of the text box. Save the CoopJobListing report.
13. With the CoopJobListing report still open in Design View complete the following:
a. Add the PostingDate field to the left side of the Detail section.
b. Delete the PostingDate label.
c. Position the left edge of the PostingDate text box at about the 1" mark on the horizontal ruler.
d. Add a label to the Report Footer section at about the 1" mark on the horizontal ruler.
e. Use Confidential as the caption for the label. Save the CoopJobListing report.
14. With the CoopJobListing report still open in Design View, add a sort order under the existing CompanyName grouping field to sort the records in ascending order on the CoopJobTitle field. Save the report and display the CoopJobListing report in Print Preview as shown in Figure 4, then close the CoopJobListing report.
Figure 4: Final CoopJobListing Report in Print Preview
15. Open the CoopJobseekerListing report in Design View and complete the following:
a. Expand the Report Footer section by about 0.5".
b. Add a text box to the Report Footer section at about the 1" mark on the horizontal ruler and use the expression of =Count([LastName]) as the Control Source for the text box.
c. Delete the new label in the Report Footer section. Save the CoopJobseekerListing report, preview it, then close it.
16. Copy the CompanyListing report and paste it as the CompanySorted report.
17. Open the CompanySorted report in Design View and change the Record Source to the CompaniesSortedByName query. Save the CompanySorted report.
18. With the CompanySorted report still open in Design View, add page numbers to the report in an N of M format to the right of the Page Footer. Save the CompanySorted report, preview it, then close it.
19. Open the CoopJobsalaries report in Design View and use the Chart Wizard to create a chart at about the 0.5" mark on the horizontal ruler in the Report Footer section with the following information:
a. Base the chart on the CoopJobsalaryIndustry query.
b. Include the StartingSalary and CompanyName fields.
c. Select a Bar Chart.
d. Use the CompanyName field in the Axis area and the sum of the StartingSalary field in the Data area.
e. Do not link the report and the chart. Ignore the pop-up message and click Next.
f. Use Salary Total By Industry as the title for the chart and do not display a legend.
g. Resize the chart to be about 2.75" tall and 7" wide. Save the CoopJobsalaries report, print preview it as shown in Figure 5, and close it.
Figure 5: Final CoopJobsalaries Report in Print Preview
20. Export the Companies table as an HTML Document to the default folder with the default file name with the following information:
a. Do not include formatting and layout.
b. Save the export steps using Export-Companies to HTML for the name.
c. Do not include an export Description.
21. Export the CompanyListing report as an HTML Document to the default folder with the default file name with the following information:
a. Do not select an HTML template.
b. Use default encoding.
c. Save the export steps as Export-CompanyListing to HTML.
d. Do not include an export Description.
22. Import the Support_AC365_CS5-8A_StateProvs.csv file as a new table with the following information:
a. The file is in a comma-delimited format.
b. The first row contains field names.
c. Do not modify the fields.
d. The StateAbbrev field should be the primary key.
e. Use StatesProvs as the new table name.
f. Do not save the import steps.
23. Export the CoopJobs table in an XML format to the default folder with the default file name with the following information:
a. Export both the Data XML and Schema XSD files.
b. Save the export steps as Export-CoopJobs to XML.
c. 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 website to submit your completed project.