Access 2000 Introduction

Queries

When working in a database, most users will only need a certain group of records at any one time. Some may want to look at sales transactions for the previous week, while others may only need to look at a list of addresses in downtown Chicago. Whatever the reason, database programs have the ability to search for any record a user needs and manipulate the data in many different ways. These are accomplished by creating a QUERY. Once tables have been established inside of a database, a person can develop a query to select a group of fields from those tables, select only records that adhere to a specific set of criteria, and ready those records for use in a report.

Queries can be based on a single table in the database, or they can be based on several tables that are joined together. As an example, we will use a single table to set up the first query. The Locations table should first be selected. Then, click on the NEW OBJECT button. The New Object button contains a drop-down arrow that lets you select several different types of objects as shown below:

When you select Query from the list, Access gives you the opportunity to create a simple query, use one of the Query Wizards, or allows you to set up the query yourself by going into Design View. This is what the query screen looks like in Design View:

You have the ability to pick and choose the fields you want for the query by either double-clicking the field, or by dragging it into one of the field boxes in the top row of the grid. You can use the [SHIFT] key and a click to select a consecutive list of fields after the first one. To choose multiple fields, hold down the [CTRL] key as you click on fields. <Click here> to see a video demo of fields being added by both methods. (208K)

For example, here is a query on the city and state fields of our Customers table. The query will find all records where the city is Chicago. (NOTE: queries are NOT case-sensitive)

To run the query and see the records selected, click on the Run Query button. The results are shown below. It should be noted that you can then save the query and use it again and again. If new records are added that meet the criteria, they will be shown. (NOTE: You can sort by any of the columns within your query. You can also choose to show or hide rows by clicking on the appropriate check box.)