Shelly Cashman Access 365 | Module 4: SAM Project B Haverhills Neighborhood Association

GETTING STARTED

1. Save the file SC_AC365_4B_FirstLastName_1.accdb as SC_AC365_4B_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 SC_AC365_4B_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. Haverhills Neighborhood Association works with the city of Madison, Wisconsin, to improve the neighborhood for residents. As a volunteer with database skills, you need to prepare reports for members and create forms that help in making database updates. Open the ResidentsByArea report in Layout View. Group the report by the Area field, and then sort the report by the ResidentID field in ascending order to make the data easier to interpret. Do not add any other grouping or sorting options to the report. Close the Group, Sort, and Total pane, then save and close the report.

2. Open the ProgramsByType report in Layout View, and modify the report as follows to calculate the total budget and average donation amounts:

a. Sum the values in the Budget column and average the values in the Donations column.

b. Switch to Print Preview to view the report and to check that the values in the subtotal controls and the total controls are not truncated (cut off) vertically.

c. Return to Layout View, and, if necessary, drag the sizing handles for the subtotal control and the total control down to display the complete values. Save the report without closing it.

3. With the ProgramsByType report still open in Layout View, apply conditional formatting to the Donations column to highlight the top donation amounts. If the donation amount is greater than $200, display the value in bold, Dark Red font (1st column, bottom row in the Standard Colors palette). Save the report again, display it in Print Preview, confirm that it matches Figure 1, and then close it.

Figure 1: ProgramsByType Report

4. Open the ResidentDues report in Layout View, and then create a summary report to focus on the average monthly and annual dues. Save and close the report.

5. Open the DuesByArea report in Layout View. Apply the Office theme to this object only so it coordinates with the other reports in the database. Save and close the report.

6. Use the Label Wizard to create mailing labels for the NewResidents table as follows to prepare for sending the new residents an orientation packet:

a. Use Avery C2160 as the label size.

b. Use Arial font, 12 point font size, Normal font weight, and Black (1st column, 6th row of the Basic Colors palette) font color with no special font styles for the labels. (Hint: These formatting options may be the default settings for your label.)

c. On the first line of the label, include the FirstName field, a space, and the LastName field.

d. On the second line of the label, include the Address field.

e. On the third line of the label, include the City field, a comma (,), a space, the State field, a space, and the PostalCode field. Your label should match Figure 2.

f. Sort the labels by the PostalCode field.

g. Save the report as Labels NewResidents (which is the default name). Preview the report, confirm that it matches Figure 3, and then close it.

Figure 2: Prototype NewResidents Label

Figure 3: Labels NewResidents Report

7. Use the Report Wizard as follows to create a report based on the Committees and Residents tables to list neighborhood committees and the resident coordinators:

a. Include the CommName and DirectorType fields from the Committees table.

b. Include the FirstName, LastName, Area, and Phone fields from the Residents table.

c. The data is automatically grouped by the Committees table data, but do not add any other grouping levels.

d. Sort the report by the LastName field in ascending order.

e. Use the Stepped layout and Portrait orientation.

f. Save the report using CommitteeResidents as the report name. Preview the report, confirm that it matches Figure 4, and then close it.

Figure 4: CommitteeResidents Report

8. Open the ResidentBirthDate form in Layout View, and then modify the form as follows to add missing information and reorganize the fields:

a. Select all labels and controls in the Detail section of the form. (Hint: Do not select the form title label in the Form Header section.) Place the selected controls in a Stacked control layout.

b. Add the DateOfBirth control to the end of the form after the Phone control as shown in Figure 5.

c. Move the LastName control after the FirstName control as shown in Figure 5. (Hint: Be sure to select both the label and the control for the LastName field.) Save and close the form.

Figure 5: ResidentBirthDate Form

9. Open the ResidentEntry form in Layout View, and then bold the ResidentID control to draw attention to the label and data. Save and close the form.

10. Open the CommitteesAndResidents form in Layout View, and then add the date to the form to document the current date. Use the 21-Nov-29 format for the date and do not include the time. Save and close the form.

11. Use the Form Wizard to create a form based on the Programs table as follows to provide a form for entering account data:

a. Include the ProgramID, ProgramDesc, ProgramType, Budget, and Donations fields (in that order) on the form.

b. Select the Columnar layout for the form. Save the form using ProgramEntry as the form name. Close the 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.