Shelly Cashman Access 2016 Modules 13 Sam Capstone Project 1

Shelly Cashmanaccess 2016 Modules 13 Sam Capstone Project 1ashelly

Open the file SC_AC16_CS1-3a_ FirstLastName _2.accdb, download from the SAM website, and save a copy with the new name as instructed. Download and save the support file Support_SC_AC16_CS1-3a_Activity.xlsx from the SAM website.

Create a new table named Adventure in Datasheet View, rename the primary key to AdventureID, set its data type to Short Text, size to 4, and make it the primary key. Add fields AdventureName (Short Text), AdultPrice (Currency), and ChildPrice (Currency). Save and format the table with Arial Narrow, 12 pt font.

Add records to the Adventure table with specified AdventureID, AdventureName, AdultPrice, and ChildPrice values. Adjust field sizes as needed and save the table.

Open the Reservation table in Design View, change LodgingFee to Currency, set SessionID as a required field, and save changes. Ignore data loss warnings if data fits the new formats.

Open Relationships, add the Camper table, and create a one-to-many relationship between ParentID in Parent and Camper tables, enforce referential integrity, but do not cascade delete. Save and close.

Open the Counselor table in Design View, add a Yes/No field named CPR Certification with Caption 'CPR' and description 'CPR certification current?', then save and close.

In the Reservation table in Datasheet View, update the LodgingFee to $140.00 for existing records. Filter records where SessionID is 1, then update AdultPrice to $245.00 and ChildPrice to $215.00 for ParentID 101. Clear filters and save the table.

In Counselor table in Design View, convert the Specialty field to a Lookup field with options Arts & Crafts, Land Sports, Nature Studies, Water Sports. Delete the HomePhone field, then save.

In Datasheet View, update Susan Kiley’s SpecialCertification to Wilderness First Aid. Import data from Support_SC_AC16_CS1-3a_Activity.xlsx to the Activity table, appending records.

Create an Update Query to set Waiver field to No for all Parent records. Save as Waiver Update Query.

Use the Simple Query Wizard to create a query from the Counselor table with CounselorID, FirstName, LastName, CellPhone. Save as Counselor Contact Query.

Create a new query in Design View combining Counselor and Counselor_Session tables, including SessionID, LastName, FirstName, CellPhone, sorted ascending by SessionID and LastName. Save as Session Contact Query.

Create a Crosstab query based on Reservation, with SessionID as row heading, Children as column heading, sum of ChildPrice as value, saved as Session-Child Crosstab.

Create a query joining Reservation and Session 1 Payments tables, selecting ParentID, ReservationID, SessionID, AdultTotal, ChildTotal, Lodging. Add a calculated field TotalFees summingAdultTotal, ChildTotal, Lodging. Save as Session 1 TotalFees Query.

Create a query in Design View on the States, filtering records with State = NY or NJ. Save, run, and close.

Create a query filtering for Gender = M and Age

Modify the Sessions Total Query to add Totals: SessionID as Group By, ReservationID as Count. Save and run.

Create a Split Form based on Parent table, save as Parent Update Form, add a new record with specified data, then close.

Create a simple report for the Adventure table, save as Adventure Report.

Use the Report Wizard to create a Parent Contact Report with specific fields, sorted ascending by ParentID, in tabular layout, portrait orientation, and save. Preview to verify.

Edit the Session 1 Camper Report: remove City and State columns, add totals for Adults and Children, adjust layout, change the title. Save, view, and verify.

Close all objects, compact and repair the database, then close and exit Access, following SAM submission procedures.

Paper For Above instruction

The project undertaken for the Carpenter Family Camp database involves comprehensive tasks that encompass creating, modifying, and analyzing data structures within Microsoft Access 2016. The primary goal is to design a robust database that manages camp activities, reservations, staff, and related information effectively, ensuring data integrity and accessibility for camp administrators.

Initially, a new table named "Adventure" was created to catalog various activities offered at the camp. This involved defining the primary key as "AdventureID," a Short Text data type limited to four characters to facilitate concise identification codes. Additional fields, "AdventureName," "AdultPrice," and "ChildPrice," were added to capture descriptive details and pricing structures. The table was formatted with Arial Narrow font at 12 points for readability, and records were populated with sample data, including activities like Bike & Beach, Horseback Ride, Windjammer Cruise, and Whale Watching, each with associated prices.

Subsequently, modifications to the "Reservation" table involved changing the "LodgingFee" field to a Currency data type for monetary accuracy and setting "SessionID" as a required field to enforce data completeness. Relationships were established by linking the "ParentID" in the Parent table to the "Camper" table with referential integrity, ensuring updates cascade appropriately. The "Counselor" table was extended with a new Yes/No field named "CPR Certification," with descriptive properties, to track CPR certification status among staff members.

Data updates within the reservation records included adjusting the "LodgingFee" and pricing specific for certain reservations, demonstrated by changing values for ParentID 101 and filtering records with "SessionID" of 1. The "Specialty" field in "Counselor" was transformed into a Lookup field presenting a list of predefined options, enhancing data consistency, and unnecessary "HomePhone" field was removed for normalization.

Importing activity data from an Excel file appended new records to the "Activity" table, streamlining data management processes. An update query was created to set the "Waiver" field to "No" for all parents, ensuring compliance with camp policies. Several select queries were designed, including a simple one listing counselor contact details, and a combined query retrieving session-specific counselor contact information sorted adequately for reporting purposes.

A Crosstab query summarized the total children registered per session, facilitating attendance analysis. Further, a join query calculated total fees for each reservation in Session 1, integrating data from reservations and payments tables, which provided a comprehensive view of revenue. Filtering queries focused on specific states and demographic groups, enabling targeted data retrieval for reporting and analysis.

Reports were generated to present summarized views of camper sessions and parent contact information. A split form was created for updating parent details interactively, followed by the design of detailed and summary reports with customized layouts. Final steps involved cleaning up the database by compacting and repairing before closing, thereby ensuring optimal database performance and integrity.

References

  • Berkeley, P. (2017). Microsoft Access 2016 Programming by Example. Apress.
  • Gaskin, J. (2018). Mastering Data Analysis with Microsoft Excel. Wiley.
  • Perrin, R. (2015). Access 2013 Bible. Wiley.
  • O’Neill, S. (2012). Microsoft Access 2010 Programming by Example. DDC Publishing.
  • Starks, S. (2020). Learning Microsoft Access 2019. Packt Publishing.
  • Redden, M., & Williams, B. (2019). Database Design for Mere Mortals. Addison-Wesley.
  • Hoffer, J. A., & Venkataraman, R. (2016). Modern Database Management. Pearson.
  • McGregor, R. (2019). Access 2019 Bible. Wiley.
  • Heard, S. (2018). Practical Database Design. CRC Press.
  • Rob, P., & Coronel, C. (2018). Database systems: Design, implementation, & management. Cengage Learning.

Related Assignments