File=west5.htm; updated 4/4/98 West Overshoe Municipal Library-Building Queries Project #2

This lab will provide you with experience building the many different kind of queries that Microsoft Access supports. Queries are the foundation for extracting and storing data in large sets from database tables. Queries can also be used to aggregate data and to update records in database tables.

NOTE: When the assignment says "Based on the xxxx query" or "Create a query like", start by making a copy of the xxxx query. Do this by: (1) highlighting the query name, (2) selecting Copy from the Edit pull-down, (3) then select Paste form the Edit pull-down. You will be then be prompted for the name of the new query.

Exercise 1. Creating Select Queries.
In this exercise you will create a query to display all available titles in the Library database in alphabetical order. Open the LAB05P1.MDB file in Access.

1. Create a query (using Design View) that returns all available titles in ascending alphabetical order from A to Z. Use the TITLE table to supply the data for the query. Save the query with the name TITLES SORTED. How many titles were returned (should be the entire table.)

2. Based on the TITLES SORTED query, create a new query that returns a range of records, that is, all titles between and including "C" through "J." How many titles were returned? Save the query with the name TITLES SORTED RANGE.

3. Based on the TITLES SORTED RANGE query, create a new query that selects data based on pattern matching criteria. Change the criteria for the query so that all book titles that include the word "database" are returned (Hint: Use the Like operator). How many titles are returned? Save the query with the name TITLES SORTED MATCH.

4. Based on the TITLES SORTED MATCH query, create a new query using the "And" ability of the query generator. Change the criteria for the query so that all book titles that include the words "database" and "design" are returned. How many titles are returned? Save the query with the name TITLES SORTED AND.

5. Based on the TITLES SORTED AND query, create a new query by changing the criteria so that all book titles that include the word "database" or that start with the letter "P" are returned. How many titles are returned? Save the query with the name TITLES SORTED OR.

Exercise 2. Creating Multiple-Table Queries.
In this exercise you will create queries that extract records from multiple tables. You will also create different types of joins to distinguish how each type of join processes data. First you will use test data to create a query and see the results, then you will create queries using the Library data.

1. View the data in the Test_Join_1 and Test_Join_2 tables. Write down the number of records and the data contained in Field_1 in the Test_Join_1 table and Field_2 in the Test_Join_2 table.

In the Database Queries window choose the New button, then choose Design View. When the Show Table dialog box appears select the Test_Join_1 table and use the Add button to add it to the query, then do the same for the Test_Join_2 table and choose the Close button.

Add the Field_1 field from Test_Join_1 and Field_2 from Test_Join_2 to the QBE grid. Create a join between Field_1 and Field_2. Do this by highlighting Field_1 and click and drag it to Field_2. A line appears between the two tables to connect the join fields.

Double-click the join line and the Join Properties box appears. Set the Join Property to 1 (this is an equi-join and should be the default setting) and choose OK. Switch to the Datasheet view and preview the results. How many matching records are returned?

Return to Design view, Double-Click the join line and set the Join Property to 2 (this is a left outer join), then choose OK. Again switch to Datasheet view and preview the results. How many records are returned? Why do all the records from Field_1 appear in the results?

Return to Design view, Double-Click the join line and set the Join Property to 3 (this is a right outer join), then choose OK. Switch to Datasheet view and preview the results. How may records are returned? Why do all the records from Field_2 appear in the results?

Save the query with the name TEST JOIN.

2. Create a multiple-table query that shows all the members who have books out on loan. This requires using the Loan table and the Member table. Add the Member_No, Lastname, Firstname, and Middleinitial fields in the Member table to the QBE grid. Save the query with the name MEMBERS WITH LOANS.

3. Create a query like the MEMBERS WITH LOANS query that will return all the books that are out on loan along with the member’s name who has checked out each book. Add the ISBN and Title_No fields from the Loan table to the first two columns in the QBE grid. Set the join to an equi-join (click on the join line to get the Join Properties dialog box). How many records are returned? Save the query with the name BOOK LIST EQUI.

4. Create a query like the BOOK LIST EQUI, only change the join to a right outer join (i.e., all records from Loan and matching records from Member). How many records are returned? When you scroll through the results, is there information in every field for every record? Why or why not? Save the query with the name BOOK LIST RIGHT.

5. Create a query like the BOOK LIST RIGHT, only change the join to a left outer join. How many records are returned? When you scroll through the results, is there information in every field for every record? Why or why not? Save the query with the name BOOK LIST LEFT.

6. This next query requires you to modify the BOOK LIST LEFT so that only the names of members who do not have books on loan are returned in the dynaset. Do this by adding the "is null" criteria to the ISBN field (note: do not put in the quotes when you type in the ISBN field criteria). Further modify the query so that empty fields are not displayed in the output by deselecting the Show row in the ISBN and Title_No fields. Save the query with the name MEMBERS WITHOUT LOANS.

7. This next query creates a query with a cartesian product (no join). Starting with the MEMBERS WITHOUT LOANS query, delete the join line that connects the two tables by clicking on it and pressing DELETE. Delete the "is null" criteria and include ISBN in the output. How many records are returned? Why? Save the query with the name CARTESIAN PRODUCT.

Exercise 3. Creating a Top Value Query.
In this exercise you will create a top value query to display the 10 members with the greatest number of books checked out. This type of query would be useful, for example, to display top salespeople or top-selling products in another type of information system.

Use the Member and Loanhist tables for the query. From the Member table add the Member_No and Lastname fields to the QBE grid. Add the Title_No from the Loanhist table. Create a Totals query (do this by clicking Totals on the View pull-down menu). Change the Total row entry for Title_No to Count and sort it in descending order. Change the join type to return all records from the Loanhist table. How many records are returned? Return to design view and access the Query Properties dialog box (by double-clicking the right mouse button). Type "10" in the Top Values box of the Query Properties dialog box (note: do not type the quotes - just the value 10). View the result of the query. How many records are returned? Why? Save the query with the name TOP 10 LOANERS.

Close the LAB05P1.MDB database.

Exercise 4. Creating Action Queries.
In this exercise you will append and update action queries to populate several tables with data. You will also create a delete query. Open the LAB05P2.MDB file (note: LAB05P2 differs from LAB05P1 in that queries not pertinent to the case study have been deleted; the Title and Item tables are the only tables populated with data).

1. Creating an Append Query. You will populate the Copy table with data by creating an append query that appends information from the Copy_Fox table. Note that this information was previously imported from a FoxPro database in a previous exercise. In this case, the field names and data types are the same in both the Copy_Fox and the Copy tables. Start a new query and add table Copy_Fox. Select all fields in the Copy_Fox table and drag them to the QBE grid. Choose Append Query from the Query menu. In the Table Name box, select Copy, then choose the OK button. Note that the fields in the Append To: row are filled in. Choose the Run button on the toolbar (the ! icon) and then choose OK. Close the query and DO NOT save it. You may confirm it works by verifying the data (450 rows) are in the Copy table. Delete the Copy_Fox table.

2. Appending a field to a field with a different name in another table. You will populate the Member table with data by creating an append query that appends information from the Memdata table. In this case, the field names are different in the Member and the Memdata tables.

Create a new query based on the Memdata table. Add the Employee ID, Last Name, and First Name fields to the QBE grid. From the Query menu, choose Append Query. In the Table Name box select Member and choose OK. In the Append To row of the Employee ID field, select Member_No from the Append To list. In the Append To row of the Last Name field, select Lastname from the Append To list. In the Append To row of the First Name field, select Firstname from the Append To list. Run the query. A total of 15 rows should be appended. Close the query and do not save it. You may confirm it works by verifying the data are in the Member table.

3. Appending an OLE field. You will populate the Adult table with data by creating an append query that appends information from the Memdata table. You will also append the photo field (which is an OLE field) from the Employees table in the Northwind database. The Northwind database is a sample database provided with all copies of Microsoft Access. It is located under the SAMPLES subdirectory under OFFICE. OFFICE may be in a separate subdirectory or it may be under the MICROSOFT OFFICE subdirectory.  In addition, the MICROSOFT OFFICE subdirectory may be found under your PROGRAM FILES directory, for example: /Program Files/Microsoft Office/Office/Samples/Northwind

If, when you open the LAB05P2.MDB and examine the Employees table, there is no data found or you receive an error message, it will be necessary for you to "attach" to the Employees table. Do not copy the Employees table to LAB05P2.MDB. Attach to the Employees table by first selecting Employees in LAB05P2.MDB and pressing the delete key to terminate the existing attachment and answer yes to the popup dialogue box. Next access the File menu option and select the Get External Data option, then select Link Tables. From the popup box select Microsoft Access as the file type since you are attaching this database to another Microsoft Access database. Now locate the proper subdirectory with the NORTHWIND.MDB (Northwind database) as described in the paragraph above. Select the Employees table and click the OK button. You will now note that the Employees table is listed in the LAB05P2.MDB as if it were actually part of the database. There is a heavy (bold) arrow immediately to the left of the Employee table name.

Create a new query based on the Memdata and Employees tables. Select the seven fields from Memdata identified below and drag them to the Field row of the QBE grid. Add the Photo field from the Employees table of the Northwind database. From the Query menu choose Append Query. In the Table Name box select Adult and then choose OK. Select the appropriate cells in the Append To row to append data from Memdata and Employees to the Adult table. Append the field names as follows:
 
 

 
Create a join between the Employee ID field in both tables. Run the query. Do not save the query. You may view the data in the Adult table to confirm the query processed correctly.

Exercise 5. Creating an Update Query.
This new query will include a calculated field used to change the expiration date for all members to 1/15/99. Create the query based on the Adult table. Add the Expr_Date to the QBE field. Change the query from a Select to an Update query (by choosing Update Query from the Query menu). In the Update To row, type in the new date: 1/15/99. You may wish to view the data prior to and after you execute the query. Save the query with the name UPDATE ADULT EXPR.

Exercise 6. Creating a Parameter Query.
This is one of the most important types of queries because it enables you to select specific individual records from a table for updating and/or modification. Create a new query based on the Member and Adult tables. From the Member table add the Member_No, Lastname, and Firstname fields to the QBE grid. From the Adult table add the Phone_No field to the QBE grid. In the Member_No cell of the Criteria row type the following: [Enter The Member Number:]

Run the query and enter the member number of 7. What happens? Save the query with the name GET MEMBER.

Exercise 7. Creating a Crosstab Query.
This type of query is used to aggregate data that may be used for reports. Create a new query based on the Item table (without using the Crosstab Query Wizard. Add the ISBN, Language, and Cover fields to the QBE grid. Change the query type to a crosstab query. For the ISBN field, change the calculation in the Total row to Count. Change the Crosstab row for ISBN to Value. This is the value that will appear in the body of the record set.

For the language field, change the Crosstab row to Row Heading. The information in this field will appear as the row headings in the leftmost field of the record set. For the Cover field, change the Crosstab row to Column Heading. The information in this field will appear as the column headings across the top of the dynaset. You may preview the query by selecting Datasheet View. Save the query with the name TOTAL BOOKS.

Create this same query using the Crosstab Query Wizard. Save the query with the name TOTAL BOOKS WIZARD.

TURN IN FOR GRADING:
Turn-in a single high-density 3.5 inch floppy disk (your work should easily fit on a single disk) with your name printed on the disk using a felt-tip pen (do not use pencil or I will not grade the disks). The disk should have the files you created including: LAB05P1.MDB (as modified) and LAB05P2.MDB (as modified). You may also have the other files you used as part of the assignment on the disk. The disks will be returned to you after I grade them.

You do not have to turn-in your answers to the questions posed in the exercises above. The questions are provided merely to stimulate your thought processes.