generally used to extract information from the tables and present it in a
non-formal format or create formal reports. Queries can be
created from one table (single table queries) or multiple tables (multi-table
Examples of single table queries:
all products that has Quantity on hand less than 100
all customers that live in Washington
Example of multi-table queries:
the company names (Customer table) and the product description (Stocks table)
the companies purchased for each order (Orders table).
In this class, we are primarily
concerned with constructing queries using QBE (Query By Example) where you use
a graphical interface to select the tables and conditions to be queried.
The other form of query
construction is through SQL (Structured Query Language) where you instruct the
computer to select certain fields and records by writing SQL statements.
Before you construct your query,
ask yourself two questions.
1.What fields do you want to
2)Where can I get the data? (Which
For example: If you want to list
the Customers’ Company Names and Titles, you need to display “CompanyName” and
“Title” for every customer, which are all located in the same table:
Example: List the company names
and the product description for the stock items the customers purchased.
In this case, “Company”
comes from the Customers Table and “Description” comes from the Stocks
For Multi-table queries, you will
need at least two tables and possibly more because we need to link (traverse)
the tables before we can get from one field to another. We will leave this for
now and come back to it later.
Steps to create single table
Query: For each customer,
we want to list the customer ID, company name and title.
the Queries Button / Tab on the Database Window, and select Create
query in Design view.
Figure 1: Database window
Alternative way: Click on the
icon bar at top right corner (you might have to use the pull down list to get
to queries) From the available selections, select the ‘Design View’ option and
3)Add the tables you will need to access the fields you want.
In this case, we
only want to add the Customers table. Select “Customers”, Click Add and
Figure 2: Select tables
4)Select the necessary fields. There are three methods of
-Drag the fields from the tables into each column
-Double click the field you want
-Click in the field that you want to set and use the pull down
list to select one When you have more than one table, be sure to select the
table and the field
Figure 3: Query designer
5)To execute this simple query, which lists the “CustomerID”,
“Company” and “Title” for every customer, we click the execute button .
6)You will see the list created in an informal Datasheet format.
7)Select View/Query Design or click to go back to the design
Though we didn’t use all of them,
each of the rows in the Query Designer has a purpose:
The fields that you want to use.
The table where the field is taken from. This comes really
handy when you are creating multi-table queries.
This field has a drop down list to select how you want the
lists to be sorted. If you choose ascending Order for the Company. Then the
records will be sorted according to alphabetical order with ‘a’ first.
If you want the field to be shown or displayed in Datasheet
view, make sure that it is marked with Υ.
This is most useful to trim the list such that the list
will satisfy a certain conditions by only including certain records.
I. For every Customer, list
the Company in alphabetical order, the CustomerID and the Title.
Mathematical & Logical Operators
The following are mathematical
operators that you can use:
greater than or equal to
less than or equal to
The following are logical
operators you can use:
NOT, OR, AND
>=10005 inside the CustomerID
This means only customers that
have a CustomerID greater or equal to 1005 will be displayed in the query.
>=10005 and <=10010 inside
the CustomerID criteria field:
Only customers that have a CustomerID between 10005 and
10010 (inclusive) will be displayed.
You can use the Like operator to
find the values in a field that match the pattern you specify
“WA” in the State criteria field:
Only customers that live in
Washington will be displayed. Note that for non-numeric fields we use double
“*Smith*” in the Customer Company field
In this case, we add asterisk (*)
in the quotes in front and back to allow for “wild cards” i.e. customers like
Ann Smith or Simthson or Smith will be selected.
“[R-V]*” in the Customer Company field
In this case, all companies that
have a name starting with R to V will be selected. Note that we use [ ] to
indicate the range for wild cards.
“1/*/96”in say a field denoted by date
In this case, only records whose date falls in January will
IS NULL Operator
eg.: put the IS NULL as a
criteria in Company and only records with a NULL (nothing) in the company
field will be displayed.
II.List only customers that are located in WA.
III.List only customers that are located in WA and whose ratings is
higher than fair (Remember that we denote 0 for No ratings, 1 for poor, 2 for
fair, 3 for acceptable, 4 for good, 5 for excellent)
query and name your query “simple”.
Create multi-table queries.
Query: For each order,
list the company names and the product descriptions of the stock items the
1.Follow the same steps as we did for single table queries. When
you have to select the tables, you can select as many tables as you
2.Add the Customers table (to get company names) and the Stocks
table (to get the product description).
3.You will notice that the two tables are not linked. In order to
get the product that the customer has ordered, we need to get the orders that
the customer places and from those orders, the products that were purchased. To
get the orders the customer has placed, we need the Orders table. To
get the individual product ordered in that particular order, we need the OrderDetails
table. Once we add the two tables, all the tables are linked.
NOTE THERE IS NO
STAND-ALONE TABLE IN A MULTI-TABLE QUERY.
If so, either
the table is redundant, that means we don’t need any fields from it. Or we need
another table to help linking the tables together
Figure 4: multi-table Query designer
If you want to list only the
customers that purchased products in January, type Like “1/*/96” in the
Criteria row of “Date” as follows:
Figure 5: multi-table Query designer
with criteria on Date
Note that I did not put a Υ mark in the Show
row of Date, therefore a “Date” column will not be displayed.
all the companies located in Washington that purchased only Software items in
the month of February.
CustomerID, Company name, Product Description. You can close the query and save
it as complex.
Exercise 4 Create Queries
Figure 1: Database window
Figure 3: Query designer 3)
Though we didn’t use all of them, each of the rows in the