Drain Adopter Tandrea Austin manages the Drain Adopter program for the Department of Water and Power in Bellingham, Washington. The program recruits volunteers to regularly monitor and clear storm drains near their homes to ensure the drains are clear and unobstructed when large rainstorms are predicted. The program has been a hit with residents, and has increased the capacity of department staff to deal with other issues that arise during major storms. Tandrea wants to use Access to maintain information about the residents who have signed up for the program, the locations of selected storm drains throughout the city, and the inventory of supplies given to program participants, such as safety vests and gloves. She needs your help in creating this database. Complete the following steps:

1. Create a new, blank database named Career and save it in the folder where you are storing your files, as specified by your instructor.

2. In Datasheet view, begin creating a table. Rename the default ID primary key field to StudentID. Change the data type of the StudentID field to Short Text.

3. Add the following eight fields to the new table in the order shown; all of them are Short Text fields: FirstName, LastName, Address, City, State, Zip, Phone, and Email. Resize the columns as necessary so that the complete field names are displayed.

4. Add a field called BirthDate to the table, which is of the Date/Time data type.

5. Add a final field called Assessment to the table, which is of the Yes/No data type.

6. Save the table as Student and close the table.

7. In Design view, begin creating a second table containing the following three Short Text fields in the following order: SignupID, StudentID, and InstanceID.

8. Make SignupID the primary key and use Primary key as its description. Use Foreign key as the descriptions for the StudentID and InstanceID fields.

9. Add the following two fields to the table in the following order: TotalCost and BalanceDue. Both are of the Currency data type.

10. Add the final field called PaymentPlan to the table. It is of the Yes/No data type.

11. Save the table as Registration and close the table.

12. Use the Navigation Pane to open the Student table.

13. Enter the records shown in Figure 1-40 into the Student table.

14. Jeremiah created a database named Support_AC_1_Records.accdb that contains a MoreStudents table with additional student data. The Student table you created has the same design as the MoreStudents table. Copy all the records from the MoreStudents table in the Support_AC_1_Records.accdb database (located in the Access1 > Case1 folder provided with your Data Files), and then paste them at the end of the Student table in the Career database.

15. Resize all datasheet columns to their best fit, and then save the Student table.

16. Close the Student table, and then use the Navigation Pane to reopen it. Note that the records are displayed in primary key order by the values in the StudentID field.

17. Use the Simple Query Wizard to create a query that includes the StudentID, FirstName, LastName, and Email fields (in that order) from the Student table. Save the query as StudentData, and then close the query.

18. Use the Form Wizard to create a form for the Student table. Include only the StudentID, FirstName, LastName, Phone, and Email fields (in that order) from the Student table on the form and use the Columnar layout. Save the form as StudentInfo, and then close it.

19. Use the Report Wizard to create a report based on the Student table. Include the StudentID, FirstName, and Email fields on the report (in that order), and sort the report by the StudentID field in ascending order. Use a Tabular layout, save the report as StudentList, and then close it.

20. Close the Student table, and then compact and repair the Career database.

21. Close the Career database.

Student table records