What is a query?



Download 132.68 Kb.
Date31.01.2017
Size132.68 Kb.

RHHS – SE1

ACCESSProperties - Queries






Index

Queries

What is a query?


ac07_lesson12

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.



Query Design


Creating a query




  1. Click Create from the menu bar

  2. Select QueryDesign

  3. Click Add then Close


ADD

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


a) Adding field in a query

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


To add a field, double – click the field name in the field list, which will automatically make occupy the next available place in the query.



b) Running a query

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





c) Saving the query

  1. Click on the Microsoft Office Button

  2. Then select Save

The saveas dialog box will appear (to let you save the query as whatever name you choose)

  1. Type: All student

  2. Click Ok


d) Sorting data

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.




The result of the query will be as the following form:






f) Using And operator

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

> 15 And <= 18
Click the Run button to see the result.

g) Using And operator in multiple fields

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

Click the Run button to see the result.

h) Using Or operator

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.




i) Using Or operator in multiple fields

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.




j) Using Not Operator

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:



k) Is Null Operator

Is Null operator is used to check if you have fields in a table with no values, that means empty fields.


  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

IsNull
Click the Run button to see the result.

L) Is Not Null

Is not Null operator is used to check if you have fields in a table with values, that means not empty fields.

  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

IsNotNull


  1. Click the Run button to see the result.


o) Like Operator

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

Like "Sa*"


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












  1. 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













  1. 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.



using an expression to create a calculated field in a query.

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



a calculated field, shown in datasheet view.

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 - -



Download 132.68 Kb.

Share with your friends:




The database is protected by copyright ©ininet.org 2020
send message

    Main page