Lab 8 - Access Queries

1. Lab 8 - Access Queries MBAC 611

2. Lab 8 - Setup In your private network directory create a folder named lab8. Copy your lab7Access file to the lab8folder– a copy is also on Moodle. Rename the copied lab7 Access file to lab8. Double-click on the lab8 file (this should start Access and load the lab8 database)

3. Tables During the last lab we created three tables. The Customer table holds the information about our customers. The Purchase table records individual purchases made by each customer. The Item table is a table containing information about all the items we sell.

4. Table Data – Below is a copy of each of the tables. Note customer 3 purchased two different items on different days

5. Single Table Query We will review how to query a single table. We will ask Access to display all purchases made by customer number 3. Note that this query only involves the Purchase table.

6. Creating the Query Click on the tab and then the icon. Select the table then click the button and then the button.

7. Query Details The tables displayed in the top part of the query form specify the tables that will appear in the “From” clause of the SQL Select statement. First, select Purchase.* in the Field textbox. The asterisk (*) tells Access we want to view all fields in the Purchase table. Then select Purchase in the Table textbox.

8. Next we need to specify the criteria for our query. We are only interested in purchases made by the Customer with Cust_ID=3. Complete the second column of the query form as shown above. To have Access execute the query press the icon located near the top left of the screen. You should see the following output:

9. Cartesian Product In order to query two or more tables we need to perform the join operation. The database will first take the product (also called the cartesian product) of the two tables. Lets take a look at what the product of two tables looks like.

10. Close your previous query by right-clicking on the tab and selecting . You do not need to save this query. We are now going to create a new query. Click on the tab and then the icon. If we were to write a query asking for the Customer ID of all customers who purchased a pencil we would need both the Purchase and Item tables. Lets see what the cartesian product of the Purchase and Item table looks like.

11. From the Show Table dialog box double-click on and then double-click on (note double clicking is equivalent to selecting the table then clicking the button). Close the Show Table dialog box by clicking the button.

12. The two tables we are going to use appear at the top of the Query form. Change the Query detail columns as shown below. Note that we wish to view all fields from both tables involved in the product operation – hence the asterisk is specified in both columns. To have Access execute the query press the icon located near the top left of the screen.

13. Below is the cartesian product of the Product and Item table. Note that the table has eight fields – 4 fields from each table From the table From the table

14. Note that there are two Item_ID fields. One belonging to the Purchase table and other belonging to the Item table. Each Item_ID field is uniquely identified – a table name and period precede the Item_ID field.

15. The product has 16 records – 4 records from the Purchase table combined with 4 records from the Item table. 4*4=16

16. The cartesian product is formed by combining every row of the Purchase table with every row of the Item Table.

17. You can see that every row of the Purchase table is combined with every row of the Item table. However, not all of these combinations make sense.

18. In the cartesian product the only rows that make sense are ones that match on Item_ID – the field that links the two tables. So we need to inform Access that only records matching on Item_ID are valid.

19. Join Operation If we were to write a query asking for the Customer ID of all customers who purchased a pencil we would need both the Purchase and Item tables. Item_ID is the linking field of the two tables. The join operation combines the cartesian product and matching on linking field.

20. Close the current query – you don’t need to save it. Click on the tab and then the icon. From the Show Table dialog box double-click on and then double-click on (note double clicking is equivalent to selecting the table then clicking the button).

21. Close the Show Table dialog box by clicking the button. Enter the values into the Query Form as shown below. Note that after you finish typing the table name in the Criteria textbox of the second column you will be able to select the Item_ID field from a pull-down menu. You don’t need to enter the bracket [ ] characters. Access will add them. This query says that you want to display the Cust_ID field and you want Purchase.Item_ID to match Item.Item_ID and Item_Name should be equal to “Pencil”.

22. To have Access execute the query press the icon located near the top left of the screen. The result is a single record with a single column: This query would be much more informative if we added the Customer First and Last Name. Lets modify the query to view the Customer First and Last Name. Right-click on the queries’ tab and select

23. We need to add the Customer Table. Click on the icon. Select the Customer table and then close the Show Table dialog box. Near the top of the query form you should see all three tables.

24. Complete the query form as below. Note that the first three columns have NOT changed. Execute the query. The result should be the following: Save and name this query by right-clicking on the queries’ tab and selecting . Name the query Q1.

25. Examining The SQL Code Right click on the tab and select You should see the SQL Code: SELECT Purchase.Cust_ID, Customer.First_Name, Customer.Last_Name FROM Purchase, Item, Customer WHERE (((Purchase.Item_ID)=[Item].[Item_ID]) AND ((Item.Item_Name)="Pencil") AND ((Customer.Cust_ID)=[Purchase].[Cust_ID])); Note the matching of both Cust_ID and Item_ID. Close the query.

26. Question 2 Create an Access query that displays the first name, last name and purchase date for all customers who made a purchase on or after 3/9/2005. Note that you do not need to place # symbols around the date as Access will do this for you. Name this query Q2. The query result is

27. Question 3 Write a query to list the Purchase_Date, Item_Name and Price of all purchases made on or after 8/4/2003. Name this query Q3. The query result is: (note: your field order may differ)

28. Question 4 Write a query to list the Item_Name,Item_IDand Num_Left of all items that have a Num_Left value of less than 5. Name the Query Q4. The query result is:

29. Lab Submission Save your Access file. Quit Access – you can’t submit a Access file that is open. Submit your lab8Access file to the lab8 Moodle Assignment.