New Perspectives Access 2016 Module 2 Sam Project 1b 443891

New Perspectivesaccess 2016 Module 2 Sam Project 1bnew Perspectives

Open the file NP_AC16_2b _FirstLastName_ 1.accdb and save it as NP_AC16_2b_ FirstLastName _2.accdb. Download and open support files: Support_NP_AC16_2b_Members.accdb, Support_NP_AC16_2b_Location.txt, and Support_NP_AC16_2b_Billing.xlsx. Verify your name in the grading info table. Open and modify tables as instructed: update the tblTrainer table's structure and data, import data into tables from provided files, create relationships, and enforce referential integrity. Finally, compact, repair, and close the database before submitting per instructions.

Paper For Above instruction

The purpose of this project is to develop a comprehensive Microsoft Access database for the Grand Rapids Memorial Hospital (GRMH) Recreation Centers, facilitating efficient management of trainer, member, billing, and location data. The task involves updating existing tables, importing external data, creating relationships, and maintaining data integrity through referential constraints. This task provides practical experience in database design, data importing, table modification, and relationship management, essential skills for professional database administration and development.

Initially, the process involves opening the provided database file titled NP_AC16_2b _FirstLastName_ 1.accdb and saving it under a new name with a '2' in place of '1'. The candidate must ensure the filename and location are correctly saved to avoid overwriting the original files. The supporting files, including a member database, a location text file, and a billing Excel spreadsheet, are to be downloaded from the SAM website to facilitate data entry and importation.

The first immediate step is to verify the correctness of the grading info table, ensuring the candidate's name is the first record, emphasizing accuracy in database management. The next significant task involves updating the 'tblTrainer' table. This includes switching to Design View to modify field properties, especially setting 'TrainerID' as the primary key, establishing appropriate data types, captions, and formats for each field, which enhances data clarity and consistency.

The candidate then adds a new 'Minors' Yes/No field immediately after the 'Interests' field, configuring its properties to default to 'No'. Subsequently, the 'College' field is to be deleted, requiring careful handling to prevent data loss. Returning to Datasheet View, the interests field is repositioned to improve logical sequencing, and updates are made to indicate trainers accepting minors—specifically Tom Fowler, Andrea Hayward, and Kathy Pearce—by updating the relevant records based on their 'TrainerID'.

Next, a new table, 'tblMember', is created by importing its structure from an external database. The field properties are meticulously set, particularly assigning 'MemberID' as the primary key and defining other fields like 'State', 'Zip', 'Phone', and 'Gender' with appropriate data types and default values. Afterward, sample member records are added directly in Datasheet View, representing typical member data.

The project then focuses on data integration through importing location data from a text file into 'tblLocation' and billing data from an Excel spreadsheet into 'tblBilling'. The imports require choosing correct delimiters, worksheets, and import options to ensure data accuracy and consistency.

Following data population, the relationships between tables are established in the Relationships window. The primary key and foreign key associations are created: one-to-many relationships between 'tblTrainer' and 'tblBilling' via 'TrainerID', and between 'tblMember' and 'tblBilling' via 'MemberID'. Both relationships are configured to enforce referential integrity and to cascade updates, ensuring data consistency and integrity across related tables. The relationship diagram is then saved.

Finally, all open objects are closed, the database is optimized using the Compact and Repair utility, and the database is closed before following the submission instructions on the SAM website to submit the completed project.

References

  • Bird, M., & Wolf, R. (2019). Microsoft Access 2019 Bible. Wiley.
  • Huser, C. (2017). Microsoft Access 2016 Programming by Example with VBA, XML, and ASP. Pearson.
  • Kennedy, M., & Rausch, D. (2018). Mastering Microsoft Access 2016. Pearson.
  • Ober, C., & Feldman, R. (2020). Microsoft Access 2019 Programming. Wiley.
  • Schiffman, S. (2017). Access 2016 Bible. Wiley.
  • Sue, S. (2021). Data Analysis with Microsoft Access. Springer.
  • Walker, J. (2020). Intro to Database Design and SQL Programming. Cengage.
  • Zhou, P. (2018). Building Business Intelligence with Microsoft Access 2016. Packt Publishing.
  • Microsoft Support. (2023). Microsoft Access Support
  • Sam Learning. (2023). SAM Learning Resources

Related Assignments