Chapter AE, Problem AE9.2

It is surprisingly easy to create a market-basket report using table data in Access. To do so, however, you will need to enter SQL expressions into the Access query builder. Here, you can just copy SQL statements or type them in. If you take a database class, you will learn how to code SQL statements like those you will use here.

1. a. Create an Access database with a table named Order_Data having columns Order-Number, ItemName, and Quantity, with data types Number (LongInteger), Short Text (50), and Number (LongInteger), respectively. Define the key as the composite (OrderNumber, ItemName). (You can do this in the table designer by highlighting both columns and clicking the Primary Key icon.)
2. b. Import the data from the Excel file Ch09Ex02_U10e.xlsx into the Order_Data table.
3. c. Now, to perform the market-basket analysis, you will need to enter several SQL statements into Access. To do so, click CREATE/Query Design. Click Close when the Show Table dialog box appears. Right-click in the gray section above the grid in the window. Select SQL View. Enter the following expression exactly as it appears here:

SELECT	T1.ItemName as First Item,
	T2.ItemName as SecondItem
FROM	Order_Data T1, Order_Data T2
WHERE	T1.OrderNumber =
	T2.OrderNumber
AND	T1.ItemName <> T2.ItemName;

  Click the red exclamation point in the toolbar to run the query. Correct any typing mistakes and, once it works, save the query using the name TwoItem Basket.

1. d. Now enter a second SQL statement. Again, click CREATE/Query Design. Click Close when the Show Table dialog box appears. Right-click in the gray section above the grid in the window. Select SQL View. Enter the following expression exactly as it appears here:

SELECT	TwoItemBasket.FirstItem,
	TwoItemBasket.SecondItem,
	Count (*) AS SupportCount
FROM	TwoItemBasket
GROUP BY	TwoItemBasket.FirstItem,
	TwoItemBasket.SecondItem;

Correct any typing mistakes and, once it works, save the query using the name SupportCount.

1. e. Examine the results of the second query and verify that the two query statements have correctly calculated the number of times that two items have appeared together. Explain further calculations you need to make to compute support.
2. f. Explain the calculations you need to make to compute lift. Although you can make those calculations using SQL, you need more SQL knowledge to do it, and we will skip that here.
3. g. Explain, in your own words, what the query in part c seems to be doing. What does the query in part d seem to be doing? Again, you will need to take a database class to learn how to code such expressions, but this exercise should give you a sense of the kinds of calculations that are possible with SQL.