Practice English Speaking&Listening with: Access 2010: Multi-Table Queries, Part 2

Normal
(0)
Difficulty: 0

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.

The Description of Access 2010: Multi-Table Queries, Part 2