Lab #03 Date:

Objective:

Study the Queries in MS Access.

Theory:

Queries :

Queries are questions submitted to the database. They can be used to filter, perform calculations on, and

summarize data.

The simplest type of query is one which shows some or all of the data from one table. Open the file

AccessLab2Queries.accdb to get started. Take a moment to familiarize yourself with the database. Also,

take a look at the Relationships in the database. This should give you a good overview of the type of data

we are dealing with here.

The database keeps tracks of books, along with their publishers and authors.

Let us start by creating a query showing all the information in bktblTitles.

 Under the Create tab, click on the Query Design button from the Queries group.

 Access is now asking which tables we would like to include in the query. Add bktblTitles and press

Close. Remember, you can always bring up the Show Table dialog box either by right-clicking, or by

selecting the Show Table button from the Query Setup group.

 Access now shows you the design view of your query. This form of queries is called query by

example or QBE. To add all the fields of the table into the query, double-click the * under

bktblTitles.
 Now, to see the result of your query, click on the Run button under the Results group.
To add the two other fields, double-click on price and then on sales.
The following examples are bases on Agent-Customer Database; we just created in
previous labs.

Create a select query

If you want to review data from only certain fields in a table, or review
data from multiple tables simultaneously or may be just see the data
based on certain criteria, a select query type would be your choice.

A. Review all the data from select fields

If you want to apply simple query that displays all the data (all rows,
and all columns), follow the following procedure:

1. Open the database (Agent-Customer Database) and

on the Create tab, click Query Design.
2. On the Tables tab, double-click the Customer table.
3. In the Customer table, let’s say that you want to display all data
(all rows, all columns), then Select CUSTOMER.* option from
Field dropdown.
4. On the Design tab, click Run. The query runs, and displays a
list of products and their prices.

B. Review selected attributes data from select fields

For example, if your database has a table with a lot of information about customers and you want to review a customer
Code, First name, last name and Phone number, follow the following procedure:

1. Open the database (Agent-Customer Database) and on the Create tab, click Query Design.
2. On the Tables tab, double-click the Customer table.
3. In the Customer table, let’s say that you want to display selected attributes, then select the required attributes from
Field Option in each column from Field dropdown.
4. On the Design tab, click Run. The query runs, and displays a list of products and their prices.

C. Review selected rows and attributes data

For example, you want to review a customers whose Insurance Policy is “T1”and view their customer code, First name, last
name and Phone number, follow the following procedure:

5. Open the database (Agent-Customer Database) and on the Create tab, click Query Design.
6. On the Tables tab, double-click the Customer table.
7. In the Customer table, let’s say that you want to display selected attributes, then select the required attributes from
Field Option in each column from Field dropdown. In the Criteria option specify Like “T1”(Like and double quote
required for Text attributes only). If the attribute is numeric, write only number.
8. On the Design tab, click Run. The query runs, and displays a list of products and their prices.
D. Review data from multiple related tables simultaneously

For example, if you have a database for a customer and its agents, and you want to display customers along with their
Agents name, Agents Contact information, use the following procedure:

1. Open the database. On the Create tab, in the Query group, click Query Design.
2. On the Tables tab, double-click Customers and Agents.

Note: The line (called a join) that connects the Agent_Code field in the Customers table and the Agent_Code field in
the Agents table. This line shows the relationship between the two tables.

3. In the Customers table, double-click customer code, First name, last name, Phone number and
Agent_Code to add these fields to the query design grid.
4. In the Agents table, double click Agent_Code, Agent_name, Agent_Phone Fields to add these fields to the query
design grid.
5. On the Design tab, in the Results group, click Run. The query runs, and then displays a list of orders for
customers in Las Vegas.
6. Press CTRL+S to save the query.

E. Create a parameter query

If you frequently want to run variations of a particular query, consider using a parameter query. When you run a parameter
query, the query prompts you for field values, and then uses the values that you supply to create criteria for your query.

Note:  You cannot create a parameter query in an Access web app.

Continuing from the previous example where you learnt to create a select query that returns customer Insurance Policy T1,
T2, S1 or S2 each time that you run the query. To follow along, open the database that you created in the previous
example:

1. In the Navigation Pane, right-click the query named Query_Insu_T1 (that you created in the previous section),
and then click Design View on the shortcut menu.
2. In the query design grid, in the Criteria row of the City column, delete T1, and then type [For what Insurance
Type?].
 The string [For what Insurance Type?] is your parameter prompt. The square brackets indicate that you want
the query to ask for input, and the text is the question that the parameter prompt displays.

Note: Neither a period (.) nor an exclamation point (!) can be used as text in a parameter prompt.

3. Select the check box in the Show row of the Cust_Insure_Type column, so that the query results will display the
city.
4. On the Design tab, in the Results group, click Run. The query prompts you to enter a value for City.
5. Type T1, and then press ENTER to see customers with T1 policy.

What if you don't know what values you can specify? You can use wildcard characters as part of the prompt:

6. On the Home tab, in the Views group, click View, and then click Design View.
7. In the query design grid, in the Criteria row of the Cust_Insure_Type column, type Like [For what Insurance
Type?]&"*".

In this parameter prompt, the Like keyword, the ampersand (&), and the asterisk (*) enclosed in quotation marks
allow the user to type a combination of characters, including wildcard characters, to return a variety of results. For
example, if the user types *, the query returns all cities; if the user types L, the query returns all cities that start
with the letter "L;" and if the user types *s*, the query returns all cities that contain the letter "s."

8. On the Design tab, in the Results group, click Run, and at the query prompt, type New, and press ENTER.

The query runs, and then displays orders for customers in New York.

F. Make calculations based on your data

For example if you have a database for some products you’d like to sell. This database has a table called Orders Details that
has information about the products in fields such as, price of each product and the quantities. You can calculate the subtotal
by using a query that multiplies the quantity of each product by the unit price for that product, multiplies the quantity of
each product by the unit price and discount for that product, and then subtracts the total discount from the total unit price.
If you created the sample database in the previous example, open it and follow along:

1. On the Create tab, click Query Design.

2. On the Tables tab, double-click Order Details.
3. In the Order Details table, double-click Product ID to add this field to the first column of the query design grid.
4. In the second column of the grid, right-click the Field row, and then click Zoom on the shortcut menu.
5. In the Zoom box, type or paste the following: Subtotal: ([Quantity]*[Unit Price])-([Quantity]*[Unit
Price]*[Discount])
6. Click OK.
7. On the Design tab, click Run. The query runs, and then displays a list of products and subtotals, per order.
8. Press CTRL+S to save the query, and then name the query Product Subtotals.

For our Customer table, There is a numeric field, Insurance Amount (Cust_Insure_Amt). We can create an attribute at
runtime that will be based on Cust_Insure_Amt attribute, and have calculated result of Cust_Insure_Amt attribute. For
example we want to calculate Amount after adding 10% as profit for previous year, do the following procedure:

1. On the Create tab, click Query Design.

2. On the Tales tab, double-click Customers.
3. In the Customers table, double-click Cust_Code to add this field to the first column of the query design grid.
 4. In the second column of the grid, right-click the Field row, and then click Zoom on the shortcut menu. In the
Zoom box, type the following: Prev_Amount: ([(Cust_Insure_Amt]).
5. In the third column of the grid, right-click the Field row, and then click Zoom on the shortcut menu. In the Zoom
box, type the following: New_Amount: ([(Cust_Insure_Amt]+ [(Cust_Insure_Amt]*0.10.
6. Click OK.
7. On the Design tab, click Run. The query runs, and then displays a list of products and subtotals, per order.
8. Press CTRL+S to save the query, and then name the query Query_Calculated_Attribute.

Lab Task:
1. Create query by design. Add single Customer table.
a. Apply query to display selected attributes (Cus_Code, Cus_Lname, Cus_Phone).
b. Apply query to display all records having Customer Code 10015.
c. Apply query to display all records having Customer Code 10015 or 10017.
d. Apply a parameter query to ask for Customer code and display the data required.
e. Apply a query to Add a field called Double that displays double the amount stored in
Cus_Insure_Amt.
2. Create a query by design. Add two tables: Agent and Customer.
a. Apply query to display Customer code, customer name, customer phone, agent name, agent phone
number.
b. Apply query to display Customer code, customer name, customer phone, agent name, agent phone
number. Include only Agent code 10 data.