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