Create Two Tables. Input Records. Establish A Relationship ✓ Solved

Create Two Tables. Input records. Establish a relationship

Create two tables in MS Access. Input 10 records for each table. Establish a relationship between the tables and create reports.

Paper For Above Instructions

Creating a relational database using MS Access is a fundamental skill in the field of data management. In this lab, we will create two tables, input 10 records for each, establish a relationship between them, and create various reports. This step-by-step guide will help in understanding the underlying processes and management of data within MS Access.

Creating the First Table: Client Information

The first table to be created is named "Client Information". The following fields will be included, along with their data types, field sizes, and comments:

  • First: Short Text, Field Size: 25, Format Option: Add >
  • Last: Short Text, Field Size: 25, Format Option: Add >
  • State: Short Text, Field Size: 2, Format Option: Add >
  • Phone: Short Text, No changes, Input Option: Do not change
  • SSN: Short Text, No changes, Input Option: Do not change

Once the table structure is defined, we need to input 10 records. Since this is a practice exercise, fictitious data can be generated. Here’s an example of the data to be used:

  • First: John, Last: Doe, State: NY, Phone: 555-1234, SSN: 123-45-6789
  • First: Jane, Last: Smith, State: CA, Phone: 555-2345, SSN: 987-65-4321
  • First: Jim, Last: Brown, State: TX, Phone: 555-3456, SSN: 456-78-9012
  • First: Emily, Last: White, State: FL, Phone: 555-4567, SSN: 678-90-1234
  • First: Michael, Last: Johnson, State: IL, Phone: 555-5678, SSN: 890-12-3456
  • First: Sarah, Last: Davis, State: WA, Phone: 555-6789, SSN: 234-56-7890
  • First: Chris, Last: Taylor, State: OR, Phone: 555-7890, SSN: 345-67-8901
  • First: Jessica, Last: Wilson, State: NV, Phone: 555-8901, SSN: 456-78-9012
  • First: David, Last: Martinez, State: CO, Phone: 555-9012, SSN: 567-89-0123
  • First: Laura, Last: Garcia, State: AZ, Phone: 555-0123, SSN: 678-90-1234

Creating the Second Table: Client Amount

The second table, named "Client Amount", will consist of the following field:

  • Amount: Currency, Input Value Range: From $100 to $5,000

Similar to the first table, this table will also contain 10 records, which could look like this:

  • Amount: $250
  • Amount: $500
  • Amount: $750
  • Amount: $1000
  • Amount: $1250
  • Amount: $1500
  • Amount: $1750
  • Amount: $2000
  • Amount: $2250
  • Amount: $2500

Establishing Relationships

To establish a relationship between the two tables, the ID field, which serves as the primary key in both tables, will be used. This relationship enables database integrity and ensures that data is connected appropriately across tables.

In MS Access, this can be done by going to the 'Database Tools' tab and selecting 'Relationships'. From there, we can drag the ID field from the "Client Information" table to the ID field in the "Client Amount" table. This action creates a one-to-many relationship, where one client can have multiple amounts associated with them.

Creating Reports

Once the tables have been created and the relationship established, the next step is to create reports. The reports will highlight different aspects of the client data:

  • Report 1: Title: Client Information – [Your Name]. Fields: SSN, First, Last, Phone.
  • Report 2: Title: Client General Information – [Your Name]. Fields: SSN, Phone, First, Last, State, Amount.
  • Report 3: Title: Client Data – [Your Name]. Fields: All relevant client data from both tables.

To create the reports, navigate to the 'Create' tab in MS Access and choose 'Report Wizard'. Follow the prompts to select the desired fields and customize the layout of the report. Ensure to modify the report titles as specified.

Conclusion

This lab exercise demonstrates the essential skills of creating tables, establishing relationships, and generating reports in MS Access. By practicing these steps, one can efficiently manage and analyze data to support various business needs.

References

  • Microsoft. (2020). Get Started with Microsoft Access. Retrieved from https://support.microsoft.com/en-us/access
  • Rouse, M. (2021). What is a Database Management System (DBMS)? Retrieved from https://www.techtarget.com/whatis/definition/database-management-system-DBMS
  • Rob, P., & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Almeida, J. P. (2019). MS Access Database for Beginners. CreateSpace Independent Publishing Platform.
  • Gilbert, S. (2018). A Beginner's Guide to Microsoft Access. BookBoom.
  • Adar, E. (2019). Understanding Relationships in MS Access. Microsoft Press.
  • Cohen, D. (2020). Building Reports in MS Access. Wiley.
  • Kumar, K. (2017). Database Management System with MS Access. Springer.
  • Chapple, M. (2021). Microsoft Access Tutorials for Beginners. DataCamp.
  • O'Reilly, T. (2019). MS Access Essentials: A Practical Approach. O'Reilly Media.

Related Assignments