Action queries

CHAPTER SIX

QUERIES

We use queries to view, change, and analyze data in different ways. You can also

use them as a source of records for models, and reports. There are several types of

queries in Microsoft Access.

A select query is the most common type of query. It retrieves data from one or more

tables and displays the results in a datasheet where you can update the records (with

some restrictions).

You can also use a select query to group records and calculate sums, numbers,

averages, and other types of totals.

Parameter queries

A parameter query is a query that displays its own dialog box promoting for

information, such as the criteria for retrieving records or a value you want to insert in

a field. You can design the query to prompt for more than a piece of information; for

example, you can design it to prompt for two dates. Access can then retrieve all

records that fall between those two dates.

Cross tab queries

You use cross tab queries to calculate and restructure data for easier analysis of

your data. Cross tab queries calculate a sum, average, count, or other types of totals

for data that are grouped by two types of information — one at the left side of the

datasheet and another across the top.

Action queries

An action query is a query that causes changes to many records in just one

operation. There are four types of action queries:

· Delete Queries:

A delete query deletes a group of records from one or more than a table. For

example, you can use a delete query to remove unnecessary products that are no

longer used. With delete queries, you always delete entire records, not just

selected fields within records.

· Update Queries:

An update query makes global changes to a group of records in one or more

than a table. For example, you can raise prices by 10 per cent for all dairy

products, or you can raise salaries by 5 per cent for people within a certain job

category. With an update query, you can change data in existed tables.

· Append Queries:

An append query adds a group of records from one or more than a table at the

foot of the table. For example, suppose that you acquire some new customers and

a database containing a table of information on those customers, to avoid typing

all this information into your own database, you can append it to your Customers

table.

· Make-Table Queries:

(Make-table query) creates a new table from all or from a part of data in one or

more than a table. (Make-table queries) are helpful for creating tables to export to

another Microsoft Access database or a history table that contains old records.

We will concentrate on the select query, as they are the most common types

Creating a query using the wizard

Exercise 1: Design a query to display (employee number, name and salary )

· As you have learnt, open the company database.

· Select Queries from the database window.

· As illustrated in the previous figure select (Create Query) using Wizard

· Or Click (New) so the following window appears:

 

Select (Simple Query Wizard) so the following dialogue box appears.

 

From the previous dialogue box, the possibility of selecting fields from more than a

Table is evident. Select the required tables as shown:

 

· Select next

The previous window enables you to display all the records. It is the (Default) option.

Click finish, so the following query appears as in the following figure:

The previous window shows the required data in datasheet, try to remember the

components of the previous window that we have dealt with, on displaying the data of

the table.

Creating a Query without the wizard:

Exercise 2:Design a Query that displays (department code - department nameemployee

name and main salary) then save it as (e1). Select (Queries) from the

database window then select (New). Choose queries and choose new as in the

following figure:

Create a new query without a wizard

The following dialogue box appears:

Select (Design View) then press (ok) so (Query Design) appears as in the figure:

- When we start the design, a window of adding the required tables to the query

appears. In this query, data from department and employee tables are required.

- Select department and employee table then click close, so (Design Query)

appears as shown in the figure.

 

Move the required fields as follows:

Drag the required field from the table to the column downward.

Alternatively, Double click on the required field.

Therefore, (design view) appears as follows:

Save the query, and then press (open) to see the result.

The calculated field

Through the following exercise, we will learn how to make the calculated field.

Exercise 3: Modify query "e2" by adding field under the name of incentive (10% of

the salary) and save it under the same name.

Open (Query e2) in (design view)

Instead of editing the required mathematical expression manually, we will use

what is called (Expression Builder) that helps us write and select fields away from the

spelling mistakes in the field name that may cause many mistakes.

Click the right hand of the mouse as in the following figure, so the following short

menu appears

Select (Build) so (Expression Builder) appears as follows:

Objects of database base area

Query fields

Select salary field, and move it to an edit area by selecting (Paste Button) or by

double click on it.

Press (Multiplication Sign) then edit. 0. 10

Click (Ok)

Run Query icon from in the toolbar

To change the column title to (incentive) per cent we will return to (Query Design) from

the icon then from the (Short menu) of the calculated field, select properties as

follows:

Choose the number of decimal places

Field title property

Run the (Query) another time, what do you see?

Save the (Query) then close.

Exercise:

Design a query named "pl", to display project code, project name, the employee

name, main salary, the number of hours and hour fee (5% of salary) and the due total

(the number of hours multiplied by the hour fee). The Query data appear as follows:

 

 

Adding conditions for the Query:

Exercise 4:

Design a query "c1" to display (employee name- main salary) for the department

whose code is two.

To design this query, we follow the same steps in the previous exercises and we add

a condition under (field code) as in the following figure:

Creating a Query with parameters:

Exercise:

Modify the query "c1" to display (employee name – main salary) for the

department code that will be inserted on running the query and save it as "C2"

This query will ask for the department code on opening and running it by editing

the message that appears in the condition area between [] as in the following figure:

Run the Query, so the following message appears:

 

Insert Number 1 and click ok.

Exersise7:

Design a query to display (data) of the employees whose salaries 250 pounds or

more and save it as c3.

In this query, the formula will be >=250 and edit it under salary.

Exercise 8:

Modify The Query ''c3'' to display all the employees' data working in the project.

Whose code is bm102 and the working hours are less than 10 hours and save it as C4.

In this query, the formulae is compound i.e. the bm102 is put under the project code

and <=15 is put under the field hours number in the same row.

This means that the two formulae should be achieved together.

Exercise 9:

Modify the Query ''c3'' to display all the employees' data who work in the project

whose code is bm102 or the number of the working hours are less than ten hours and

save it as C4.

In this Query, there is a compound formula. This means achieving one of the

formulae will display any record.

We put (bm102) under the project code field and <=15 under the field of the working

hours in the second row of the formulae area.

Total Queries:

Exercise 10:

Design a Query named as (s1) to display the department name and the number of

the employees working in it.

Make a Query to display the department name and the name of the employee.

To make this Query (totalized), click on the

Alternatively, from (shortcut menu), click on any place in the foot area of (design)

Select Totals

- (Design) window appears as follows:

 

 

When we put 'Group by' under department name, it means (totalizing) according

to each department. As for (Count) when we put it under the employee's name, it

means the number of the employees for each department.

Task:

Design a Query named as (S2) to display the project name and the number of the

employees who work in it.

Questions

1- Explain briefly the following queries?

· Select Query

 

· Update Query

 

· Append Query

 

2- What is the difference between the select Query and the action Query?

3- Put (√) in front of the correct statement and (x) in front of the wrong one.

a) The calculated field by query is saved inside the table ()

b) Data can be saved inside queries ()

c) Inserting and modifying data in tables can be through queries ()