Microsoft Access 2003 - Introduction to Queries

Microsoft Access - Queries

Introduction

Queries are used to provide data for forms and reports based on criteria required by the user. They allow the user to search, view, and modify data from single or multiple tables.

For our purposes, we will be using a sample database provided by Microsoft called Northwind. To work with this example on your own pc we will import the database into a new database called Northwind Example.

How to Create the Northwind Example Database

Start Microsoft Access and select NewDatabase from the TaskPane.
Save the database as NorthwindExample and then click on the button Create.
Click on the menu button File | Get External Data | Import.
Navigate to C:/Program Files/Microsoft Office XP/ Office 11/Samples/Northwind.
When the Import Objects dialog box opens click on the button Select All under the tab Tables and then click on OK.

Creating a Query and Entering Criteria

Click on Queries from the Objectmenu. Select either “Create query by using wizard” or the button “New” and then “Simple Query Wizard.”
Under the menu Tables/Queries click on the down triangle to select the table “Products.” Use the double arrow (>>) to select and transfer all fields to the right.
Next select the table “Categories” transfer the field “CategoryName.”
ClickonNext. Except the suggested name Product Query and then click on Finish. Access automatically runs the query and then displays it in Datasheet View. The query that was just created is known as a Simple Query.
Click on the Design View button. Under the field “Discontinued” type “Yes” in the Criteria row and then run the query. Only the products that have been discontinued will display. Save the query but do not close it.

Note:This field's data type is displayed as a Yes or No option. If you replaced the Yes with a No only the products that are still available would display.

Using Compound Criteria

Two types of compound criteria are "AND" and "OR." Although any criteria entered under another field on the same line requires each criterion to be true in order for the compound criterion to be true. If you were to enter criteria on the "OR" row the results would produce an either or result (apples or oranges).

AND Criterion

Using the same Query enter the criteria greater than or equals one “>= 1” under the field “UnitsInStock” in the same row as the criterion “Yes.” Run the query. This query will display only discontinued inventory that contain one or more units in stock.
Remove all the criteria from the query.

OR Criterion

In this query we are asking for information on either Beverages OR Dairy Products.

Under the field “CategoryID” type the “1” for Beverages on the criteria row. On the OR row below type “4,” which is the category id for DairyProducts. Run the query. The results will only display the criteria requested.
To change the results, type “Yes” for the criteria under the field “Discontinued” on the same row as “1.” Run the query. The data returned will include only information regarding Beverages that were discontinued or all Dairy Products (current and discontinued).
To modify the query type “Yes” as another search criteria for discontinued Dairy Products. The results will now show only discontinued Beverages or Dairy Products.

Creating a Query in Design View

Create a new query using the button "Create query in Design View."
Select the “SuppliersTable” and then click on the Add button.
In design view double click on the asterisk shown in the table. This adds all the fields in the table.
Run the Query but do not close it.