You may recall from the initial scenario that several of our requirements involved creating reports that allowed different business units to perform a number of functions. They included:
- The Manufacturing Department should be able to receive a paper report listing inventory shortfalls.
- The Accounting Department should be able to run a daily query listing any orders that shipped during the previous business day for billing purposes.
- The Shipping Department should be able to receive a list of orders that need to be shipped and the Sales Department should update those orders with a tracking number once they are shipped.
Before we can create these reports, we need to build the underlying Access queries that provide the relevant data. We’ll do this one report at a time.
Let’s start with the first report, for the Manufacturing Department. From the Widgets database menu, click the Queries button and double-click on “Create query using Design view.” We’re interested in inventory data, so choose “Inventory” from the Tables menu and click the Add button followed by the Close button. We’d like the query to return a list of all items where the inventory level has fallen below 5 units. In the “Select Query” window, choose “Description” from the Field drop-down box in the first column. Similarly, choose “Quantity” from the Field drop-down box in the second column. We need to limit our list to those items that have an inventory level below 5, so insert the constraint “<5” in the Criteria box for the second column. The manufacturing department will also want to know [u]what[/u] item they need to manufacture, so be sure to include the Description field (with no criteria set). That’s all there is to it! Save your query as “InventoryQuery” and you’re done!
Our second report requires us to show all orders shipped during the previous day. This can be accomplished using the Orders, Customers, Inventory and OrderedItems tables. The Accounting department uses this information to create invoices, so our query should show the quantity of each item included in the order. Create a new query including all four of the necessary tables. Configure the query to show the customer contact information along with the DateShipped from the Orders table, the Quantity from the OrderedItems table and the Description from the Inventory table. Note that the relationships we created earlier will ensure that only relevant records are shown. Once you’ve added all of the relevant information, add the constrant “=Date()-1” to limit the query to orders shipped yesterday. In this case, it would be redundant to display the DateShipped for each record (we know it was yesterday!), so deselect the Show checkbox. The field will still be used as a selection criteria, but it will not show up in the query output. Save the query as “YesterdaysShipmentsQuery”.
After walking through these two queries, you should have the skills you need to build the third one on your own! Join us for the final installment of this series when we use our queries to build professional-looking reports!
