New Perspectives Access 365 | Module 7: SAM Project A Lighthouse Cybersecurity

GETTING STARTED

1. Save the file NP_AC365_7A_FirstLastName_1.accdb as NP_AC365_7A_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. With the file NP_AC365_7A_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. As a project assistant for Lighthouse Cybersecurity, you have created an Access database to track new clients and their projects, services, and analysts. To meet a need for well-formatted, professional-quality information, you will modify advanced reports. Open the AnalystSalary report in Report Layout View and modify it as follows to identify the data more clearly:

a. Add a title to the report to identify its contents.

b. Modify the title to change its font size to 22 pt.

c. Resize the title label control so it is only wide enough to display its text.

d. Center the AnalystID label and the data in the AnalystID text box. Save and close the AnalystSalary report.

2. Open the ClientGrouped report in Report Design View, and add a grouping level on the RegionCode field to group the data by region. Save and close the report.

3. Open the AnalystList report in Report Design View and modify the layout as follows to display the data in columns:

a. Use 2 as the number of columns.

b. Use a Down, then Across layout. Save and open the AnalystList report in Print Preview, and then close the AnalystList report.

4. Open the RegionList report in Report Design View and modify the design as follows to remove clutter from the report:

a. Remove the control layouts from the controls in the Report Header and Detail sections.

b. Remove the controls from the Page Header section, and then reduce the height of the section to 0".

c. Resize the Region Descriptions label in the Report Header section so that the right edge is at the 3" mark on the horizontal ruler. Save the RegionList report.

5. With the RegionList report still open in Report Design View, format the report as follows to make it easier to read:

a. Select all controls on the report and change the font color to Automatic (Black). Set the Alternate Back Color of the Detail section to Light Blue 1 (5th column, 2nd row in the Standard Colors palette). Save and view the RegionList report in Print Preview as shown in Figure 1, and then close the RegionList report.

Figure 1: Final RegionList Report in Print Preview

6. Create a new report in Report Design View as follows to list client revenue data:

a. Set the Record Source property to the ClientFees query.

b. Add all the fields from the Field List to the top of the Detail section of the report at about the 2" mark on the horizontal ruler.

c. Change the height of the Detail section so it is only as tall as necessary to display the controls (about 1.25" tall).

d. Close the Page Header and Page Footer sections so the height of each section is 0". Save the report with the name ClientRevenue and close it.

7. In the Navigation Pane, copy the ClientList report and paste it, using ClientDetails as the name, to create a similar report with slightly different contents.

8. Open the ClientDetails report in Report Design View and modify it as follows to display complete field values:

a. Delete the two text boxes in the Page Footer section.

b. Move the ID label and the ClientID text box so their right edges are at the 2.5" mark on the horizontal ruler.

c. Increase the size of the ClientName text box so that its right edge is at the 2" mark on the horizontal ruler.

d. Use Client Details as the caption for the label in the Report Header section. If necessary, resize the label so that the caption is fully visible. Save the ClientDetails report.

9. While still working in Report Design View of the ClientDetails report, add a sort order to sort the records in ascending order by ClientName to make it easier to find information. Save and preview the report, a portion of which is shown in Figure 2, and then close the ClientDetails report.

Figure 2: Final ClientDetails Report in Print Preview

10. Use the Label Wizard to create labels based on the Analyst table as follows:

a. Select the Avery USA 5096 product number. Use the Arial font with a font size of 12, a Bold font weight, and Automatic (1st column, 6th row in the Basic Colors palette) text color.

b. On the first line of the label, insert the LastName field, followed by a comma, a space, and the FirstName field.

c. On the second line of the label, insert the AnalystID field.

d. On the third line of the label, insert the Region field.

e. Sort the report by LastName. Save the report, using AnalystLabels as the name. Confirm that it matches Figure 3, and then save and close it. (Hint: If a message appears warning that not all information is displayed, click OK.)

Figure 3: AnalystLabels Report in Print Preview

11. Open the ProjectList report in Report Design View and then modify it as follows to add identifying information:

a. Add page numbers to the report using the Page N of M format positioned in the left side of the Page Footer.

b. Add the date (but not the time) to the report using the Medium Date (07-Feb-29) format. Save the ProjectList report.

12. With the ProjectList report still open in Report Design View, modify the report as follows to calculate the total monthly fees:

a. Adjust the Report Footer section to be about 0.5" tall.

b. Add a text box to the right side of the Report Footer section directly below the MonthlyFee text box in the Detail section.

c. Within the text box, add an expression to sum the MonthlyFee field for the entire report.

d. Enter Total Monthly: as the caption for the new label in the Report Footer section. If necessary, resize the label to display the complete caption. Save the ProjectList report.

13. With the ProjectList report still open in Design View, format the report as follows to make the information easier to interpret:

a. Right-align the content in the MonthlyFee text box in the Detail section and in the new text box in the Report Footer section.

b. Right-align the right edge of the new text box in the Report Footer section with the MonthlyFee text box in the Detail section.

c. Apply the Currency format to the text box in the Report Footer section. Save and preview the report in Print Preview as shown in Figure 4, and then save and close the ProjectList report.

Figure 4: Final ProjectList Report in Print Preview

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.