Case Exercises

You are a data analyst at WorldCorp. Your supervisor has assigned you the task of creating a comprehensive sales report for the past quarter. The report will provide insights into sales performance, product categories, and regional sales distribution. You are required to use Access to design and customize the report. Follow these steps to complete the task:

• Report setup: Use the Report Wizard to choose the appropriate data source (e.g., the Sales table) and fields for the report. Include fields such as SalesDate, ProductCategory, ProductName, QuantitySold, and TotalSales.
• Customize the report layout: Add the company logo to the Report Header section. Format the logo for an appropriate size and position. In the Page Header section, add the report title, current date, and your name as the report creator.
• Group, sort, and filter the report: Group the report by ProductCategory in the Group Header section. Display the product category name and a brief description. In the Detail section, display product details including product name, quantity sold, and total sales. Add a calculated field to calculate the average price per unit for each product. Apply a filter to show data for sales within the past quarter (e.g., between April 1 and June 30). Calculate and display the total sales for each product category in the Group Footer section. Add a grand total at the end of the report to display the overall total sales for the quarter.
• Formatting and design: Format the text boxes with consistent font style, size, and color. Apply alternating row colors to improve readability in the Detail section. Use appropriate borders and spacing to enhance the visual appeal of the report. Add page numbers to the Page Footer section. Review the entire report in Print Preview to ensure proper formatting and data accuracy. Save the report with a meaningful name.

You have been tasked with enhancing the efficiency of WorldCorp’s database by implementing macros for automating common tasks and enhancing database functions. Your goal is to create macros that streamline inventory tracking and reporting processes.

Task 1: Automating Inventory Tracking

WorldCorp needs a way to automate the process of updating inventory quantities whenever new products are received or sold. Create a macro that performs the following tasks:

1. Open the Inventory table and find the product for which the inventory needs to be updated.
2. Update the inventory quantity based on whether products are received or sold.
3. Save the changes to the Inventory table.

Task 2: Enhancing Inventory Reporting

To improve inventory reporting, WorldCorp wants a macro that generates a report showing low-stock items. Create a macro that does the following:

1. Open the Inventory table and filter products with low stock quantities (e.g., less than 10 units).
2. Create a new report with the filtered data, including product name, current quantity, and reorder information.
3. Save and display the generated report for review.

Task 3: Implementing Custom Message Boxes

WorldCorp would like to enhance the user experience by adding custom message boxes to the database. Create a macro that does the following:

1. Displays a message box when a new product is added to the Products table, asking the user to confirm the addition
2. Provides options to proceed with adding the product or cancel the operation

Task 4: Reviewing Completed Macros

After implementing the macros for automation and enhancement, review and test each macro’s functionality. Open the macros you’ve created and ensure they perform the intended tasks accurately.

Task 5: Reflecting on Visual Basic for Applications (VBA)

Write a short reflection on how VBA could further enhance the automation and functionality of the macros you’ve created. Identify at least two scenarios where incorporating VBA code could provide additional benefits to the macros.