Illustrated Access 2016 Module 5 Sam Project 1a

Illustratedaccess 2016 Module 5 Sam Project 1aillustratedaccess 201

Modify a database structure in Access 2016 by establishing relationships, creating reports, editing table designs, adding and modifying fields, applying data validation, setting formatting, and attaching media files. The project involves working with the Dive Trips database, updating tables, creating relationships and reports, customizing field properties, and inserting images or attachments to records to meet specific data requirements and improve database functionality.

Paper For Above instruction

The purpose of this project is to develop a comprehensive understanding of database structure modification within Microsoft Access 2016. The tasks encompass creating relationships, editing table structures, customizing data entry and display formats, and enriching the database with attachments, all crucial skills for effective database management and design.

Initially, the project begins with establishing a one-to-many relationship between the Countries and DiveTrips tables. By adding the Countries table to the Relationships window and creating a link using the CountryName and Country fields, the database enforces referential integrity, ensuring data consistency. Once established, the relationship is stored in a relationship report, which is saved as "Relationships," aiding in database documentation and future reference.

Next, we modify the DiveTrips table by removing unnecessary fields—AverageTemp and Climate—from its design view. This streamlines the table structure, focusing on relevant data. Moving the StartDate field between Location and City enhances logical data organization and improves user navigation. Changing the Rating field to a lookup field simplifies data entry by providing predefined options (1-5), limiting user errors. Additionally, attaching a new Photo field with the Attachment data type at the end of the table allows for storing images relevant to each trip, enriching the dataset with visual context.

In datasheet view, the Rating field's lookup list is used to update the rating for a specific record (ID 1) from 4 to 5, reflecting a corrected or updated classification. Attaching the image file Support_IL_AC16_5a_Scuba.jpg to this record further personalizes or documents the trip data, illustrating multimedia integration capabilities in Access.

The next step involves optimizing the Divemasters table. In design view, the Last Name field's caption is customized to improve clarity, and the DiveMasterID field is assigned a descriptive label ("Unique ID number"). Such modifications enhance user understanding of database fields. Switching to datasheet view, the PhoneNumber field is repositioned for better logical flow, and a new AlternatePhone field is added with input masking to ensure a consistent 10-digit format, which standardizes contact data entry.

Further, the DiveTrips table receives formatting and validation enhancements. The StartDate field is set to a custom date format (mm/dd/yyyy) and marked as required, ensuring all records have a valid date in the correct format. The State field is formatted to display entries in uppercase characters, aiding uniformity in data presentation. The Participants field’s data type is adjusted to Single with zero decimal places, a default value of 10 is set, and a validation rule restricts the number of participants to 20 or fewer, with a validation message clarifying the rule. These measures maintain data integrity and user guidance.

Finally, the project involves adding a new field below PhoneNumber in the Divemasters table called AlternatePhone, with a matching input mask to enforce proper phone number formatting. Data entries such as "( " are entered for specific records to demonstrate data editing and entry capabilities. After completing all modifications, the database is compacted and repaired to optimize performance and then closed, ready for submission.

References

• Hutchinson, H. (2018). Microsoft Access 2016: The Most Complete Guide. TechPress.
• Roberson, B. (2017). Mastering Microsoft Access 2016. Sybex.
• Microsoft Corporation. (2016). Access 2016 Quick Start Guide. Microsoft Press.
• Gaskin, A. (2019). Data Management and Design in Access 2016. Packt Publishing.
• Anderson, M. (2020). Professional Access 2016 Programming. Apress.
• McGregor, R. (2017). Building Databases with Microsoft Access 2016. Pearson.
• Gaskin, A. (2021). Effective Database Design with Access. Packt Publishing.
• Microsoft Support. (2023). How to Create Relationships in Access. Microsoft Docs.
• Craig, B. (2019). Access 2016 VBA Programming. Wiley.
• Soni, S. (2022). Enhancing Databases with Multimedia in Access. O'Reilly Media.