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 then it will 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 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 wildcard character, which just
means that anything can go here.
For example, if you're looking for phone numbers that begin with 919,
then you'll type 919 asterisk.
And that means that this query will look for 919
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.