To view Part-1 of this series, (by Ben Beitler ), Click here >>

Queries can be hard to comprehend and fabricate if you don’t have a smart thought of what you’re trying to search and how to find it. A one-table MS access query can be simple enough to make up as you go along. However, to construct anything more powerful, you will need to plan the query initially.

Planning a MS access query

When planning a query that uses multiple tables, follow these steps:

1. Define precisely what you wish to know. If you could ask your database any question, what would it be? Building a MS access query is more complex than just asking a question, but knowing exactly what question you wish to answer is vital to building a suitable query.
2. Identify each type of information you want to include in your query output. Which fields contain this data?
3. Locate the fields you want included in your MS access query. Which tables are they contained in?
4. Determine the criteria the information in every field needs to meet. Consider the question you asked in the initial step. Which fields do you have to look for particular data? What information are you searching for? How will you search for it?

This of course may appear to be theoretical at first, yet as you go through the process of planning our own multi-table query, you should begin to realise how planning your queries can make building them a lot simpler.

Planning our query

Let’s apply this procedure with a MS access query we’ll run on our Online Shop database. As you go through it step by step, consider how every portion of the planning process could apply to different MS access query you might run.

1—Defining the question you want to ask

Our Online Shop database contains many customers but some of them did not actually purchased; they are in our database because they signed up for our mailing list. Most of the customers live in the US, while some reside in other countries, and others live in different continents. We want to get the list of our US customers who have purchased from the shop and who live within Delaware.

To put it plainly, the question we need our query to answer is this: Which customers live within Delaware have made an order?

2—Identifying the information we need

What information do we want to find about these customers? Naturally, we will need the customers’ names, addresses, phone numbers, and email addresses. How are we going to know if they have purchased twice? Each record of an order identifies the customer who placed that order. If we include the order ID numbers, we should be able to narrow our list down to only customers who have placed orders.

3—Locating the tables containing the information we need

To compose a MS access query , you should familiarise the various tables in your database. From working greatly with our own database, we know the customer information is located in the Customers table. The Order ID numbers are in a field in the Orders table. We only need to include these two tables to get all of the information we need.

4—Determining the criteria our query should search for

When you set criteria for a field in a query, you are basically applying a filter to it that tells the query to only get the data that matches the criteria. Inspect the list of fields we are including in this MS access query. How can we set criteria that will best help us answer the question?

We want customers who live within Delaware, so we want a criteria that will return all records with Delaware in the city field. We will not set a criteria for the order ID field or any other fields because we want to see all of the orders made by people who meet the criteria we set.

Joining tables in queries

The last thing you need to look upon when designing a query is the way you link (join) the tables you are working with. When you add two tables to an MS access query, this is what you will see in the Object Relationship pane:

The line linking the two tables is called the join line. The join line looks like an arrow since it shows the order in which the query looks at data from the two tables. Notice the arrow on the image above is pointing from left to right. This means the query will look at data in the left table first, then it will examine the second table but only the data that relates to the records in the left table will be interpreted.

In some cases, Access will link the tables right to left. The join direction can affect which information your query retrieves hence you need to change the direction of the join to ensure your query contains the correct information.

To understand what this implies, consider the query we are designing. For our query, we need to see customers who have purchased from the shop, so we have included the Customers table and the Orders table. Let’s look at a portion of the information contained in these tables.

Looking at the lists, you will notice each order in the Orders table is linked to somebody in the Customers table—the customer who purchased that item. But when you look at the Customers table, you will see that the customer who have placed multiple orders is associated to more than one order, and those who have never placed an order are linked to no orders. Therefore, even when two tables are joined it is possible to have records in one table that have no relationship to any record in the other table.

So, what will be the result if Access tries to run our query with the current join, left to right? Initially, it gets each record from the table to the left: our Customers table.

Next, it collects each record from the right table that has an association with a record Access has already taken from the left table.

Since our join started with the Customers table, our MS access query will include records for all our customers, as well as those who have never placed orders. However, we only want to view records for customers who have purchased.

Luckily, we can settle this issue by changing the join direction. If we join the tables from right to left instead, Access will initially collect all orders from the right table, which is our Orders table:

Access will then look at the left table and collect only the records of customers who are connected to an order on the right.

Now, we have the information we want— all customers who have purchased from the shop, and only them. We needed to join our tables in the precise direction to acquire the information we wanted.

To view Part-3 of this series, (by Ben Beitler ), Click here >>

To get instant access to the downloadable guides, right now, click the “Buy Now” button you see in the order below…

INSTANT DOWNLOAD

My Access 2016 Database eBook Guide

Grab it Today for £44.97

A Truly Great Offer!!!

<p

“Still Unsure About Your £44.97 Decision? Read On!

I can understand why you might be cautious.  After all, we’ve never met, you don’t know me and I don’t know you.  That’s part of the reason why I’ve priced these guides so ridiculously low at just £44.97.

Act now, grab my eBook guides, and transform your skills in Microsoft Access Database today!

Kind regards,

Ben Beitler – “Your Access Database Expert!

Tags: access database expression builder, Access database tutorial, access form designs, access vba, Microsoft Access 2016, microsoft access database forms, microsoft access forms, microsoft access queries, MS Access, ms access forms, ms access queries