Two Basic Queries for MadamCurry.com
So we are about to work with the QBE interface in Access. I
intend on supplying the steps verbally, with intermittent images to
help you find your way.
Since you can download the Madam Curry database, you should get a copy.
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.
|The Show Table form is out in front of the
overall QBE design view so that you can first select the tables in
the database that you want to work with as you develop this set of
For our first query you want to work only with the purchasers. So
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.
|Notice how the purchasers table and the fields
within it are now available in the table section of the QBE design
Now you should notice that the grid has labels at the left.
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.
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.
|Now you want to save the query and give it a name
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.
|In this course, we will call this the
resultset of the query.
This is fairly common terminology, though it is not universal.
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.
|Now we start filling in the Field: entries by
selecting from the drop down list in each column. Since our
tables are normalized, the information is largely based on foreign
keys. So you should select the following.
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
|So now we need to save the query which I called
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.
|The resultset is not really at all informative.
That is much of the point of this exercise. While developing
and executing this query is fairly simple, and good practice, the
results are pretty much a bunch of meaningless numbers that are keys
and foreign keys.
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.
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 need to fill in the fields.
Because all three tables are available and inter-related
you can find the following fields in the drop down for
Using the notation
helps identify which table each field is contained in. Access often shortens this in different settings, but only when the context is clear.
We also want to sort these orders by the date_of_purchase in descending order so you want to select Descending from the Sort: dropdown box under date_of_purchase.
So you should see something like the following image.
|So you should save the query, which I
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.
|Now the results of this query are far more informative. Though the format could be even more helpful which we will improve on using both forms and reports in future webpages.|