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 to 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), Text (50), and Number

(LongInteger), respectively. Define the key as the composite (OrderNumber, ItemName).

b. Import the data from the Excel file Ex32 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 the queries tab and select Create Query in Design view. Click Close when the Show Table dialog box appears. Rightclick in the gray section above the grid in the Select Query window. Select SQL View. Enter the following expression exactly as it appears here:

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 the queries tab and select Create Query in Design view. 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:

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.

Transcribed Image Text:

SELECT T1.ItemName as FirstItem, FROM WHERE T2.ItemName as SecondItem Order Data T1, Order_Data T2 T1.OrderNumber = T2.OrderNumber AND T1.ItemName T2.ItemName;