Query Exam, Section 4:
Ad-hoc Queries

A query is a question you ask of the tables in the database that turns data into information. If there is a pre-determined set of questions that managers will typically ask of the database, then these can be "pre-programmed" in advance and selected from a menu by the manager. In this phase, we are simulating those situations where new or infrequent questions need to be answered. These types of questions are termed ad-hoc inquiries. We will use the query option in Access to find answers to this sort of spur-of-the-moment question.

Using Microsoft Access

Microsoft Access lets you create tables, queries, forms, reports and other objects. When these objects are stored on disk, they are merged into one combined file called a database with an extension of ".MDB" which is short for "Microsoft database." All the examples in the entire Coffee Roasting Project come from the sample database created by Wesley Devonshire while he was a student intern at the Coffee Roasting Company last spring. Wesley named his database: Purchase.mdb. The best way for you to follow this section's examples is to work through the examples on your own local copy of this file. You can obtain a copy of this database file by looking at this Purchase.mdb database file.

Start Windows and run Access.

Figure 1-11. Access Startup Window

The first window you are likely to see in Access is a window that asks whether you want to open an existing database or create a new database from scratch (Figure 1-11). Alternatively, you may bypass this window and begin using an existing database immediately if you launch Microsoft Access by double-clicking on a file with a .MDB extension in Windows Explorer.

One way or another, open Wesley's Purchase.mdb database file.

Figure 1-13. Purchase:Database Window (figure 1-12 is deliberately missing)

The Purchase:Database window displays all the tables in the Purchasing database. Note that all seven tables described in the Purchasing database schema (Figure 1-10) are listed in the window.

The tabs along the side of the Purchase:Database window list all object types that Access can create: Tables, Queries, Forms, Reports, Pages, Macros and Modules.

• Tables hold data in a list-like format
• Queries hold instructions for extracting or summarizing data from tables
• Forms provide customized views of the data in tables and are particularly convenient for entering or viewing transactions
• Reports allow you define how you want data to be summarized and arranged when printed on paper
• Pages let you build web pages that can display or edit data inside the database
• Macros and Modules will not be dealt with in this class. They allow "power users" and programmers to automate various aspects of database management.

Figure 1-14. Purchase:Database Window with Query Tab Selected

Select the Query tab (click with the left mouse button). The Purchase:Database window will display the nine ad-hoc queries in the database (Figure 1-14). All the Ad-Hoc queries in this window correspond directly to the discussion below.

Within Access, a "select query" lets you choose records that meet specified criteria. When the query is executed, the records meeting the criteria are displayed in a datasheet called a dynaset. The dynaset datasheet is dynamic — it will change when the values in the underlying tables change.

For more information on the query operations described in this section, pull down the Help menu and choose "Microsoft Access Help", then select "Create, modify, or view the results of a query".

Ad-Hoc Inquiries to Select Records

Problem 1: Assume that today is April 11, 1995, and that the "flu" has hit the company hard. The supervisor at the loading dock has just called and says she is severely understaffed. She wants to know how many suppliers are scheduled to deliver shipments today.

Analysis: To answer this question, we need information on the desired delivery dates for each of our Purchase Order documents. Referring to the database schema in Figure 1-10, we determine that the query must link the Supplier and PO-Details tables so that it can list supplier names (Sup-Name) and purchase order numbers (PO-Num) for all shipments scheduled for delivery today (Del-Date).

Design: Since the supervisor has asked for an immediate response, we will call her with the information. If she wants a hardcopy report, we can print the resulting "answer" table that the query will create.

Implementation: The process of building any query requires you to complete several steps. First, you must indicate which tables contain the necessary data. Next, you show how the tables should be linked together. Next, you select the fields to appear in the answer. Finally, you enter any processing or analysis rules, such as requesting totals or restricting the query to certain dates.

Figure 1-15. New Query Window

To create a new query:

• Select the Query tab in the Purchase:Database window and click the "New" button.
• Select "Design View" in the New Query window (see Figure 1-15).
• With the Show Table window displayed (See Figure 1-16), select the PO-Details table and click the "Add" button. The table will be displayed in the Select Query window (See Figure 1-17) as it is added. Then select the Supplier table and click the "Add" button. After both tables are added to the Select Query window, click on the "close" button.

Figure 1-16. Add Table Window

Figure 1-17 Select Query Window

• Access should automatically establish the link between the two tables using the "Sup-Code" field. This is shown in Figure 1-17 with a line drawn between the PO-Details and Supplier tables. If for some reason Access didn't establish this link automatically, click on the Sup-Code field in the PO-Details table and while holding down the left mouse button, drag the cursor to the Sup-Code field in the Supplier table.

•  Additional tables may be added to the Select Query window using the "Add Table" icon on the top toolbar.

•  Next you need to choose which fields from the PO-Details and Supplier tables should appear in the query. Use the selector button in the "Field" box to choose fields from the tables. A field may also be added to the query by clicking on its name in the upper part of the Select Query window and dragging it to an open field box or by just double clicking on the field name. Fields will appear in the query's answer in the same order as they appear in the field boxes. For this specific query, we need to select the "Sup-Name" and "PO-Num" fields for inclusion in the query.
• The delivery date (Del-Date) field should also be added to a field box in the query. In Access queries, you can specify specific values, or ranges of values, as the criteria for any field. In this case, we want to limit the query to those specific records where the delivery date is 4/11/95, so we enter 4/11/95 in the criteria box. Access will automatically place number signs (#) around date and time variables, so don't be concerned if the date looks like #4/11/95#.

•  To execute the query, click on the "Run Query" icon on the top toolbar.

Access will generate a datasheet view of the query that contains the requested information (see Figure 1-18). Check to see if the fields you wanted in your query appear in the table.

Figure 1-18. Datasheet View of Ad-Hoc1 Query

•  To modify the query, return to the "Design" view by clicking on the "design" icon on the top toolbar. Modify the query as needed, and re-execute to see the results. When the query is completed, close by either clicking on the close button on the window, or using the "File|Close" command from the menu. As you close the query, Access will let you save and name the query.

Ad-Hoc Inquiries to Perform Calculations

Problem 2: The company has just been notified that one of our suppliers, Vienna Imports Coffee Co, has filed for Chapter 11 bankruptcy. The CEO wants a summary of our business activity with this supplier for the month of April. Specifically, what coffee bean varieties did we buy, and what was the volume (in terms of both quantity and total costs).

Analysis: After looking carefully at the database schema in Figure 1-10, we decide this query will require data from the Supplier, PO-Product, Bean and PO-Details tables. However, since the extended cost (Qty-Ord times Cost-Lb) is not stored in the database, we must ask the query to calculate this result.

Implementation:

• Set up a new query. Add and establish links among the four table objects needed for this query.
• Select the fields to appear in the resulting query (Bean-Code, Bean-Name, Qty-Ord and Sup-Name).
• Since the query will be limited to records for the supplier Vienna Imports Coffee Co., add the field Sup-Code and specify the supplier code "VieImp" as the criteria (since this is a text field, it must be enclosed in quotations). We are not interested in displaying the supplier code in the answer datasheet, so de-select the in the show box. Thus, supplier code is used as a criterion in the query, but will not be included in the results.

Figure 1-19. Select-Query Window that summarizes Quantity Ordered and Extended Cost

• The extended cost must be entered as a calculation. Access allows the use of expressions in both the field and criteria boxes. Field names may be included in an expression as long as they are enclosed in square brackets []. The extended cost field is calculated as the quantity ordered times the cost per pound. The "expression builder" will be used to enter the following expression into a field to compute the extended cost.

Figure 1-20. Expression Builder Window

• With the cursor in an empty column, right click the mouse and select the "Build" option. This will open the Expression Builder window (see Figure 1-20).
• The information needed to compute the extended cost is contained in the PO-Products table. To access these fields, double-click on the "Tables" folder, and then single-click on the "PO-Products" folder. The four fields in this table (PO-Num, Sup-Code, Qty-Ord and Cost-Lb) should be displayed in the middle window.
• Click on the Qty-Ord field, and then click on the "Paste" button. The term "[PO-Products]![Qty-Ord]" should be displayed in the expression window.
• Click on the "*" button under the expression window to add the multiplication sign to the expression.
• Click on the Cost-Lb field and then click on the "Paste" button. The term in the expression window should now be: [PO-Products]![Qty-Ord] * [PO-Products]![Cost-Lb]
• Click on the "ok" button to close the expression builder window

• If we execute the query at this point, our datasheet view will contain seven records; but there are only three different coffee beans. That is, there are three purchase orders to this supplier for the Vienna Mocha coffee bean, and three purchase orders for the Vienna Mocha Decaf coffee bean. To summarize across these three purchase orders, we need to change the "Group By" options on the query.
•  
  • Click on the "Total" icon on the top toolbar. This will display a Total row in the bottom portion of the Select Query window. The default value is to "Group By," which means that separate categories are created for each different value of the data element. Since there is a one-to-one relationship between Bean Code and Bean Name, and Supplier Code and Supplier Name, the resulting query datasheet will be grouped (categorized) by Bean and Supplier.
  • By default, the expression is given the name Expr1: You may click within the expression and change this name to a more descriptive term. For example, replace the "Expr" with "Extended-cost". Make sure you leave the colon between the name and the expression. That is, the revised expression should look like:
         Extended-Cost: [PO-Products]![Qty-Ord]*[PO-Products]![Cost-Lb]
  • For the quantity ordered and extended cost fields, change the Total field to "Sum" — this will summarize the values in these two fields, while the values in the other "Group By" fields will determine the categories in the resulting datasheet.

• If needed, we could limit the inquiry to those purchase order documents created in April. We would add the PO-Date data element as a field and enter the inclusive dates for April (>= 4/1/95 and <= 4/30/95) as a criterion.

The datasheet view created from executing the query depicted in Figure 1-19 should now contain the summarized values for the pounds ordered and the extended cost for all bean varieties ordered from the supplier Vienna Imports Coffee Co. (See Figure 1-21). This query is saved as Ad-Hoc2 in Wesley Devonshire's sample database.

Figure 1-21. Datasheet View of Query in Figure 1-19.

Ad-Hoc Inquiries to Compare Fields

Problem 3: The CEO has also asked whether we were solely dependent on the Vienna Imports Coffee Co. for any of our coffee beans. In other words, can we route our unfilled orders to another existing supplier?

Analysis: Referring to the database schema in Figure 1-10, we can determine which coffee bean varieties we purchase from each supplier by linking the Supplier, PO-Product, and PO-Details tables. We will need to limit the inquiry to those coffee bean codes identified in the query above (saved in the Ad-Hoc2 query). This is accomplished by linking the PO-Product table to the Ad-Hoc2 query on the Bean-Code field.

Implementation:

Figure 1-22. Select Query Window for Ad-Hoc Question 3

• Begin a new query, and add the three tables using the Add Table window. Then, select the "Query" tab at the top of the Show Table window, and add the Ad-Hoc2 query.
• Link the tables as needed. Select supplier name and bean code as fields in the query.
• Press the "Total" icon and "Group By" both supplier name and bean code.

The resulting query in the "Design View" is displayed in Figure 1-22, with the datasheet view in Figure 1-23. Note the bean code "JJ" is also supplied by the Specialties Coffee Co. However, the Vienna Imports Coffee Co. was the sole supplier for the Vienna Mocha and Vienna Mocha Decaf coffee beans. The query is saved as Ad-Hoc3.

Figure 1-23. Datasheet View of Ad-Hoc3 Query

Problem 4: The CEO wants to know how our suppliers are doing on deliveries. Specifically, which suppliers have not been able to meet the promised delivery date, and how many days were they late during the month of April.

Analysis: Referring to the database schema in Figure 1-10, we will need to list each Receiving Report document where the date received is greater than the promised delivery date on the corresponding Purchase Order document. We will list the supplier name, purchase order number, shipment number and receiving report number. We need to calculate the number of days late as the difference between the promised delivery date and the actual date the shipment was received. This requires information from the Supplier, PO-Details and RR-Details tables.

Implementation:

• Select a new query, select the appropriate tables and establish links between the tables.
• Select the Sup-Name, PO-Num, Ship-Num, and RR-Num fields for inclusion in the resulting answer table.
• Calculate the days late as an expression for the difference between the receiving report date and the promised delivery date: [RR-Date] - [Del-Date].
• Specify a selection criteria that the "days late" field must be greater than zero.
• Execute the query.
• The query is saved as Ad-Hoc4.

Figure 1-24. Select Query Window for Ad-Hoc Problem 4

The sample database file, Purchase.mdb, has several queries numbered 5 through 9 just to show you extra examples of how queries are constructed and what they do. For your information, these queries will answer the following questions:

• Ad-Hoc Query #5: "What is the maximum number of days between taking an order (PO-Date) and creating a Payment Voucher (PV-Date)?"
• Ad-Hoc Query #6: "What is the total number of pounds of each variety of coffee bean that has been ordered from each supplier?" Place the list in descending order based on pounds ordered.
• Ad-Hoc Query #7a: "What is the total cost and the total number of pounds ordered for each bean variety?" Place the list in descending order based on total cost.
• Ad-Hoc Query #7b: Use the output from query 7a to calculate, "What is the average cost per pound for each bean variety?" Place the list in descending order.
• Ad-Hoc Query #9: "How many times has each bean variety been ordered?"

This website was created and is maintained by Dave Sullivan.
[College of Business Home Page] [OSU Home Page] [OSU Disclaimer]