Enhanced Microsoft Access 2016 Chapter 6 Lab Test A ✓ Solved

Enhanced Microsoft Access 2016 Chapter 6 – Lab Test A

To demonstrate the ability to utilize special Access features in the creation of reports.

Problem: You have been asked to add tables to the HaveMore Electronics database as well as create some advanced reports using the new data. Instructions: Open the Access_Chapter_6_-_Lab_Test_A_-_HaveMore_Electronics database file provided in a location specified by your instructor and execute the following tasks:

  1. Create a table to contain inventory information for the outlets. Use the structure illustrated in Figure A6A – 1. Add the data shown in Figure A6A – 2 to the Inventory table.
  2. Create a table to contain inventory items information for the outlets. Use the structure illustrated in Figure A6A – 3. Add the data shown in Figure A6A – 4 to the Items table.
  3. Add the Inventory table to the Relationships window and establish a one-to-many relationship between the Outlets and the Inventory tables. Print the Relationships window.
  4. Add the Items table to the Relationships window and establish a one-to-many relationship between the Inventory and the Items tables.
  5. Create a query called Outlets and Sales Rep that joins the Outlets and Sales Rep tables. The query should display all fields in the Outlets table and the Sales Rep table except for the Outlet ID from the Sales Rep table.
  6. Create a query called Inventory and Items that joins the Inventory and Items tables. The query should display all fields in the Inventory table and the Items table except for the Item ID from the Items table.
  7. Create the report shown in Figure A6A – 5. The report uses the Outlets and Sales Rep query as the basis for the main report and the Inventory and Items query as the basis for the subreport. Use the name Outlet Master Report for the report. The report title has a Text Align property value of Distribute. The Border Width property is hairline and the subreport label is Inventory.
  8. Have the Outline ID Header repeat on each page. In the footer, include the page number on the left and the current date on the right.
  9. Print the report.
  10. Close the database. Submit the results according to the specifications provided by your instructor.

Field Name Data Type Field Size Primary Key Description Outlet ID Short Text 6 Yes Outlet ID Item ID Short Text 5 Yes Item ID Qty on Hand Number Number of items on hand Date Updated Date/Time Date inventory of item was last updated Figure A6A – 1, Structure of Inventory table Outlet ID Item ID Qty on Hand Date Updated HME 11 T/1/2015 HME 15 L/12/2015 HME 17 M/1/2015 HME 26 P/29/2015 HME 11 Y/8/2015 HME 15 U/15/2015 HME 17 H/25/2015 HME 26 H/30/2015 HME 11 T/1/2015 HME 15 P/22/2015 HME 17 U/4/2015 HME 26 Y/12/2015 HME 11 I/5/2015 HME 17 N/5/2015 HME 26 B/15/2015 Figure A6A – 2, Data for Inventory table Field Name Data Type Field Size Primary Key Description Item ID Short Text 5 Yes Item ID Item Descr Short Text 100 Item description Price Currency Cost of an item Figure A6A – 3, Structure of Items table Item ID Item Descr Price T1587 BlueRay DVD 269 L2458 DVD RW 135 M9899 External Hard Drive 179 P2933 HD Video 248 Y9390 LaserJet LJ U9893 Maximun CPU 267 H4541 Palm Reader H2250 Ultimate Keyboard 204 T8345 WiFi Router 169 P4320 HD LCD 147 U1165 Big Audio - Sound Kit 325 Y2899 LGM Pad 699 I9809 TKB Laptop 875 N9300 LR Car Stereo 451 B9839 OnCue TV 689 Figure A6A – 4, Data for Items table Figure A6A – 5 © 2016 Cengage Learning®.

Paper For Above Instructions

The purpose of this lab test is to demonstrate proficiency in accessing advanced reporting techniques utilizing Microsoft Access, specifically focused on the HaveMore Electronics database. This exercise is aimed at enhancing your database management capabilities, emphasizing the importance of database organization, relationship establishment, and effective report creation.

Creating the Inventory Table

Begin by launching the Access_Chapter_6_-_Lab_Test_A_-_HaveMore_Electronics database file. The first step is to create an Inventory table to store outlet inventory information. According to Figure A6A - 1, the table requires the following fields: Outlet ID, Item ID, Qty on Hand, and Date Updated. Ensure that Outlet ID and Item ID are set as primary keys with appropriate data types.

Once the structure is in place, populate the Inventory table with data as presented in Figure A6A – 2. This will build a foundational layer for future queries and reports.

Establishing Relationships

After populating the Inventory table, the next step is to create another table that contains inventory items information, as specified in Figure A6A – 3. This Items table must include fields for Item ID, Item Descr, and Price. After populating the Items table with the corresponding data (refer to Figure A6A – 4), navigate to the Relationships window.

In the Relationships window, add both the Inventory table and the Items table. Establish a one-to-many relationship between the Outlets and Inventory tables, followed by a one-to-many relationship between Inventory and Items. It is essential to print the Relationships window as documentation of these connections.

Creating Queries

Queries provide a dynamic way to analyze and retrieve data from the database. Create a query titled Outlets and Sales Rep that merges data from the Outlets and Sales Rep tables. This query should display all fields from the Outlets table and Sales Rep table, excluding the Outlet ID from the Sales Rep table. Similarly, create another query named Inventory and Items that accepts information from both the Inventory table and the Items table, withholding the Item ID from the Items table.

Report Creation

Proceed to create a report based on the queries generated. The report, titled Outlet Master Report, will utilize the Outlets and Sales Rep query for the main body and the Inventory and Items query for its subreport. Additionally, adjust the report properties by setting the Text Align property to Distribute and ensuring that the Border Width is set to hairline. Label the subreport as Inventory.

Moreover, modify the report header to repeat the Outline ID Header on each page and add the current date and page number in the footer.

Final Steps

After finalizing the report, print it, ensuring that it adheres to all previously specified formatting requirements. Finally, close the database as per your instructor's directives. Proper documentation and adherence to these steps foster a thorough understanding of effective database management.

Conclusion

This lab exercise underscores the fundamental capabilities of Microsoft Access in handling multiple tables, establishing relationships, executing complex queries, and generating precise reports. Mastering these skills is vital for individuals aiming to excel in data management fields, ultimately optimizing business operations.

References

  • Cengage Learning. (2016). Enhanced Microsoft Access 2016 Chapter 6 – Lab Test A.
  • Cengage Learning. (2016). Introduction to Database Systems. Silver Springs, MD.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Addison-Wesley.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management. Pearson.
  • Kroenke, D. M., & Auer, D. J. (2016). Database Concepts. Pearson.
  • Rob, P., & Coronel, C. (2017). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2011). Database System Concepts. McGraw-Hill.
  • Vernadat, F. (2017). Enterprise Modeling and Ontology for Information Systems. Springer.
  • W3Schools. (2023). Microsoft Access Tutorial. Retrieved from https://www.w3schools.com/access/
  • Zhou, J., & Wu, Z. (2016). Database Management Systems. Wiley.

Related Assignments