What is a query?

What is a query?

A query is an object used to retrieve data from a database, which is a search for all records that meet certain criteria or conditions that you define.

1. 
   Click Create from the menu bar
   
2. 
   Select QueryDesign
   
3. 
   Click Add then Close
   

The query will appear in design view as the following form:

The student table has a number of fields; you can either include all of them in the result, or just select some of them.

1. 
   Click the exclamation mark button on the toolbar to see the result.
   

1. 
   Click on the Microsoft Office Button
   
2. 
   Then select Save
   

3. 
   Type: All student
   
4. 
   Click Ok
   

You can sort data in ascending or descending order using 1 or multiple fields as the following

Setting the criteria of a query

In the sort row, click the arrow and select Ascending or descending

e) Setting the criteria of a query

Remember that you want to limit the results to a specific student, who is "Roy". Therefore, you will enter the single criteria "Roy" in the criteria row of the Name column.

Suppose you want to find the names of students who their age is greater than 15 and less than or equal to 18.

1. 
   Create a new query in design view
   
2. 
   Add the student table
   
3. 
   Add the name, and the age fields to the query.
   
4. 
   In the criteria line in the age column enter
   

Suppose you want to find the names of students who live in “saida” and their age is equal to 17.

1. 
   Create a new query in design view
   
2. 
   Add the student table
   
3. 
   Add the name, the age, and the address fields to the query.
   
4. 
   In the criteria line in the age column enter = 17
   
5. 
   In the criteria line in the address column enter “saida”
   

Suppose you want to find the names of students who their age is equal to 16 or equal to 18.

1. 
   Create a new query in design view
   
2. 
   Add the student table
   
3. 
   Add the name, and the age.
   
4. 
   In the criteria line in the age column enter =16 or =18
   

Click the Run button to see the result.

Suppose you want to find the name of students who their age is 17, or their address is “Saida”

1. 
   Create a new query in design view.
   
2. 
   Add the student table
   
3. 
   Add the name, the age, and the address fields to the query.
   
4. 
   In the criteria line in the age column enter =17
   
5. 
   In the Or line in the address column enter “saida”
   

Click the Run button to see the result.

The Not Operator is used to find the non matching values. Suppose you want to find all students in the table, but you don’t want to see “Rami”.

Create a new query in design view

Add the student table

Add the name and the age

In the criteria line in the Name column enter Not “Rami”
Click the Run button to see the result.

Miscellaneous operators include:

• 
  Is Null
  
• 
  Is not null
  
• 
  Like
  

1. 
   Create a new query in design view
   
2. 
   Add the student table
   
3. 
   Add the name, and the address fields.
   
4. 
   In the criteria line in the Address column enter
   

1. 
   Create a new query in design view
   
2. 
   Add the student table
   
3. 
   Add the name, and the address fields.
   
4. 
   In the criteria line in the Address column enter
   

5. 
   Click the Run button to see the result.
   

Sometimes you only know part of the field contents, or you are interested in finding records in the table that match a given pattern, then you can use the like operator followed by the desired pattern.

You can also use with the pattern two symbols known as wildcards.

The first is the asterisk (*) which represents any collection of characters.

The other is the question mark (?) which represents a single character.
Suppose you want to find the name of students who start by the letters “Sa”.

1. 
   Create a new query in design view (add the student table).
   
2. 
   Add the student table
   
3. 
   Add the name field.
   
4. 
   In the criteria line in the name column enter
   

Click the Run button to see the result.

Practice 1 – Like operator

Use Like operator to answer the following conditions:

1. 
   The name must start by the letter (A) like "a*"
   
2. 
   The name must contain the letter (A) like "*a*"
   
3. 
   The name must start by the letter (A) and must end by the letter (b) like "a*b"
   
4. 
   The name must end with A and contains E like "*e*a"
   
5. 
   The name must end with A and composed by 4 letters like "???a"
   
6. 
   The third letter of the name is (A) and the last is E like "??a*e"
   

Parameter Query

It is a query that when run display its own box prompting you for information

Suppose you want to search data of all students who live in a given address by the user

When you run the query a new box will appear to enter the address that you want (Example: Saida)

The Result of the query should appear as the following form

Practice 2

Given the following tables

Product (Pr id, Pr name, Cost, price, Expiry date, factory)

Supplier (Su id, name, age, address, Status, Phone, Nationality, Contract, Gender)
The field "Contract" has the type yes/No.
Answer the following queries

Display the names of products which their id is different from 10 and their expiry date is in January of 2011.

Field	PR NAME	Pr id	Expiry date
Table	Product	Product	product
Sort
Show	√
Criteria		< >10 أو not 10	Between 1/1/2011 and 31/1/2011
Or

Display the names of suppliers and their ages sorted from Z to A on their ages whose names start with "A" or starts with "E", their age is not empty.

Field	Name	Age
Table	Supplier	Supplier
Sort		descending
Show	√	√
Criteria	Like "a*"	Is not null
Or	Like "e*"	Is not null

Field	Name	Age
Table	Supplier	Supplier
Sort		descending
Show	√	√
Criteria	Like "a*" or Like "e*"	Is not null
Or		Is not null

Product (Pr id, Pr name, Cost, price, Expiry date, factory)

Supplier (Su id, name, age, address, Status, Phone, Nationality, Contract, Gender)
Display the names of suppliers in ascending order, who their names start by the letter A and composed by 5 letters, and who supply products mars or twix.

Field	Name	Pr name
Table	Supplier	Product
Sort	Ascending
Show	√
Criteria	Like "a????"	Mars or twix
Or

Display the names of suppliers who are contractual or who are male.

Field	Name	Contract	Gender
Table	Supplier	Supplier	Supplier
Sort
Show	√
Criteria		yes
Or			Male

Display all data of products using a parameter on their ID.

Field	Product.*	Pr id
Table	product	Product
Sort
Show	√
Criteria		[enter the product id]
Or

Display the names of products and the expiry date of products that have Cost = 1500 and their expiry date is before the system date.

Field	Pr name	Expiry date	Cost
Table	Product	Product	Product
Sort
Show	√	√
Criteria			1500
Or

Practice 3

Supplier (Supplier id, name, address, date of birth, date of death)

Product (Product id, name, cost, price, Production date, expiry date, supplier id)

1. 
   Display the names of products which their cost is equal to their price.
   

Field	Name	Cost
Table	Product	Product
Sort
Show	√
Criteria		=[price]
Or

2. 
   Display the names of products which their cost is less than 100 L.L of the price.
   

Field	Name	Cost
Table	Product	Product
Sort
Show	√
Criteria		[price]-100
Or

3. 
   Display the names of products that have an expiry date more than the production date in 2 years.
   

Field	Name	Expiry date
Table	Product	Product
Sort
Show	√
Criteria		[production date] + 2 * 365
Or

Create a calculated field in a query

Suppose that you are designing a query, and you want to display the results of a calculation that involves other fields in the query. To create the calculated field, you enter an expression in a blank cell in the Field row in your query. For example, if you have a query that contains a Quantity field and a Unit Price field, you can multiply the two to create a calculated field for Extended Price by entering the following expression in the Field row of the query:

Extended Price: [Quantity] * [Unit Price]

Prefacing the expression with the text Extended Price: names the new column Extended Price. This name is often called an alias. If you do not supply an alias, Access will create one, such as Expr1.

When you run the query, Access performs the calculation on each row, as shown in the following illustration:

Practice 4: Expression Queries

Product (Pr id, pr name, cost, price, expiry date, fact id)

Factory (Fact id, Fact name, location)
Query 1: Create a query to calculate the new price for all products.

1. 
   New price : price + 100
   
2. 
   The output of the query should contain: Pr name New price
   

Field	Pr name	New price: [price]+100
Table	Product
Sort
Show	√	√
Criteria
Or

Query 2: Create a query to calculate the Profit for the products "Pepsi" or "Seven Up".

1. 
   Profit : Price – Cost
   
2. 
   The output of the query should contain: Product name Profit
   

Field	Product name: pr name	Profit: [price]-[cost]
Table	product
Sort
Show	√	√
Criteria	Pepsi
Or	Seven up

Query 3: Create a query to calculate the 10% tax of price of "Pepsi" product.

The output of the query should contain: Name Tax

Field	Name: pr name	TAX: [price]*10%
Table	product
Sort
Show	√	√
Criteria	Pepsi
Or

Extra question

Calculate the amount of 5 products of Pepsi (price*5).

The output of the query should contain: Amount

Field	Amount: [price] * 5	Pr name
Table		Product
Sort
Show	√
Criteria		Pepsi
Or

Informatics

Microsoft Access - -

Directory: file -> view
view -> Western Europe after wwii
view -> Author study: paul fleischman
view -> Qpa1 7ela study Guide
view -> Arctic Oil/Gas Neg
view -> November 1970 Approximately 150,000-500,000 deaths Bhola Cyclone Bangladesh
view -> Grade Level: 6 Unit of Study: Caribbean glce
view -> Citation: Duffield, Katy
view -> Table of Contents Lesson #
view -> Chinese Wind Energy Disad

Download 132.68 Kb.

Share with your friends: