14.0: Chapter Scenario
Now it is time to discuss advanced database applications at WorldCorp. As you continue to grow with the company, we will expect you to dive deeper into what is possible with data analysis. Your role will continue to advance as an analyst working with data professionals supporting various teams within WorldCorp.
WorldCorp wants to grow and expand into its independent retail locations to better serve its vast customer base. It still needs to create a database to manage inventory for these new locations. This would involve more complex database design and querying, as well as the use of advanced features in Microsoft Access.
First, we want to determine the data WorldCorp might need to store. We can start by identifying the key elements to track—potential product names, SKU numbers, locations, quantity on hand, and likely reorder points—and designing a table structure to best serve each type of data. For example, you could create a “Products” table with fields for product name, SKU number, description, and unit cost, as well as a “Locations” table with fields for location name and address.
WorldCorp would need a user interface to enter the data for each product and location into their respective tables. We will walk through some advanced steps in Access queries to retrieve specific sets of data from specific tables—for example, a query to find all products with a quantity on hand below the reorder point, or to find all products located in a specific store. You will also learn how to create reports to help keep track of inventory by summarizing and displaying data in a user-friendly format. For example, you could create a report that shows inventory levels by location, or one that shows sales trends by product category.