Since you can download the Madam Curry database, you should get a copy.

madam_curry.mdb

If you want to duplicate my steps you can do it in this database or one of your own creation.  But you are going to want to name your QUERIES slightly differently than the names I've used so you do not overwrite those that already exist.  But all the other names should match as exactly as you can.  Or you can work in a copy of madam_curry.mdb.

The first step is to open the database and select the queries.  We are going to work in Design View so that you will learn more than if you were working with the wizard.

When you select

Create query in Design view

you should see something like the following.

For our first query you want to work only with the purchasers.  So

select purchasers

click on the Add button

click on the Close button

since you do not need to add anymore tables to the query.

Now you should see something like the following on your screen.

Now you should notice that the grid has labels at the left.

• Field:
• Table:
• Sort:
• Show:
• Criteria:
• or:

These rows can be filled with fairly standard syntax to execute queries.  Though, as you learn more, you will discover that some of these rows can contain terribly non-standard syntax.  But most queries, especially as you start out, will come to be quite familiar and have structures that are used over and over again.

We want to start by selecting which fields you will put in each column within the Field: row.  For my example, I want the query to select/display the following fields from left to right.  You can select them by going into the drop down list in each column.

• bill_first_name
• bill_middle_name
• bill_last_name
• bill_city
• bill_state
• bill_email_address

Now we want to make sure to sort these entries alphabetically by bill_last_name so you need to  click on the drop down box in the Sort: row under bill_last_name and select Ascending.

Your QBE design view should now look like the following.

qry_purchasers_sort

Then in order to execute it you need to click on the exclamation point in the toolbar.  Or you can save and close the query and then execute it from the controller form by double clicking on the name.

But after this is done, assuming you have the same entries as I do, you should see something like the following.

It is almost always the case that reports are formatted displays of the results of queries to make them much more readable.  We will not get into these at this point in time.

Another Query.  Now I want to motivate some more sophisticated queries, but I will start with another relatively simple example.  We want to do a query on the orders, eventually to see who is placing the orders and what they are ordering.

We start with a new QBE query design.  Now that you know how to add tables we will add the orders table to the new blank query design.  After closing the form to add tables, this results in the following query design.

• id_order
• fk_id_purchaser
• fk_id_book
• date_of_purchase

We also choose to sort by the date_of_purchase in a descending order by selecting Descending in the Sort: row and date_of_purchase column.

This results in the following QBE design view

When we execute this query by pressing the exclamation point or by closing this and double clicking the name of the query in the database form we get the following.

How can we get a query that has better information?

Remember, we have worked hard to get normalized table structures to improve the efficiency and effectiveness of our database.  But now that we want to query it to see certain things we find that information is spread across different tables.  In order to cull the appropriate information and combine it from separate tables we need to base our queries on something called a join in SQL.  The joins are based on the keys and foreign keys just like the relationships are diagramed.

In SQL, first learning to do join queries is quite difficult, though not impossible.  In QBE they are much simpler because you have already diagrammed the relationships between the tables, their keys and their foreign keys.  QBE draws upon these diagrams to develop join queries which saves the developer knowing a lot of specialized syntax and linking clauses.

So this brings us to our third and last query for this webpage.

A Join Query.  Now we want to pull information from three different tables into a single query.  In Access using the QBE design view we don't need to know any complicated syntax.

So start an entirely new query in design view.  You need to add three tables at this stage of our developments.

• purchasers
• orders
• books

Because you have already configured the relationships between these tables using the tools elsewhere in Access, when you add these to the roster of available tables for use in this query the relationships associating keys and foreign keys will automatically show up.  These relationships will also allow you to not write out the complicated joins required by SQL.

After adding all three tables and closing the form for adding tables you should see something like the following.

Now you should execute it either by pressing the exclamation point in the toolbar or closing this design view and double clicking the name of the query in the database coordination form.  When this has executed you should see something like the following.