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

GETTING STARTED

1. Save the file SC_AC365_9B_FirstLastName_1.accdb as SC_AC365_9B_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_9B_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 are performing basic database administration tasks to make sure everyone can use the database efficiently. Users sometimes ask when the database started tracking program representatives. Create a custom database property to record this information as follows:

a. From the Info screen in Backstage View, open the Properties dialog box for the database. (Hint: Click the View and edit database properties link.)

b. Add a custom property with Reps as the property name.

c. Select Date as the type.

d. Enter 11/15/2029 as the value. Confirm that your custom property matches the one shown in Figure 1, and then click OK. (Hint: Your database may contain other properties.)

Figure 1: Custom Tab in the Properties Dialog Box

2. Users prefer to select forms and reports from a Navigation form rather than the Navigation Pane. Set an option as follows to display a Navigation form when the database opens:

a. Open the Access Options dialog box.

b. Change the Display Form option in the Current Database category to display the Main Menu Navigation form as shown in Figure 2.

c. When a Microsoft Access dialog box indicates you must close and reopen the current database for the specified option to take effect, click OK.

Figure 2: Access Options Dialog Box

3. Open the Programs table in Design View to change a property as follows to help users enter data accurately:

a. Create a custom input mask for the ProgramID field so that each field value consists of one uppercase letter and five required numbers. (Hint: Use L00000 as the input mask.)

b. Save the changes to the table and then close it.

4. Open the Representatives table in Design View to modify the Address multiple-field index as follows:

a. Open the Indexes dialog box.

b. Change the sort order for the PostalCode field to Descending. Save the changes to the Representatives table and then close it.

5. Open the Residents table in Design View, and then create a single-field index on the LastName field to speed up record sorting. The index should allow duplicate values. Save the changes to the table design but do not close the table.

6. With the Residents table still open in Design View, create a multiple-field index as follows to improve record retrieval:

a. Use District as the name of the index.

b. Use the Area field, sorted in Ascending order, as the first field in the index.

c. Use the School field, sorted in Ascending order, as the second field in the index. Save the changes to the Residents table and then close it.

7. Open the Programs table in Design View to improve data-entry accuracy as follows:

a. Open the Property Sheet for the table.

b. Create a validation rule requiring that the Budget field value is always greater than or equal to the Donations field value. (Hint: Use [Budget]=[Donations] as the validation rule.)

c. Use Budget amount must be greater than or equal to donations as the validation text. Close the Property Sheet, and then save the changes to the table. (Hint: When asked if you want to test the existing data, click No.)

8. With the Programs table still open in Design View, create a multiple-field index as follows:

a. Use ProgramInfo as the name of the index.

b. Use the ProgramID field, sorted in Ascending order, as the first field in the index.

c. Use the ProgramType field, sorted in Ascending order, as the second field in the index. Save the changes to the Programs table, and then close it.

9. Create a blank form based on the 1 Top application part. (Hint: The blank form will appear in the Navigation Pane as a form object named SingleOneColumnTopLabels and is saved automatically.)

10. Use the Table Analyzer to analyze the Programs table for redundancy as follows:

a. Select the Programs table as the table to analyze and let the wizard decide what fields go in each table.

b. Rename Table 1 as AltPrograms using the Rename table button.

c. Rename Table 2 as AltTypes as shown in Figure 3.

d. Accept the ProgramID field in the AltPrograms table and the Generated Unique ID field in the AltTypes table to uniquely identify records.

e. Do not create a query that resembles the original table.

f. When a message indicates the command or action TileHorizontally isn't available now, click OK, and then close the AltPrograms and AltTypes tables. Confirm the Tables category in the Navigation Pane matches Figure 4.

Figure 3: Table Analyzer Wizard

Figure 4: Navigation Pane with New Tables

11. Customize the Navigation Pane as follows to focus on objects associated with the Residents table:

a. Switch to viewing database items in the Navigation Pane by the Residents category.

b. Add the ResidentDues and ResidentSearch forms to the Resident Forms group.

12. Add a new group to the Residents category as follows to list reports associated with the Residents table:

a. Use Resident Reports as the name for the new group.

b. If necessary, move the Resident Reports group so that it appears between the Resident Forms and the Unassigned Objects groups.

c. In the Navigation Pane, add the ResidentAddresses report to the new group. Confirm that the Navigation Pane matches Figure 5.

Figure 5: Residents Category in the Navigation Pane

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.