Create a Query in Microsoft Access 2013

• Create a Relationship
• Modify a Query

A query refers to the action of instructing the database to return some (or all) of the data in your database. In other words, you are "querying" the database for some data that matches a given criteria. The queries are run against one or more tables to return only the data that you're interested in.

For example, you might like to see a list of all individuals whose last name is "Griffin". Or you might like to see a list of all users who have registered with your database over a given time period. You might also want to see which customers have ordered a particular product. To do all this, you need to perform a query.

Sample Data

Before we start querying the database, we should probably make sure we've got enough data in there first. Otherwise it doesn't matter how good our queries are - they will all return zero results if there's zero data. So let's add some data.

Customers Table

Feel free to make up your own data. Here's mine:

Products Table

Feel free to make up your own data. Here's mine:

Orders Table

Again, feel free to make up your own but you will need to make sure that the value in the CustomerId and ProductId fields match an actual ID in the respective tables. For example, if your Orders table contains a CustomerId of 5, you will need to make sure there's an actual customer in the Customer table with a CustomerId of 5. Here's some sample data for the Orders table:

Referential Integrity

When you create a relationship, you also have the option to Enforce Referential Integrity (which we selected when we created our relationship). This prevents foreign key values from having no corresponding primary key value in the referenced table.

Using our tables as an example, let's say you have 10 records in the Customers table (with a CustomerId of 1 to 10), but your Orders table contains a record using a CustomerId of 11. In this case, referential integrity has not been maintained. If you had enforced referential integrity on the relationship (as we have), Access would prevent this from happening.

Below is a screenshot of what will happen if you attempt to enter a value that would breach referential integrity. In this case I tried to enter a CustomerId of 50 and a ProductId of 50, but my Customers and Products tables don't have a CustomerId of 50 or a ProductId of 50:

Note that you will only get this message if you have enabled Enforce Referential Integrity. If you hadn't enforced referenctial integrity, Access would have happily let you enter the meaningless values and the referential integrity of your database would have flown out the window.

Create the Query

Now let's create a query that returns the names of all customers who have ordered a product.

1. Ensuring you have the CREATE tab open on the Ribbon, click Query Design
   
2. The Show Table dialog box will appear with all of our tables listed. Select all three tables and click Add, then click Close:
   
3. The three tables are now represented in the top pane (beneath the Ribbon). Choose the fields you'd like to be presented in the results of your query. You can either double click on the field name or click and drag it down to a column in the bottom pane. Select the fields as follows:
   
4. Click the Run button at the top-left part of the Ribbon:
   
5. You should now see the result of the query. Here's mine:
   
6. Save the query by right-clicking on the Query1 tab and giving it a name. Call it Customer Orders:
   

So What Does Our Query Actually Do?

Our query successfully returns a all customers who ordered at least one product and it includes the product and its price next to their name. You can also see that some customers have ordered more than one product. The query has automatically joined the three tables using the relationship that we created and returned the matching data.

This query will only return those customers who have ordered at least one product. If a customer is in the Customers table but does not order a product, that customer's record will not be displayed in the results of this query. If you look back at the Customers table (near the top of this page), you'll see that Marge Simpson is a customer but she hasn't ordered any products yet (i.e. she doesn't have a corresponding record in the Orders table). Well actually, Marge is only a potential customer. She simply expressed interest in buying a space ship. But that didn't stop the pesky sales person from adding her name into the database!

In any case, the query has done its job. We didn't want to see a big list of customers that haven't ordered anything yet.

Next we're going to modify our query so we can look at some of the other things we can do with queries.

• Create a Relationship
• Modify a Query