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.

a. Create an Access database with a table named Order_Data having columns OrderNumber, 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.)

b. Import the data from the Excel file CE12Ex01_ E7e into the Order_Data table.

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 Select Query window. Select SQL View. Enter the following expression exactly as it appears here:

SELECT T1.ItemName as FirstItem, 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 TwoItemBasket.

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 Select Query 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.

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.

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.

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.