an Access Database:
A Primer for Resource Center Managers
by
Laura C. Larsson
Creating Queries in Access 97
Queries Table of Content
Introduction: What is a Query?Some Sample Queries
Creating a Query: An Overview
Creating a Query: Example 1a: Please give me an alphabetical list of the journals in your library.
Creating a Query: Example 1b: How much shelf space was taken up in 1997 by incoming issues?
Creating a Query: Example 2: How many journal titles added more than 8 inches (20.32 cm) each to your collection in 1997?
Creating a Query: Example 3: How many journals are published by Blue Cross publisher?
Creating a Query: Example 4: Please get full information on all publishers (unique id, name, address, phone and fax numbers.
Creating a Query: Example 5: Get the total number of publishers.
Creating a Query: Example 6: Select all journals whose name begins with the letter "a".
Creating a Query: Example 7: How much did we pay for journals when we last paid our bill?
Creating a Query: Example 8: Provide me with the name and contact information for the Senior Editor for the Milbank Quarterly journal.
Creating a Query: Example 9: Which of our journals are available on CD-ROM?
Creating a Query: Example 10: Provide me with the URLs for the Instructions to Authors Web pages for all journals.
Creating a Query: Example 11: I need a list of all the journals you own and in which format (hardcopy, CD-ROM, Web) so that I can make it available on my Website. Can you provide me with such a list?
Other Queries
Introduction: What is a Query?
Queries enable you to ask questions of the information you've captured in your database. Some designers say that you should plan as many of the queries as you can before you set about designing the database. It is probably a good idea to write down as many queries as you can. You will remember that we did write down several questions when we were first thinking about the database. Below you will see additional questions which we would like our database to answer.Improvements to Access now mean that users can build a query using a graphical interface rather than having to type in a line of SQL code. Access users can use "click and drag" to move the fields they want to create queries on. Once you have selected all the fields necessary for the query, Access builds the SQL code, sends it to the database and returns the results in a datasheet format.
Some Sample Queries
- How much shelf space is taken up each year by incoming unbound issues?
- How many journal titles added more than 8 inches (20.32 cm) each to your collection in the past year?
- How many journals are published by X publisher?
- How much have 1998 journal prices increased over last year's (1997)?
Creating a Query: An Overview
The basic process is a simple one.- Create a new query in Design view
- Decide which table/s to use
- Determine which field (or fields) have the information you need
- Determine if you need to add criteria (greater than, less than)
- Create the query
- Run the query
- Revise the query if necessary
- Save the query
Creating a Query:
Example 1a: Please give me an alphabetical list of the journals in your library.
Create a new query in Design view
To create a new query, click the Query tab on the main window, and click the New button.
The New Query window will appear:
In the interests of simplicity, we will use the Design View.
Select the Design View from the list of possibilities and click
the OK button.
Decide which table/s to use
Just after you ask the question it helps to determine which table(s) you need to draw on for the data. Some of the queries listed below will require only one table, others will require two or more.We are going to determine what journals we have in our library. Thus we need the LogicalJournal table since that has the name of the journal in it, plus previous names.
The top part of the Select Query window contains a field list box for each table being used in the query definition. The bottom half (called a QBE grid) is a series of columns, one column per field. The bottom section is where you define your query and set the parameters such as criteria or sorting.
Determine which field (or fields) have the information you need
To add a field to the QBE grid, click on the field name and drag the field name from the tables at the top of the window to the left-hand most column in the bottom half called Field.Determine if you need to add criteria (greater than, less than)
We don't need to add any additional criteria.Create the query
Run the query
We should get a list of all the journals we own.Revise the query if necessary
We don't need to make any changes to this query.Save the query
To save the query, click on the
(Save) button, or select File | Save from the menu.
When the Save As window appears, type in the name of the query. In this
case, the query should be saved as "All Journals" . Click the OK
button to save the query.
Hints for Working with Queries
A Query is an Object in your database and can be resaved for reuse.To sort records you can use any field as a sort key. You will need to
be in the Table Datasheet View, however. Records can be sorted in ascending
order (the default) or descending (Z-A) order by clicking anywhere in the
key field column and by clicking on the Toolbar A-Z button 
.
The original order of the records in the database is not affected by a
sort.
Adding too Many Tables
If you find that you've added too many tables to your design, select (highlight) the extra table and delete it.Queries Requiring Table Joins
This just means using more than one table to create your query.Creating Another Query:
Example 1b: How much shelf space was taken up in 1997 by incoming unbound issues?
This question asks about the growth of your collection. It helps to know how fast your collection is growing so that you can plan for shifting the collection when it gets tight, or for planning on storage of the older, less used volumes. So, here's how we determine how much shelf space is taken up by each incoming unbound journal each year.Create a new query in Design view
To create a new query, click the Query tab on the main window, and click the New button. The New Query window will appear.In the interests of simplicity, we will use the Design View.
Select the Design View from the list of possibilities and click
the OK button.
Decide which table/s to use
We are going to determine how additional much space our journals took up in 1997. Thus we need the ShelfSpace table.From the Show Table window, select ShelfSpace table and click
on Add. This will add the table to the query. Click on Close to
close the window.
You will now see the ShelfSpace table in the Select Query window
Determine which field (or fields) have the information you need
We will use the Click and drag from Table box method.Click and Drag from the Table Box Method
In the Select Query section of the window as shown above, you can see the ShelfSpace table. In this table it lists all the fields which can be selected for the query. To select the field, click and hold the left mouse button on the Year field in the table box. Continue holding the mouse button down as you drag the mouse on top of a blank column. Now release the mouse button. Repeat using the ShelfSpace Field. Put the ShelfSpace Field in the next column.You will now have the Year field and and the ShelfSpace field in the
Field row of the two columns.
Drop-Down List Box Method
In the Field row, click in the first blank column. A small down arrow will appear to the right of the empty box. If you click the down arrow, the fields you can use from this table will appear.Determine if you need to add criteria (greater than, less than)
You will need to add 1997 to the Criteria: box in the Year Field.Create the query
This query is complete. And here it is.
Run the Query
If we had data we could run the query by clicking the Run
button, or select Query | Run from the menu. A dynaset
will appear. A dynaset is a temporary table that is created when
you execute a query. The table contains the values from your query. The
data in your table has not been changed.
To return to the Design window, click the Design button, or select
View | Design View from the menu.
Revise the query if necessary
This should have gotten you the information you need. You will not need to revise the query.Save the query
To save the query, click on the Save
button and save the query as ShelfSpace97. Click the OK button to save
the query
Other Queries
| Question:
What other questions can you think of that we might use to query this database? Use this space to enter a few queries. |
For More Information
Look at Working with Queries from Microsoft Help. The Creating and Modifying Queries, Using Multiple Tables in Queries and Using Criteria and Expressions to Retrieve Data are particularly relevant.Next: Generating a Report
Back: Data Entry