inls 461
Information Tools

Professor: Serena Fenton
School of Information and Library Science at UNC-Chapel Hill

Creating Queries

Why Queries?

Query Types Creating Queries using Wizard

Queries let you pull just the data you need out of a database so you can perform tasks and get questions answered.With queries, you can retrieve, combine, reuse, and analyze your data. You can use queries to retrieve data from multiple tables, or as a source for forms and reports. Before you start thinking about the nitty-gritty of creating a query, it's a good idea to think through the questions you want to answer, logically and in detail.

  • How do you want to choose your data?
    • Do you want, for example, the 10 most of something, all items above or below a certain amount, or all employees who live in a different county from the location of your office?
  • What database fields do you need?
    • For example, for a list of your company's five best-selling beverages, you might want beverage names, manufacturers, and suppliers, but you probably don't need the states they're bottled in.
  • Once the data is returned, do you want to do more with it?
    • Do you want to multiply sales quantities times price, or view the impact on sales figures of a recent discount?

It can help to put your question into full sentence form, as in this example:

"I want to know the 10 best-selling products in our Midwest region. I need to know the product names, the product IDs, and the department that produces each product."

Whenever you run a query, it checks for the latest data in your database. The data returned by a query is called a recordset.

You can browse through the recordset, select from it, sort it, and print it. Typically, the recordset you produce with a query isn't saved, but the query structure and criteria you used to get the results are saved.


Query Types

Query type Description
Select query Retrieves data from one or more tables and displays the recordset in a datasheet. This is the most common type of query.
Parameter query Prompts the user to enter values that define the query, such as a specified region for sales results, or a specified price range for houses.
Cross-tab query Arranges a recordset to make it more easily visible, using both row headings and column headings.
Action query Creates a new table or changes an existing table.
SQL query An advanced query that is created by using an SQL statement.

A select query retrieves data from one or more tables and displays the recordset in a datasheet. You can also use a select query to group data, and to calculate sums, counts, averages, and other types of totals, although we won't cover calculations in this course.

A parameter query displays a dialog box when it runs, prompting the user to enter information to use as criteria for the query. You can design a parameter query to prompt for more than one piece of information; for example, you can design it to prompt for two dates. Access will then retrieve all data with values between those two dates.

A cross-tab query arranges a recordset to make it more easily visible, using both row headings and column headings. Data can be seen in terms of two categories at once.

An action query creates a new table or changes an existing table by adding data to it, deleting data from it, or updating it. Because an action query is so powerful, actually changing table data, you should consider backing up your data before running an action query.

An SQL query is created by using a statement in Structured Query Language (SQL). SQL is an advanced way to query, update, and manage relational databases. Access can create the SQL statement for you when you create this type of query, or you can create your own SQL statement.


Access provides two basic ways to create queries:

The Wizard, also known as the Simple Query Wizard, gives you a head start in setting up your query's structure by making some arrangements for you.

In Design View you have total control when creating a query. You drag the fields you want to a grid, and then you enter the criteria for selecting the data to be retrieved.

You can also create your query using the Wizard and then refine the query in Design View to get just what you need.


Creating Queries using the Wizard

Our example, BOOKS.MDB is "a database for managing detailed information about a collection of books. It was developed for a collection of mostly old used books, some collectible, in varying conditions, and is thus somewhat geared toward that purpose. It has 100 records in it so you can see how it works and experiment with it." books.mdb is an open source database from 25 Years of Programming,

Download books2003.mdb to try the query & forms exercises and to see the existing queries.

001

'AutoForm' Query

Note: this is just another way to access the Select Query window. Even though the button says AutoForm, it is the same Query WIzard that you can access from the Objects window 02

 

Open the Objects window and click on the table WherePubs

From any object window, click on the AutoForms button 03

04

Select Simple Query Wizard

Click OK

05

The table that you had selected in the Object window will be selected in the Query wizard

Move only the WherePub field across into the Selected FIelds column .

(do NOT click next!)

Notice that you can "choose from more than one table or query"

07

In the Tables/Queries window, click on Table:Books

Move the Field Title into the Selected Fields column.

Click Next.

08

Accept the default: Detail

Click Next

09

Save the query as: WherePublished-Query

Click Finish

10

You now have a list of each book and where it was published. By default, it is sorted alphabetically by WherePub (the left column)

11

Sort Alphabetically

Place your mouse cursor in the header column, next to the word TITLE

The cursor becomes a down-pointing arrow

Click and you will select the entire column

Click the Sort alphabetically button in the toolbar 12

This will sort either ascending or descending.

13

This will allow you to find books with similar titles and compare their publication locations

Close this query. You will see the new WherePublished-Query in the Queries window.

Congratulations! You have completed your first query!!


 
Create Query using a wizard Create a query to show which authors are published in a certain place (New York) and count how many authors their are published in that place.

While you are still in the Queries window, look at the top of the list in the Name window. You will see your two choices for creating queries.

Double click on Create Query by using a wizard.

The Query Wizard will open. (same one as before)

Click the arrow in the Tables/Queries windows, to see all of the tables and/or queries that have already been created..

Select Table Author.

From the available fields, move Author into the Selected Fields window.

15

Then return to the tables/queries window and select table: WherePubs

Click next.

16

The default is Detail. Accept it.

Click Next.

17

Change the title of your query to be specific. Title it: Authors-WherePub Query.

Accept the default: Open the query to view information.

Click finish.

The select query will open, as displayed below. You can now modify the query or make changes, as desired.

18

The information is in a spreadsheet format (this is the default of Select Query)

You can click in the Author or WherePub headers and sort either list alphabetically.

 

 

Portions of the text (Why Queries and Query Types) excerpted and edited by Serena Fenton from Microsoft Access Training: Queries I: Get answers with queries

 

 

 


revised May 31, 2006