Access: Multi-table Queries (Part 1) - YouTube

We've already talked about how to create a simple

query that only uses one table.

But queries that involve more than one table let you ask much more interesting questions

to your database.

Now this also takes a little bit more planning than a single-table query, and there are four

steps that you can use to help you design your query.

The first step is Pinpoint exactly what you want to find out.

In other words, which question are you trying to answer?

In this example, let's say our bakery is sending out coupons to our customers who live outside

the city limits, to entice them to come back to our bakery.

Obviously, we don't want to send them to people who live really far away - we just want to

focus on people who live relatively close to Raleigh.

And we're also just going to send them to customers who have previously placed orders

at our bakery.

The customers who meet all three of these requirements will receive coupons.

So the question that we're trying to answer is 'Which customers live in our area, are

outside the city limits, and have placed an order at our bakery?'

The second step is to Identify the information that we need.

We'll need the customers' names and their contact information.

And in order to know whether they have placed an order at our bakery, we'll also need to

look at the Order ID numbers.

The third step is to Locate the tables that contain the information that we need.

In this case, the customers' names and contact information are stored in the Customers table.

And the Order ID numbers are stored in the Orders table.

So that means we need both of these tables in our query.

At this point, we have enough information to start creating our query.

Go to the Create tab, and select Query design.

We're going to add the Customers table and the Orders table.

And then close this window.

And you resize these windows if you need to.

Then we're going to double click the fields that we need.

I'm adding the customer's first name, last name, street address, city, state, zip code,

and phone number.

And from the Orders table, we'll need the ID field.

When you have more than one table in a query, they will be connected by a line, and this

is called a Join.

The join will often have an arrow that points to the left or the right, which tells the

query which table to look at first.

Sometimes you'll need to change the direction to get the results that you want.

We're going to double-click on the join to change it, and we want to select the third

option which says 'Include ALL records from the Orders Table'.

And in the next video, we're going to talk a little more about *why* we're choosing this

option, but basically this means that it will pull from the Orders table first, which ensures

that only the customers who have placed an order will be included.

When you click OK, you can see that the arrow now points to the left.

The fourth step is to determine which search criteria you need to use.

We're going to be adding criteria under the City and Phone Number fields.

First, we want to exclude all of the customers who are in Raleigh.

To do this, we're going to need to use a very specific syntax.

So for the City criteria, type Not In, and then in parentheses type Raleigh in quotation

marks.

You can use this syntax whenever you want to exclude something from the query results.

Now we also need some way of limiting the results to just the nearby towns.

And in this case we're going to do this by getting the area code from the Phone Number

field.

The 919 area code covers Raleigh and a number of nearby cities and towns, so this should

give us a pretty good range.

We'll need to use a syntax that looks at the beginning of each phone number.

In the Phone Number column, type Like, and in parentheses, type *quote* 919, asterisk,

*end quote*.

The asterisk means that any phone number can come after the 919 area code.

There are many other syntaxes that you can use, and in the next video we'll look at a

few other examples of these.

In this case we are putting the criteria on the same row, because we want the customers

to meet both of these criteria.

If they just meet one of the criteria, then they're not going to be included in the query

results.

If we needed them to meet one or the other, then we would put one of the criteria on the

next row.

This query is finished now, so we can Run it to see the results.

And you can see that each customer meets both criteria.

They are not from Raleigh, and their phone numbers begin with 919.

[put boxes around city and area code]

So generally, more complex queries require more planning.

But you can make it a lot easier by just following the four steps of Pinpointing exactly what

you want to find out, Identifying the information that you need, Locating the tables that contain

the information, and Determining exactly what criteria you need.

And in the next video, we're going to look at joins and search criteria in a little bit

more detail.