Forms: Adding labels

• A label is a control that holds text for display

purposes only. By default, MS Access adds a label
containing the table name in the Form Header area
of the form.
• To add a label you must click the Label control, then
click (and drag for sizing) where you want the label
placed. You can then type the content for the label
and adjust its properties for formatting (e.g. font size,
colour, ...).
 Forms: Adding calculated fields
• A calculated field is one that involves a calculation using
existing fields; for instance to multiply;
• To add a control where a calculated value will be displayed
you must click the Text Box control.

• Then click (and drag for sizing) where you wish the control be
placed. You will see two controls placed in the form: a label
and a text box.
• For the label one could enter Extended price, and in the text
box you would enter a formula (e.g. for an extended price a
formula would be: =[quantity]*[unitprice]).
 Forms: Adding calculation fields
• Adjust the size and location of the controls as
necessary. To do this can be a little tricky. To move a
control you must select the control, and then click
(and drag) the large dot in the control’s upper left
corner:

• To resize a control you must position the mouse

so you can see a resizing indicator:
 Activity
1. Open the Orders database and create a form for
OrderDetails. You will be able to incorporate the calculated
field discussed above. Open your form in Form View and view
the data to verify your calculated field displays properly. Note
that you can modify the properties of fields on a form. When
you are in Design View for this form you can right-click a field
and select properties – the first property on the Format tab is
Format and, for this calculated field, you could choose
Currency.

2. Open the Library database and create forms for each of

Book, Loan and Member tables.
Activity: reproduce this form
 Queries
• Queries are a way of searching for
and compiling data from one or more tables.
• Running a query is like asking a
detailed question of your database.
• When you build a query in Access, you
are defining specific search conditions to find
exactly the data you want.
 Queries
• Queries are used for multiple purposes in a
database environment. They can be used directly
to
– restrict the information a user can see,
– as the basis of a MS Access form,
– as the basis of a MS Access report
• With MS Access you can create a query in
multiple ways; we will examine the use of the
Query Designer.
 Queries
• To create a query, click
the Create tab and then
click the Query Design
icon:

• As a result MS Access
opens a Query By
Example (QBE) window
 Queries
• This window comprises two areas: Relationships
and Grid. The Relationships area will show each
table that needs to be accessed and the
relationships to be used with those tables.
• The Grid area is used to specify:
– fields and tables,
– sort fields,
– fields to be included in the results display,
– criteria fields must meet for a row to be included in the
query result,
– calculations,
– grouping of rows for displaying summary information
 Simple query
• The simplest query is one that displays a
complete table – all rows and columns.
Suppose we want to list all books in the library.
The process of creating the query is as follows:
 Simple query

• Click on the Create tab if necessary

and then click on the Query
Design icon. Now you can right-
click in the Relationships area and
choose the Show Table option
• A window pops up, and from the
list of tables you must double-click
Book:
• Choose Close from the Show Table
pop up window
 Simple query

• MS Access displays the Book

table and its fields in the
Relationships area. The first in
this list is an * which stands for
all attributes – double-click the
*. This results in the following:
 Simple query
• We can run the query to test it and
confirm it does what we expect: list
all rows in Book. To run a query, click
the Run icon:

• There are other views of a query. If

you click the drop down just below
the View icon:
– Datasheet View
– Design View
– SQL View.
 Simple query

• Result:
 Simple query

• Save the query and it will be listed as

a database object.
 Projection query
• A projection query is a query
that displays a subset of the
columns of a table.
• Suppose we need to produce a
listing of call numbers and titles.
• double-click the callNo and title
fields.
• Only fields checked on the Show
line are displayed in the results.
 Projection query
• Running this query yields:
 Selection query
• Suppose we want a list of
paperbacks. That is, we
want to list information
about books where the
paperback field has a value
Yes.
• Requirements like this are
placed on the criteria line of
the pertinent field(s)
 Selection query
• Running this query yields:

• Save your query as paperbacksQuery.

 Sorting the result
• Let us extend the previous
example so books are listed in
alphabetic order by title and,
since they are all paperbacks we
will not display the paperback
field.
• Create another query similar to
the last one. Now, place the
cursor in the Sort line beneath
the title field: click and select
ascending from the choices. Then
click the Show check box for
paperback to turn Show off.
 Activity
1. List the titles of books in descending order.
2. List the titles of books written by Joe Celko.
3. List all members of the library.
4. List the members in sequence by last name.
5. List the members sequenced by last name and
then by first name. (If members have the same
last name they appear on consecutive lines, and
those lines are in sequence by first name.)
6. Which of the above are a) simple queries, b)
selection queries, c) projection queries, d) both
selection and projection queries?
 AND
• Suppose we want to list Celko’s books on “SQL”. In
this case there are two criteria a book must meet:
– criteria 1: the author’s name must end with “Celko”
– criteria 2: “SQL” must appear in the title.
• We are looking for titles that have the text SQL
anywhere within the title.
• The character * when used in a text string is a
wildcard character that matches any number (zero or
more) of characters. For criteria 1 we need two
wildcards and so we specify the pattern that title
must match: Like “*SQL*”. For criteria 2 we specify
the pattern that author must match: Like “*Celko”.
•
 AND
•
 OR
• Instead of books with titles
containing “SQL” and
authored by Celko, suppose
the end user wants a list of
books with “SQL” in the title
or where Celko is the
author.
• We place the criteria on
separate lines. MS Access
ORs the criteria; a row is
selected for the result set if
either or both of the criteria
are true for a row.
 Activity
1. List the titles of books where the author name ends
with “Celko”.
2. List the titles of books where the author name ends
with “Celko” and the text “data” appears in the title.
3. List the titles of books where the author name ends
with “Celko” or the text “data” appears in the title.
4. List titles of books where the title contains the word
“medieval”.
5. List the titles of books where the title contains the
words “medicine” and “medieval”.
6. List the titles of books where the title contains the
words “medicine” or “medieval”.
Query criteria reference
Query criteria reference
Query criteria reference
Query criteria reference
 Activity

• These exercises refer to the

access2016sampledatabase.

• Create a new query. your results will include

customers (First Name Last Name City Zip Code)
who live in Durham AND phone number starting
with 919.
 Activity based on the workfile
• Open the query called Cakes and Pies Sold.
• Apply a filter to the Product Types field that
shows only Cakes.
• In the Sum of Quantity field, apply a number
filter that only shows numbers greater than or
equal to five.
• Apply an ascending sort to the Sum of
Quantity field.
 Activity based on the workfile
• Open the table called Product Table.
• Apply a filter to the Product Types field that
shows only a list of all of the products that
contain the word chocolate in their names.
• Open the table called Orders Table.
• Apply a filter to the Notes field that shows
records whose notes indicate the order was
placed for a party.
 Activity based on the workfile
• Open the table called Product Table.
• Apply a filter to the Product Types field that
shows only a list of all of the products that
contain the word chocolate in their names.
 Activity based on the workfile
• Open the table called Product Table.
• Apply a filter to the Product Types field that
shows only a list of all of the products that
contain the word chocolate in their names.
 Activity based on the workfile
• Open the table called Product Table.
• Apply a filter to the Product Types field that
shows only a list of all of the products that
contain the word chocolate in their names.
 Activity based on the workfile
• Open the table called Product Table.
• Apply a filter to the Product Types field that
shows only a list of all of the products that
contain the word chocolate in their names.
 Designing a multi-table query
Steps for planning a query
1. Pinpoint exactly what you want to know. If you could ask
your database any question, what would it be? Building a
query is more complicated than just asking a question, but
knowing precisely what question you want to answer is
essential to building a useful query.
2. Identify every type of information you want included in
your query results. Which fields contain this information?
3. Locate the fields you want to include in your query. Which
tables are they contained in?
4. Determine the criteria the information in each field needs
to meet. Think about the question you asked in the first
step. Which fields do you need to search for specific
information? What information are you looking for? How
will you search for it?
 JOINS
• If a query must be answered using data that appears in more
than one table then the query requires a database join.
• Suppose we wish to produce a list of member names and the
call numbers of books they have borrowed in the Library
database. Important points about this query:
– The Loan table has the loan information we need
– The Member table has the member names we need.
• We could say we are trying to go from a row in Loan to a row
in Member based on rows having the same value for member
id.
• In database terminology we say we are joining Loan to
Member based on a common value of member id.
 JOINS
1. Create a new query
2. Right-click the Relationships Area and
select both the Member and Loan
tables from the popup window;
3. Note the line connecting the two tables.
This is called a relationships line which
causes MS Access to join pairs of rows -
a row in Member is joined to a row in
Loan where the two rows have the same
value for id.
4. Select the call number, first name, and
last name fields by double-clicking them
to obtain:
5. Run the query and you see the results:
 JOINS
In access2016sampledatabase.
Suppose the question we want our query to
answer is this:
Which customers live in our area (area with
phone starting with the 919 area code), are
outside the city limits (Raleigh), and have
placed an order at our bakery?
 Planning our query
Step 1: Pinpointing the question we want to ask
 Planning our query
Step 2: Identifying the information we need
 Planning our query
Step 2: Identifying the information we need
 Planning our query
Step 3: Locating the tables containing the
information we need
 Planning our query
Step 4: Determining the criteria our query
should search for
 Implementing our query
Joining tables in queries: default join by access
 Implementing our query
Joining tables in queries: Exemple
 Implementing our query
Joining tables in queries: from left to right
 Implementing our query
Joining tables in queries: from left to right
 Implementing our query
Joining tables in queries: from right to left
 Implementing our query
Joining tables in queries, from right to left
 To create a multi-table query:
Step 1: Select the Query Design command from
the Create tab on the Ribbon.
 To create a multi-table query:
Step 2: Select each table you want to include in
your query and click Add.
 To create a multi-table query:
Step 3: The tables will appear in the Object
Relationship pane, linked by a join line. Double-
click the thin section of the join line between two
tables to edit its join direction.
 To create a multi-table query:
Step 4: The Join Properties dialog box will appear.
Select an option to choose the direction of your
join. In our example, we'll choose option 3
because we want a right-to-left join.
 To create a multi-table query:
Step 5: In the table windows, double-click the field names you want
to include in your query. They will be added to the design grid in
the bottom part of the screen. In our example, we'll include most
of the fields from the Customers table: First Name, Last
Name, Street Address, City, State, Zip Code, and Phone Number.
We'll also include the ID number from the Orders table.
 To create a multi-table query:
Step 6: Set field criteria by entering the desired criteria in the
criteria row of each field. We want to set two criteria: Not in
("Raleigh") in the City field, and Like ("919*") in the Phone
Number field. This will find customers who do not live in Raleigh
but who do live in the 919 area code.
 To create a multi-table query:
Step 7: After you have set your criteria, run the
query by clicking the Run command on
the Design tab.
 To create a multi-table query:
Step 8: The query results will be displayed in the
query's Datasheet view.
 ACTIVITY: Create a multi-table query
In access2016sampledatabase.
• Create a new query.
• Select the Customers and Orders tables to include in your query.
• Change the join direction to right to left.
• Add the First Name, Last Name, and Zip Code fields from
the Customers table to your query.

• Add the Paid field from the Orders Table to your query.
• Set the following criteria:
In the Zip Code field, type 27609 to return only records with a zip code
of 27609.
In the Paid field, type Yes to return only customers who have paid.
• Run the query. If you entered the query correctly, your results will include
20 records of customers who live in the zip code 27609 and have paid for
an order. If not, click the View drop-down arrow on the Ribbon to return
to Design view and check your work.
• Save the query with the name Paying Customers in 27609.
 ACTIVITY: Create a multi-table query
In Library database.
1. For each loan show the title of the book and the date it
was borrowed. Note that the title is in the Book table and
the date borrowed is in the Loan table.
2. Modify the previous query to produce a listing that is in
order by title and then by date.
3. Produce a list that shows for each loan the book title, the
name of the member who borrowed the book, and the
dates the book was borrowed and then returned. Note
that 3 tables are needed for this query.
 ACTIVITY: Create a multi-table query
In Library database.
4. Produce a list of members and the books they
have taken out on loan. Include the
member’s last name, first name, and titles of the
books. The information to be displayed is in 2
tables, but it is necessary to specify 3 tables for
this query:
- Member joins to Loan
- Book joins to loan
 ACTIVITY: Create a multi-table query
In Library database.
5. Modify the previous query to produce a listing that is
in order by last name and then by first name.
6. For member id 2, list the person`s name and the titles
borrowed.
7. Produce a list of book titles and member names for
those books that are due back May 18, 2014.
8. Produce a list of book titles and member names for
those books that have not been returned. In this case
you must give the criteria for dateReturned as null.
Null is a special keyword that represents no value.
 Activity

Create a new query. your results will include

customers (First Name and phone number ) whose
zip code starts with 27 AND who have a pre-paid
order.
ACTIVITY: Create a multi-table query

Which customers placed an order with

a due date between February 15th and
March 15th 2013?
 ACTIVITY: Create Totals query

We want to find out how many of each menu

item at our bakery has been ordered—how
many Almond Croissants, Apple Pies, and so
on.