[Music]
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
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 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 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 right join or
option 3 for a 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 like
what you just saw radix add a comment
subscribe to our chin the freedom to
learn what you want when you want
absolutely free
GCF learn free.org