Access Queries
Using Query Design View
[This exercise was created by Evelyn Barry.
If you have any questions or comments, she can be contacted at eb48@andrew.cmu.edu]
To begin this exercise, you will need to load the library database onto your PC. Do that by clicking here. (The library database was created using some of the data from the previous lab exercise for creating databases.)
Begin by opening Microsoft Access '97, and requesting the existing database, library. After the database is open, your screen should look like the one shown below.
Our first query will do the following: "List the ISBNs for any book with more than 10 copies in the library."
Remember that a query is simply a question. Access categorizes queries in several different ways; select queries, action queries, make table queries, crosstab queries, etc. In this exercise we will be concerned only with select queries.
Click on the Query tab and request New. You will be asked to select from a list of optional views for queries. Choose Design View. The Design View will begin by asking which tables need to be included in your query.
Select the table(s) you from which want to retrieve data. At this point you can choose any number of tables from the database. Our first query is very simple and requires only the inventory table. Highlight the inventory table by clicking on it once and the add it to your query by clicking on Add. After you have added all the tables you need for your query, click on close. Your screen should look like this:
Each column in the design view relates to a field in a table. The specifications you give on the options lines below field and table specifications will tell the query how to handle that data element.
We have been asked to list ISBN, so the first field we need will be ISBN. Click down arrow on the right side of the field line in the first column of design view. A window will appear with all the fields listed for the inventory table. Click on ISBN. Access fills in the field and table names for you.
We are also interested in the number of copies. Using the same steps, put 'copies owned' in the field for the second column. Your screen should look like this:
Now we need to complete the specifications for our query. We are only supposed to list those ISBNs with copies owned > 10. To add this to our query we need to go back to the design view. Click on the Design View icon in the upper left corner. You should now see the design view screen as you left it. To add selection criteria for a field, click on the Criteria line in the column for the field you will be testing. In this case, we want to test values of copies owned to see if they are > 10. Enter > 10 in the criteria line for the copies owned column. Your design view for this select query should look like the one below.
Now rerun the query and see how your results have changed.
We have completed the first query. When you close or save this query, you will be asked to give it a name. Access will name it for you, or you can choose your own more descriptive name. We will call it Query1. The query will then be saved as one of the objects in your database. You can then run it anytime you like by clicking on Open under the Query tab.
Now, let's make a list the titles of books with more than 10 copies owned. Start by displaying the Query tab. Highlight Query1, the query we just completed. Then choose Copy under Edit. Now paste the query back onto the Query tab. You will be asked to give it a new name. Call it Query2. Your Query tab screen should now look like this:
Open Query2 in design view. We need to add the titles of the books to our query. However, title is not one of the data fields in the inventory table. We need to add the ISBN master list table to our query. To do this, open Query in the top toolbar of the design view screen. Select Show Table. You will be presented with a window displaying all the tables in the database. Click on the ISBN master list table, Add it to your query, and Close the Show Table screen. Your design view should now look like the one below.
Notice that Access has recognized the similarity of the key fields in the two tables and automatically established a relationship between the two tables. This tells the query that when the isbn in the master file matches the isbn in the inventory file, the other data fields can all be considered as pertaining to the same isbn book.
This query wants us to list title instead of isbn. In the first column, clisk on the right end of the Table row. You will be given a list of table names to choose from. These correspond to the tables displayed in the workspace above. Choose ISBN Master List. In a similar manner, enter title for the field name in the first column. The design view of Query2 should now look like this:
Now run the query. You should get a list like that for query1, but listing titles instead of isbn.
Now try to create Query3: List the titles of books written by Grisham that were copyrighted after 1993.
Hint: You will need selection criteria for both the author's last name and the copyright year. Operators for selection criteria can include
Your finished query should look like this:
Now list the most recent book copyrighted by Grisham. To do this, we will use Query3 and sort the output listing the most recent book first. We do this by adding Sort parameters for the copyright field. Click on the right end of the sort line for copyright year. Access will give you a choice of Ascending, Descending and (no sort). Ascending sort will arrange records from smallest to largest. Descending sort will arrange them from largest to smallest. To list the most recent date first, we want to list the largest or highest year first. This would be a descending sort. As a test, run the query again after specifying the sort sequence. The results of your query should be arranged as follows:
However, we have been asked to only list the most recent. Query lets us specify how many records we want to list. In the toolbar at the top of the design view you will find a Top Values icon. The value is normally set to All. You can change it by clicking on the attached down arrow and selecting a preset option from the pop-up window. Another way is to simply type over the All option with the number of records you need. In this case, we only want to print one record, so replace All with 1.
When you run this query, you should list only one title by Grisham, the one with the most recent (highest) copyright year.
Now try these Queries:
How many books has each author written?
Hint: You will need to use the functions available to you in the Totals line for each field. Display the Totals line by selecting Totals under View in the toolbar at the top of the screen. By clicking on the right end of the Totals line in the appropriate column, you can select the aggregating function that will produce the results you seek. In this case, you want a count of titles grouped by author's last name. Notice the other functions that are available (Sum, Avg., Min, Max, Count, etc.).
The query should look like this:
The results will be:
See if you can create queries to find the author with the least expensive book; the most expensive book. Find out which author's books have the highest average cost.
Now that you have mastered simpler queries, lets try one more.
Answer this: List the inventory value of each title. The listing should start with the most expensive inventory value.
Hint: inventory value for each title = cost * copies owned
When dealing with queries with more than one table, field names must be prefaced by the table name followed by a period, e.g. tablename.fieldname. If table or field names contain spaces, they must be contained in square brackets.
Inventory value would equal [isbm master file].cost*inventory.[copies owned]
Inventory value is not yet a defined field. We create a Calculated Field by entering the new name in the field and following it with a colon (:), then show the expression for the way the field should be calculated.
In this case, we would enter the following for field name.
Inventory value: [isbm master file].cost*inventory.[copies owned]
Now we can refer to inventory value as if it were any other field in one of the tables, even though the only place it exists is in our query. We can sort Inventory value, or use any of the functions from the Totals line that we would like. In this case, we want to sort descending on inventory value to list the title with the most valuable inventory first.
The results of this query should look like this:
Now we want to save the results of a query in a table. Back in design view, under Query click on Make Table Query. This option will save the query results as another table in your database. You will be asked to name the table. In this case let's call it inventory value. The results of you query will be stored in this table and will change only when you rerun the query and answer yes when Access asks if you want to replace the values in the table. Note: If queries get too complicated, you can make a partial selection with a query, save the results in a table, and then finish by creating a second query for the new table(s) you have just created.
This lab is intended to give you an introduction to select queries using design view. After you become familiar with these simpler queries, try some more complicated ones on your own.
