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

GETTING STARTED

1. Save the file SC_AC365_3B_FirstLastName_1.accdb as SC_AC365_3B_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_3B_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 update the company database regularly. You want to improve the table designs and use forms to enter and maintain data. Create a Split Form for the Committees table to provide a form for entering and updating committee data. Save the form using CommitteesSplit as the name. Do not close the form.

2. Switch the CommitteesSplit form to Form View, and then add a record using the values shown in Table 1 to enter data for a new committee. Close the CommitteesSplit form.

Table 1: Values for the New Record

CommitteeID | [AutoNumber field—press TAB] CommitteeType | City CommitteeName | Parking DirectorType | Volunteer Members | 5

3. Open the ResidentsSplit form in Form View to maintain records for Haverhills Neighborhood Association residents. Update resident records as follows:

a. Search for and navigate to the record with the ResidentID field value of 12048 for Cyril Foley.

b. In the Address field, replace 462 S. Poplar Ave. with 662 S. Poplar Ave. as the street address.

c. Search for and navigate to the record with the ResidentID field value of 12015 for Carmen Dorsey, and then delete the record.

d. Close the ResidentsSplit form.

4. The neighborhood association has four types of committees. Open the Committees table in Design View and add a lookup field as follows to make it easy to enter committee types:

a. Use the Lookup Wizard to change the CommitteeType field to a Lookup field. Select the option to type in the values that you want.

b. Enter the values shown in Table 2 in the order shown.

c. Limit the values to only the items in the list, and do not allow multiple values for the field.

d. Save the changes to the table design, but do not close the table.

Table 2: Values for the CommitteeType Lookup Field

City Communications Internal Social

5. Switch the Committees table to Datasheet View and then correct an error by using the lookup list to change the CommitteeType field value to Communications for CommitteeID 11. Close the Committees table.

6. Open the Dues table in Datasheet View, and then add a field to the table to calculate the annual payment amount as follows:

a. Add a new field to the end of the Dues table using the Calculated data type and the Currency format.

b. Use the expression [DuesAmount]*4 to calculate the field values.

c. Assign the name AnnualDues to the field.

d. Save the changes to the Dues table and then close it.

7. The neighborhood association received a grant to fund its traffic programs. The grant provides enough money to increase the budget of each traffic program by $50.00. Update the Programs table as follows to reflect this change:

a. In Query Design View, create an Update query based on the Programs table.

b. Select the ProgramDesc, ProgramType, and Budget fields from the Programs table.

c. Add criteria to select only those records where the ProgramType field values equal Traffic.

d. Update the Budget field using [Budget]+50 as the expression for the new field value.

e. Save the query using UpdatePrograms as the name, run it, and then close it. The query should update five records.

8. The neighborhood association is developing a special program for residents who pay their dues using a credit or debit card. Create a new table for these residents as follows:

a. In Query Design View, create a Make Table query that selects all the fields except the AnnualDues field from the Dues table in the same order that they are listed in the field list.

b. Add criteria to select only those records where the DuesMethod field value is Credit Card or Debit Card.

c. In the Make Table dialog box, assign the name CardDues to the new table.

d. Save the query using CreditDebitDues as the name, run it, and then close it. The new CardDues table should contain 23 records.

9. Committees with no members are obsolete committees that can be removed from the Committees table. Remove the records as follows:

a. In Query Design View, create a Delete query for the Committees table.

b. Select the Members field from the Committees table.

c. Add criteria to delete only those records where the Members field values equal 0.

d. Save the query using DeleteCommittees as the name, run it, and then close it. The query should delete two records.

10. New residents on the east side should be added to the EastSide table. Add the records as follows:

a. In Query Design View, create an Append query for the NewResidents table.

b. Select all the fields from the NewResidents table in the same order that they are listed in the field list. Select all the fields individually for the design grid; do not use the (*) asterisk.

c. Add the criteria "East" to the Area field.

d. Select EastSide as the destination table in the Append dialog box.

e. Save the query using AppendEastSide as the name, run it, and then close it. The query should append three records.

11. Open the Committees table in Design View and then change the Field Size property for the Members field to Integer to save storage space for the field values. Save the changes to the Committees table and then close it.

12. Open the Dues table in Design View to add field properties that improve data-entry accuracy as follows:

a. Add 250 as the Default Value for the DuesAmount field since most dues are for that amount.

b. Enter a Validation Rule for the DuesAmount field to ensure that values in the field are greater than or equal to 100 since $100.00 is the lowest dues amount.

c. Enter the text, Must be 100 or more in the Validation Text property box.

d. Save the changes to the Dues table then close it. (Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)

13. Open the Programs table in Datasheet View. Because residents often ask for a list of neighborhood programs, it should have a professional appearance. Format the datasheet as follows:

a. Display only horizontal gridlines in the datasheet.

b. Change the alternate row color to Green, Accent 6, Lighter 80% (10th column, 2nd row in the Theme Colors palette). Save the changes to the table but do not close it.

14. With the Programs table open in Datasheet View, calculate budget and donation totals as follows:

a. Add a Total row to the datasheet.

b. Calculate the Sum for the Budget field.

c. Calculate the Average for the Donations field. Save the changes to the table and close it.

15. Open the Relationships window and add the Residents and Programs tables to the Relationships window. Create a one-to-many relationship between the ResidentID field in the Residents table and the ResidentID field in the Programs table. Make the relationship enforce referential integrity. Save the relationship.

16. Open the Residents table in Datasheet View and display the Subdatasheet for Tracey Barnes (ResidentID 12011). Update the Donations amount for the Bicycle Safety program $200. Close the Residents table.

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.