Carpenter Family Camp · Open the file , available for download from the SAM website. · Save the file as by changing the “1” to a “2”. o If you do not see the file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. · To complete this Project, you will also need to download and save the following support file from the SAM website: o 1. Carpenter Family Camp operates summer camps for youth as well as families on a lake in Maine situated near the coast. The camp includes activities for families as well as separate activities for adults and children. Optional adventures to nearby attractions are also available. Create a new table in Datasheet View with the following options: a. Rename the default primary key field to , and then change its data type to . ( : The field should remain the primary key.) b. Change the field size of the field to . c. Add a new field with the name and the data type. d. Add another field to the table with the name and the data type. e. Add a fourth field to the table with the name and the data type. f. Save the table using as the name. 2. With the table still open in Datasheet View, change the font in the table to and the font size to 3. With the table still open in Datasheet View, add the records shown in Table 1 below. If necessary, resize the field so that all field values are completely visible. Save and close the table. Table 1: Adventure Table Records 4. Open the table in Design View and make the following changes: a. Change the data type for the field to . b. Specify that the field is a field. c. Save the changes to the table, and then close it. ( : Because you made changes to data types and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.) 5. Open the Relationships window, and then add the table to it. Create a one-to-many relationship between the field in the table and the field in the table. Make the relationship enforce referential integrity and cascade update related fields. Do make the relationship cascade delete related records. Save the relationships, and then close the window. 6. Open the table in Design View and make the following changes: a. Add a new field following the field. Assign the name to the field. b. Set the data type for the field to . c. Set the Caption property, using as the value. d. Enter as the description for the field. e. Save the changes to the table, and then close it. 7. Open the table in Datasheet View, and then find or navigate to the record with the field value 1700012. Change the field value to . 8. With the table still open in Datasheet View, apply a filter by selection to locate all records where the field value equals 1. Change the field value for 101 to . Change the field value for the same record to . Clear all filters. Save and close the table. 9. Open the table in Design View. Use the Lookup Wizard to change the field to a Lookup field. Type in the following four values (in the order shown) as the list of possible values for the field: , , , and . Limit the field values to only the items in the list, and do not allow multiple values for the field. 10. With the table still open in Design View, delete the field. ( : If a message appears concerning deleting an index, continue with the deletion.) Save the table. 11. Switch to viewing the table in Datasheet View, and then change the field value for Susan Kiley (who has a field value of KS01) to . Close the table. 12. Import the data from the file , available for download from the SAM website. Append the records to the table. Do not create a new table, and do not save the import steps. 13. Carpenter Family Camp requires all campers to submit a signed liability waiver from their parents. Create an upda
te query to change the field value to for all records currently in the table. Run the query, and then save it using as the name. ( 21 records will be updated by this query.) Close the query. 14. Use the Simple Query Wizard to create a query based on the table with the following options: a. Include the , , , and fields (in that order). b. Save the query with the name , and then close the query. 15. Create a new query in Design View based on the and tables with the following options: a. Include the field from the table. b. Include the , , and fields (in that order) from the table. c. Sort the records in order based on the field and then by the field. d. Save the query using as the name. e. Run the query, and then close it. 16. Use the Crosstab Query Wizard to create a crosstab based on the table with the following options: a. Use only data from the table in the query. b. Use as the row heading. c. Use as the column heading. d. Use a sum of the field as the calculated value for each row and column intersection in the crosstab query. e. Save the crosstab query using as the name. f. View the query, and then close it. 17. Create a new query in Design View based on the table and the table with the following options: a. Select the field from the table. b. Select the , , , , and fields (in that order) from the table. c. Move the field to the right of the field. d. Add a calculated field after the field with the alias that calculates the sum of the , , and fields. e. Save the query using as the name. f. View the query, confirm that it matches Figure 1 below, and then close it. Figure 1: Session 1 TotalFees Query 18. Open the in Design View, and then add the criteria to select only those records with a field value of . Save and run the query, and then close it. ( This query should return records that meet one or more of the query conditions.) 19. Open the in Design View, and then add the criteria to select only those records with a field value of an field value of . de the field. Save and run the query, and then close it. ( This query should only return records that meet both of the query conditions.) 20. Open the in Design View, and then modify it by adding to the query. For the field, set the Total row to . For the field, set the Total row to . Save and run the query, and then close it. 21. Create a Split Form based on the table. Save the form as , and then close the form. 22. Open the , and then add a new record to the table, using the values shown in Figure 2 below. Close the form. Figure 2: New Record for the Parent Update Form 23. Create the simple report shown in Figure 3 below for the table. Save the report with the name , and then close the report. Figure 3: Adventure Report 24. Use the Report Wizard to create a new report based on the table with the following options: a. Include the , , , , and fields (in that order) from the table. b. Use no additional grouping in the report. c. Sort the report in order by the field. d. Use the layout and orientation for the report. e. Assign the name to the report. f. Preview the report to ensure that it matches Figure 4 below, and then save and close the report. Figure 4: Parent Contact Report 25. Open the in Layout View, and then make the following changes to the report: a. Remove the and columns from the report. b. Add a row that calculates the sum of the values in the column and the sum of the values in the column. If necessary, expand the size of the total control so that it appears completely. c. Change the title of the report using as the name. d. View the in Report View, confirm that it matches Figure 5 below, and then save and close the report. Figure 5: Session 1 Report 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 SAM website to submit your completed project.