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 the 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… then choose Option 2 for a right join, or
Option 3 for a left join.
We're 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, 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*".
And that means 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.