Goodwill Community Foundation creating
opportunities for a better life
in the last video we created a query
that used two tables and we just wanted
our query to include customers who have
placed an order at our bakery now we're
going to talk about why we chose the
type of join that we did and we're also
going to look at some examples of search
criteria that you can use to narrow down
your queries in this example we chose a
right-to-left join because we wanted the
query to pull in the records from the
orders table first and then use the
information in those records to retrieve
the records from the customers table to
understand how this works let's look at
how these tables are connected every
time an order is placed it's connected
with a customer and if someone places
multiple orders then they'll be
connected with each of those orders
you'll notice that not all of our
customers have placed an order but all
of the orders are connected with a
customer and that's an important thing
to keep in mind when we're choosing
which type of join to use let's look at
what would happen if we connected these
tables with a left-to-right join first
access retrieves all of the records from
the customers table and then it uses
this list to get all of the orders that
are connected with a customer that means
the query is going to include all of the
customer records even if they're not
connected with an order and this is not
what we want instead we want to use the
order records to just pull in the
customers who have placed an order we
can do this by choosing a right-to-left
join now the query will first retrieve
all of the records from the orders table
and it will then use that list to find
all of the customers who are connected
with at least one order
so whenever you're creating a query with
multiple tables you'll need to decide
which type of join to use you can double
click the join to change it and then
choose option two for a left-to-right
join or option 3 for a right-to-left
join we are also narrowing down our
query by using search criteria in the
city and phone number fields you may
remember that search criteria have to be
written with a very specific syntax so
that access can understand them and
they'll often need to include quotation
marks and parentheses in order to be
correct so let's look at a few of the
different syntaxes that you can use if
you're looking for an exact match then
you can just put your search terms in
quotation marks if you want to exclude
something from the results then you can
use not in and you can even exclude
several different things by separating
them with commas if you're looking for
terms at the beginning or the end of a
field you'll need to use the like syntax
and you'll notice that each one of these
has an asterisk in it this is known as a
wild-card character which just means
that anything can go here for example if
you're looking for phone numbers that
begin with nine one nine then you'll
type nine one nine asterisk and that
means this query will look for nine one
nine followed by anything
and finally when you're working with
numbers you can use symbols such as
greater than and less than to test the
values and you can also look for numbers
that are between two values so those are
some of the most common syntaxes that
you can use and you might not use all of
these but depending on what type of
information you have in your database
you can probably find at least two or
three that will be useful to you