Access Queries

WORKSHOP DESCRIPTION... 1 

Overview 1 

Prerequisites 1 

Objectives 1 

WHAT IS A QUERY? ... 2 

WHY USE QUERIES? ... 2 

TERMS TO KNOW ... 2  Select Queries 2  Action Queries 2  Crosstab Queries 2  Parameter Queries 2  CREATE A QUERY ... 3  Using a Wizard 3 

Using Design View 3 

Adding Fields to Query 4 

Running a Query 4 

EDITING QUERIES ... 5 

Move a Field 5 

Change Column Width 5 

Insert a Field 5 

Delete a field 5 

TABLE RELATIONSHIPS ... 6 

Viewing Relationships 6 

Creating Relationships 6 

Edit Existing Relationships 8 

Delete Relationships 8 

Remove a Table from the Relationships Window 8 

Adding a Table to the Relationships Window 8 

MODIFYING A QUERY ... 9 

Specify a Sort 9 

Criteria 9 

Rules for Setting Criteria 9 

DIFFERENT TYPES OF QUERIES ... 10 

Grand Total Expression Query 10 

Creating a Parameter Query 10 

Performing a Grouped Count Query 11 

Update Query 12 

Delete Query 12 

WORKSHOP DESCRIPTION

Overview

Once data has been entered into a table, it is often helpful and necessary to manipulate the data. A query is created when you want to gather information from one or more tables, perform calculations, sort data on more than one field, and update a group of records. This workshop will identify the different types of queries and explore, through the use of lecture and hands-on practice, the basics of creating, running, and modifying a query.

Prerequisites

Access 2007 Overview & Tables

Objectives

Participants in this workshop will: • Review creating a table

• Use filter and sort • Define query types

• Create a query based on a single table • Identify different types of queries

• Understand and manipulate table relationships • Modify a query by using sorting and criteria options • Create a query based on two tables

• Create a lookup query • Summarize data in a query • Calculate a field

WHAT IS A QUERY?

A query creates a question about the data stored in an associated table(s), or a request to perform an action on the data.

WHY USE QUERIES?

• Select only the fields you want displayed

• Specify only the records you are interested in displaying with criteria • Sort more than one field

• Gather information from more than one table • Perform calculations by creating new fields

• Serves as a source of data for a form, report or other queries • Update a group of records

TERMS TO KNOW

Select Queries

A select query asks questions about the data stored in your tables and returns a datasheet that contains the result of the query, without changing the data in the original datasheet.

Action Queries

An action query can change data or move data in the associated table. Append, delete, make-table, and update queries are action queries.

Crosstab Queries

Crosstab queries can compute summary totals based on values for each row and column.

Parameter Queries

Parameter queries, which are not a separate type of query, extend the flexibility of other queries by prompting you to enter certain criteria each time the query is run.

Informatio

CREATE

Here’s th your emp employee and addr

Using

1 Create choose 2 In the Type t 3 Click t 4 Click t 5 Choos 6 Choos Emplo 7 Select Title, A Note: yo 8 Name 9 You no query's Finish Note: If the query 10 Save th

Using

The end decide to have wor 1 From t n Resources a

E A QUER

he scenario: y ployee, custo es a payroll u resses. To do

a Wizard

the Northw e Local Tem Navigation to view the o he Create ta he Query W e the Simpl e the name o oyees table. the fields wh Address, Ci u can select your query q ow have two s structure in h. the resulting y in Design v he query. Cl

Design V

of your fisc o pull up a qu rked. the Create t nd Technology

RY

you are the o omer, produc update. To d o this you w wind 2007 d mplates, the n Pane, click objects in th ab on the rib Wizard butto le Query Wi of the table hose data yo ity, State/P additional ta qrytblEmpl o options, yo n Design vie g query is no view.

lose the quer

View

cal year is com uick list of S tab on the rib

y : Fall 2007 owner of No ct, order, sup do this you n will perform a database. Fro n choose N k the databas he database b bbon. on. izard and cl or query, on ou want to re Province and ables or que loyees(Add ou can choos ew. Choose ot exactly wh ry by right-c ming and yo Salespeople a bbon, click t orthwind Tr pplying and need to quer a Select Qu om Templa orthwind 2 se header, an by type. lick OK. n which the q etrieve. Cho d ZIP/Post ries to add m dresses). se either to r Open the q

hat you want licking on th

ou will need and the invo the Query D rading Comp shipping da ry your datab uery. ate Categori 007. nd click Obj query will be oose First N talCode. Cl more fields.

run the query

query to vie t, you can re he query’s ta to prepare c oice totals of Design butt Access 2 pany. You u ata. You nee base for emp

ies, ect e based. Ch Name, Last N lick Next. y or to see a w informat

2 Add the Invoice Data query to your query by selecting it from

the Show Table dialog box and clicking the Add button. Close the dialog box.

Note: If you have multiple tables or queries in this query, make sure they are related. If there are not any visible connections, create the join lines.

Figure 2. The Show Table Dialog Box

Adding Fields to Query

For data to appear in a query from the table on which the query is based, fields must be added into the query. There are several different ways to add fields to the query.

♦ Select the fields from the field lists and drag them to the columns in the grid. You can select and drag one field or several fields, as described below.

• One field – Drag and drop the field, double-click the field in the field list, or select a field directly from the list box in the Field row on the grid.

• A block of fields – Hold down the Shift key and select the first and last fields in the block. To choose non-consecutive fields, hold down the Ctrl key and click the fields.

• All fields - Double-click the title bar of the field list and drag the fields in the block.

1 Using the drop-down list box, add the following fields to the query grid: Salesperson and

ExtendedPrice.

2 Save the query as qryqryInvoiceData(Bonus) by right-clicking on the tab and choosing Save

or clicking the Save button on the Quick Access Toolbar.

Running a Query

Run or activate the query once the fields have been set.

1 Click the Run button.

NOTE: After a query is run, the results will appear in a dataset that looks just like a Table.

EDITING QUERIES

Move a Field

1 Return to design view.

2 Select the ExtendedPrice field by clicking the column selector above the field name. 3 Click the Column selector again while holding down the mouse button and drag the column

in front of the Salesperson column.

4 Run the query.

Change Column Width

1 Position the pointer on the right edge of the column you want to resize. 2 Drag the edge to resize the column.

Insert a Field

1 Return to design view.

2 From the field list, select the field.

3 Drag the field from the field list to a column in the grid. Insert the Quantity field.

Delete a field

1 Return to design view.

2 Select the field to be deleted by clicking above the field name. 3 Press the DELETE key. Delete the Quantity field.

Informatio

TABLE

To relate must be c

Viewin

1 Select 2 Click t

Creatin

Here’s th the anim Step On 1 Close t choosi 2 Create 3 Create Owner FirstN LastNa 4 Delete 5 Save an Step T 1 Create 2 Add th TypeID Anima 3 Make t n Resources a

RELATIO

information created. To

ng Relatio

the Databas he Relation

ng Relatio

he scenario. als that you

ne – Create t the Northw ng Close D a new blank a table nam rID Tex ame Tex ame Tex the ID field nd close the Two – Creat a table usin he following D Tex alType Tex the TypeID nd Technology

ONSHIPS

n from one t query relate

onships

se Tools tab nships Butto

onships

You are the watch over a the Owners wind 2007 da atabase. k database en med tblOwne xt First th xt Owner xt Owner d and make t table by righ te the Anim g the Table fields, data t xt Three l xt Full na

D field the pri

4 Save the table as tblAnimalTypes.

Step Three – Create the Animals Table 1 Create a table using the Table Design button. 2 Add the following field, data type, and description:

AnimalID AutoNumber Access assigned autonumber

3 Set the AnimalID field as the Primary Key.

4 Continue adding the following fields and data types to the table:

AnimalName Text Name of the animal

OwnerID Text Six letter ID based on the owner’s first and last names

Type Lookup Wizard…

4a. In the first screen of the lookup wizard, choose next.

4b. Base the look up on the tblAnimalTypes table and click Next. 4c. Choose the AnimalType field.

4d. Sort by AnimalType.

4e. Keep the key column hidden.

4f. Change the label name to Animal Types and click Finish. 4g. Save the table by clicking Yes. Name the table tblAnimals. 4h. Add the Description “Animal type.”

5 Save and close the table by right-clicking on the tab and choosing Close. Step Four – Create the Relationships

1 Select the Database Tools tab and click the Relationships button.

The Relationships Window will appear. Notice that a link was created automatically when you used the Lookup Wizard. Now create a link manually.

2 Add the tblOwners table by clicking the Show Table button, selecting the table, and clicking

the Add button.

3 Close the Show Table window.

4 In the Relationships window, drag the field OwnerID from the tblOwners table to the OwnerID field in the tblAnimals table.

5 Enforce Referential Integrity. Now an ID cannot be added in the tblAnimals table until

the ID has been created in the tblOwners table. Click Create.

Step 5 – Add Data

1 Add the following data to your tables: tblOwners

OwnerID cardar bilgre shemeg

FirstName Carla Bill Sheila

LastName Darma Green Megget

tblAnimalTypes

TypeID kan rab dog cat

AnimalType Kangaroo Rabbit Dog Cat

tblAnimals

AnimalID This is automatically generated.

AnimalName Bingo Ziggy Bouncer Lucky

OwnerID cardar cardar bilgre shemeg

TypeID Dog Cat Kangaroo Rabbit

Edit Existing Relationships

1 Open the Relationships window.

2 Double-click the relationship line for the relationship you wish to edit. 3 In the Relationship Dialog Box, set any changes.

4 Choose OK.

Delete Relationships

1 Click the line for the relationship you wish to delete. 2 Press the DELETE key.

Remove a Table from the Relationships Window

1 Choose Tools, Relationships.

2 Select the table to remove and press the DELETE key. 3 Close the relationships window.

Adding a Table to the Relationships Window

1 Click the Show Table button.

2 Add the table back that you just deleted.

MODIFYING A QUERY

Specify a Sort

A query can be defined to sort records in ascending or descending alphabetic or numeric order. The sort order is determined by the field placement in Design View. The fields to the left are sorted first.

1 Open the Northwind 2007 database by clicking on the Microsoft Office Button and

choosing it from the Recent Documents list.

2 Open the Invoice Data query in Design View by right-clicking on the query and choosing

Design View.

3 Click in the Sort cell of the ShipName field.

4 Click the down arrow associated with the Sort cell and select ascending from the list. 5 Sort ProductName in ascending order also.

6 Select the Dataset View button on the ribbon or click the Run button.

Criteria

Criteria defines the records you are searching for. The updated set of records is a dataset. You can change data in a dataset and Access updates the records in the underlying tables in most cases.

1 View the Invoices Data query in Design View.

2 Click in the Criteria cell for the field you are searching on. Choose City. 3 Type the expression = “Seattle” Or “Las Vegas” in the cell and press Enter. 4 Click the Run button on the ribbon.

5 From the Microsoft Office Button Save the query as

qryqryInvoiceData(Seattle&LasVegas).

Rules for Setting Criteria

• When entering a text criteria, you can use either uppercase or lowercase.

• You can use wildcard characters ? for any character and * for any group of characters. • Use the Not operator to find records not matching a value.

• Use Is Null to find records with a blank field; use Is Not Null to find only records with data in a specific field.

• Use the In operator to find a value from a list of value [e.g. typing the expression In(NY, CA,

TX) would find customers in the states of New York, California, and Texas].

• To find a Yes value, enter Yes, True, On, or –1. • To find a No value, enter No, False, Off, or 0.

DIFFERENT TYPES OF QUERIES

Grand Total Expression Query

You can use a query to perform calculations on your table data.

1 Click the Create tab on the ribbon and click the Query Design button. 2 Add the Invoice Data query to the query.

3 Add all the fields to the query.

4 In a blank query field, type the following GrandTotal: [ExtendedPrice]+[Shipping Fee]

Note: You can press Shift + F2 to zoom

5 Run the query to see the results.

Note: If you want to multiply the fields together use * (asterisk)

If you want to subtract the fields, use - (minus)

If division is part of the equations, use / (forward slash)

6 Save your query as qryqryInvoiceData(GrandTotal). Close the query.

Creating a Parameter Query

You can set up a parameter on one or more fields in your query to narrow down your query results each time you run the query.

1 Create a new query in Design View.

2 Add the Invoice Data query to the design of the query. 3 Add all the fields to the query.

4 Create a parameter for the OrderDate field, type Between [Enter the Beginning Date]

and [Enter the End Date] in the criteria cell.

5 Run the query to see the results. 6 Close the query without saving it.

Performing a Grouped Count Query

You can group your query results by the data within a field. You can also have a query count the number of entries for that field or that group. This query will group each row by the data in a column and will provide a count for each instance of the data.

1 Create a new query in design view.

2 Base the query on the Invoice Data query.

3 Add the field Ship Name to the query. Add the field again. 4 Click the Totals button on the Design tab of the ribbon.

5 A new row titled Total: will be added to your query grid. Keep the first instance of the field

as Group By.

6 Change the second instance to Count by selecting it from the drop-down list in the Total:

row.

7 You can now run the query. Close the query without saving it.

Make Table Query

You can use a make table query to create a new table based on fields in an existing table(s).

1 Create a new query in design view.

2 Add the Customers table and close the Show Table dialog box. 3 Add the First Name, Last Name, and Company fields to your query. 4 Click the Make-Table button from Design tab of the ribbon.

5 Name the new table tblCustomers(ContactNames). 6 Click OK.

7 Run the query by clicking the Run button. 8 Click Yes to paste the new rows into a new table. 9 Close the query without saving it.

10 Open the table you created. 11 Close the table.

Update Query

Use the update query to update data in records.

1 Create a new query in design view. 2 Add the Customers table.

3 Click the Update button from the Design tab of the ribbon. 4 Add the field City to the query.

5 In the Update To: field type Vegas. 6 In the Criteria field type Las Vegas. 7 Run the query.

8 Open the Customers table and perform a find. Look for the city Vegas. 9 Perform a Filter by Selection on the selection Vegas.

10 Rerun the query. This time update the City Vegas to Las Vegas. 11 Close the query without saving it.

Delete Query

Use the delete query to delete records in a table.

1 Create a new query in design view.

2 Add the table tblCustomers(ContactNames).

3 Click the Delete button on the Design tab of the ribbon. 4 Add the field Last Name to the query.

5 In the Criteria field, type Lee. In the or: field, type Li. 6 Run the query.

7 Open the tblCustomers(ContactNames) table. 8 Close the query without saving it.

Information Resources and Technology : Fall 2007 Access 2007 : Queries 13

Append Query

You can add data from a table(s) to a different table using the Append Query.

1 Create a new query in design view. 2 Add the Customers table.

3 Click the Append button on the Design tab of the ribbon.

4 In the Append dialog box, choose tblCustomers(ContactNames). 5 Add the First Name, Last Name, and Company fields to the query. 6 Run the query.

7 View the tblCustomers(ContactNames) table. Notice that the records were added twice