Shelly Cashman Access 365 | Module 1: SAM Project B Haverhills Neighborhood Association
Creating and Modifying Tables and Other Database Objects
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_1B_FirstLastName_1.accdb as SC_AC365_1B_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_1B_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 is a group of people who reside in a neighborhood near downtown Madison, Wisconsin, and work with the city to improve the area for residents. As a volunteer, you've decided to create an Access database to better manage information on residents and programs. Create a new table in Datasheet View with the following options:
a. Rename the default primary key ID field as CommitteeID and keep it as the primary key with the AutoNumber data type.
b. Add a new field with the name CommitteeName and the Short Text data type.
c. Add a new field with the name Members and the Number data type. Save the table using Committees as the name.
2. With the Committees table still open in Datasheet View, add the record shown in Table 1. Resize the columns in the datasheet to their best fit, and then save and close the Committees table.
Table 1: Record for Committees Table
CommitteeID | CommitteeName | Members [AutoNumber] | Newsletter | 12
3. Create a new table in Design View to store the funding data for the neighborhood association:
a. Add a field with the name AccountNumber and the Short Text data type.
b. Set AccountNumber as the table's primary key.
c. Change the field size of the AccountNumber field to 6. Save the table with the name Accounts but do not close it.
4. With the Accounts table still open in Design View, add a second field to the table with the field name AgencyID and the Number data type. Save the table and then close it.
5. Open the Residents table in Design View and change the name of the field named First to FirstName and use LastName to change the name of the field named Last.
6. With the Residents table still open in Design View, change the Data Type property for the ResidentID field from Number to Short Text.
7. In Design View, add a new field to the Residents table after the Phone field. The field should be named DateOfBirth and have the Date/Time data type. Save the Residents table.
8. Switch the Residents table to Datasheet View, navigate to the fifth record (which has a ResidentID field value of 12005), and delete the record.
9. With the Residents table still open in Datasheet View, navigate to the third record (which has a ResidentID field value of 12003) and change the value to Singer for the LastName field. Close the Residents table.
10. You also need to view individual resident records. Use the Form tool to create a form for the Residents table. Save the form with the name Resident Data Entry and confirm that the form matches Figure 1. Close the form.
Figure 1: Resident Data Entry Form in Form View
11. Use the Simple Query Wizard to create a query based on the EastSide table with the following options:
a. Include all fields from the EastSide table in the query. Save the query with the name EastSide Query and then close the query.
12. You need to refer to a printed copy of the Programs table when assigning volunteers to programs. Use the Report Wizard to create a report based on the Programs table with the following options:
a. Include all fields from the Programs table in the report.
b. Do not include any grouping levels.
c. Sort the report by Budget in Ascending order.
d. Select Tabular as the layout of the report and Portrait as the orientation of the report. Save the report using Program Report as the name. Confirm that the report matches Figure 2 and then close the report.
Figure 2: Program Report in Print Preview
13. In the Navigation Pane, rename the EastSide form using East Side Data Entry as the new name.
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.