Queries are objects or tools that let us ask questions about the information in our database. Let’s take a look of some the things that we can do with Queries. Now, using queries, we can choose to see only the data from certain fields in a table. For example, just the customer last name and phone number from a customer table.

Queries can include fields from multiple related tables. For example, a query of customer an invoice tables that show us the customer last and first names for all invoices. We’re going to show you how to do this and more with queries but let’s start off by just creating a simple query.

We want to query the invoices table to show just the invoice number and the invoice total. One way to create a new query is to click on the query’s tab in the database window and then click on the “New” button. A new Query Dialog Box offers some choices including some different query wizards.

Now were going to use the simple query wizard. So, we double click on it and a dialog box opens. From the list of available tables and queries, we select the invoices’ table. We double click on the invoice number and invoice total fields to select them. Then we click on Next, to display the next dialog box.

We do want to see every field of every record, so we leave detail selected. Then we click on Next to display the last dialog box. We’ll use the default title invoices’ query. We’re done! So we click on finish. Access creates the query and displays the results in data sheet view. It shows just the invoice number and invoice total for each invoice. The title bar show that this is a select query, meaning that it selects records.

Access calls the results of this query a record set because it is a set up record selected by the query. Working with the record set is just like working with looks like one of the table. We can select and edit records and even print the record set. And this information is still connected with the records in the invoices’ table. So, changes to query data will automatically change the data in the table and vice versa.

Well, now we want to query to include the invoice due date. Make this change. We’re going to switch to design view. The view button is set for design view, so we click on it. The top portion of the design view window show the table that we include in the query with the list of fields. The field name in bold face is the primary key field. We’ll explain the asterisks in just a moment.

The button portion of the query window is a grid. Each column in the grid can include a field name. The table the field comes from and instruction to tell access how to use the field in the query. The show check box let us control whether or not a query field displays in data sheet view. To add the due date field to the query, we just drag it to the grid. The pointer changes to a small rectangular box representing the field.

We want to be the second field in the query so, we drop it there. The invoice total field moves over to the third column. If we want to add all the fields of a table, we can just drag the asterisks to the grid.

To view the updated query, we just click on the view button. The data sheet view shows the invoice number, due date, and invoice total for each invoice and notice, that the record set is sorted by invoice number because it is the primary key field in the invoice’s table. So far we've created queries that use data one table.

Now, we want to do is create a query that uses data from multiple tables. We want the record set to also show the customer’s last name for each invoice. But that information comes from different table, the customer’s table. To end that table to the query we first switch back to design view.

Next we’ll use the show table button. When we click on it, the show table dialog box displays and as you can see not only can we query tables. We can also query other queries.

Now, we’ll add the customer’s table so we double clicked on it and the field list appears in the design view window. Then we close the show table dialog box. The two field list automatically display with the relationship line because of the relationship we created earlier. The relationship tells access which customer’s last name goes with which invoice number.

Now, we’ll add the last name field to the table. A quick way to add a field to the next available column is just double click on it. When we do it’s added to the query. Then we switch to the record set. The last name information from the customer’s table is added and notice that the record said is now sorted by customer ID. That’s because it’s the primary key field of the primary table in the query.

Now, I know it might seem confusing since that field isn’t displayed in the query. But it’s not a problem because we can sort a query by any field we want. Now in the next topic, we’re going to show you how.