New Perspectives Access 365 | Module 4: SAM Project A Lighthouse Cybersecurity
CREATING FORMS AND REPORTS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_4A_FirstLastName_1.accdb as NP_AC365_4A_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_4A_Lighthouse.png
3. With the file NP_AC365_4A_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 project assistant at Lighthouse Cybersecurity, you need to view records for all analysts, clients, and projects and prepare reports for company departments. Open the ClientEntry form in Design View and complete the following tasks to improve the appearance and usefulness of the form:
a. Resize the Paid text box to be the same size as the ClientName text box above it.
b. Move the Close command button so that the left edge of the command button is at the 6" mark on the horizontal ruler.
c. Add a label with the caption Accounting Department to the Form Header at the 3" mark on the horizontal ruler. Save the ClientEntry form, switch to Form View as shown in Figure 1, then close the form.
Figure 1: ClientEntry Form in Form View
2. Open the AnalystEntry form in Design View and then modify it as follows to add a missing field:
a. Add the Salary field to the right of the AnalystID field.
b. Delete the Salary label.
c. Align the bottom edge of the Salary text box with the bottom edge of the AnalystID text box. Save the changes to the AnalystEntry form and then close it.
3. Use the Form Wizard to create a new form based on the Client table with the following options:
a. Include all fields from the Client table in the form.
b. Select the Columnar layout for the form.
c. Enter ClientStatus as the form title.
4. With the ClientStatus form open in Layout View, right-align and bold the text in the five labels. Save the ClientStatus form.
5. Open the ClientStatus form in Form View, and then navigate to the record with the ClientID field value of 8. Change the Balance field value of this record to $35,000.
6. Use the ClientStatus form to add a new record to the Client table with the field values shown in Table 1. (Hint: The Total field is a calculated field.) Save and close the form.
Table 1: New Record for ClientStatus Form
ClientID | ClientName | Paid | Balance 17 | Maya Media | 12,000 | 0
7. Open the AnalystSalary form in Design View and then modify it as follows to add a missing field and make the form more accurate:
a. Delete the Human Resources Department label in the Form Header.
b. Add the StartDate field to the form and then position it just below the Salary label and text box.
c. Align the left edge of the StartDate label with the left edge of the labels above it.
d. Align the left edge of the StartDate text box with the left edge of the text boxes above it.
e. Change the StartDate label to display Start Date as the text. Save and close the AnalystSalary form.
8. Use the Report button to create a report based on the Region table. Save the report using RegionList as the name. (Hint: Do not use the Report Wizard.)
9. Modify the RegionList report in Layout View as follows to improve its appearance:
a. Change the orientation to Landscape.
b. Change the label in the Report Header section to use Region Descriptions as the text.
c. Delete the text boxes displaying the current date and time.
d. Delete the text box displaying the page number since this is a one-page report.
e. Resize the height of the text box that counts the number of records to clearly display 8 as the count, as shown in Figure 2. Save and close the RegionList report.
Figure 2: RegionList Report in Layout View
10. Use the Report Wizard to create a report based on the Project and Analyst tables with the following options:
a. Include the Description field from the Project table and the LastName, StartDate, and ServiceCode fields from the Analyst table in the report.
b. View the data by Analyst.
c. Use Description as the grouping field.
d. Sort the records in alphabetic order by LastName.
e. Select Stepped as the layout and Landscape as the orientation.
f. Use ProjectsAndAnalysts as the report title, and then preview the report. Close the ProjectsAndAnalysts report.
11. Open the AnalystsByProject form in Design View and then modify it as follows to add visual appeal:
a. Apply a Transparent border to the Description label.
b. Change the background color of the Form Header section to Maroon 1 (6th column, 2nd row of the Standard Colors palette).
c. Change the font size of the Lighthouse Cybersecurity Analysts label in the Form Header to 24.
d. Change the width of the Lighthouse Cybersecurity Analysts label so the right edge aligns with the 6" mark on the horizontal ruler.
e. Insert the picture Support_AC365_4A_Lighthouse.png as a logo in the Form Header. Save and close the AnalystsByProject form.
12. Open the SalaryList report in Design View and then update it by applying conditional formatting to the values in the Salary text box so that if the field value is greater than or equal to 80000, the field's font color is set to Dark Red (1st column, 7th row in the Standard Colors palette) and the font is bold. Save and close the SalaryList report.
13. Open the AnalystRegion report in Design View, and then change the font color of the label in the Report Header to Black, Text 1 (2nd column, 1st row in the Theme Colors palette). Save and close the AnalystRegion report.
14. Open the AnalystStartDate form in Design View and then update it as follows to make it easier to read:
a. Resize the width of the FirstName and LastName text boxes to the 6" mark on the horizontal ruler.
b. Resize the width of the form to 6.5".
c. Change the background color of the Analyst Start Date label to Maroon 1 (6th column, 2nd row of the Standard Colors palette).
d. Change the font for the Analyst Start Date label to Cambria. Save and close the AnalystStartDate form.
15. Open the ProjectLandscape report in Layout View, and then apply the Cambria font scheme from the theme fonts to this object only. Save and close the report.
16. Open the AnalystRegion report in Layout View and then modify it as follows to enhance its appearance:
a. Change the orientation to Portrait.
b. Apply a left alignment to the StartDate label and StartDate field values.
c. Move the Region column so that it appears between the AnalystID and LastName columns.
d. Resize the Salary label and Salary field values so they fit just within the right border of the report. Save and close the AnalystRegion report.
17. Open the ClientPayments form in Design View and then modify it as follows to streamline its appearance:
a. Delete the =Date() and =Time() text boxes in the Form Header.
b. Apply the Office theme to this object only. Save and close the ClientPayments form.
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.