Home and Learn: Microsoft Access Course
Access Queries
In the previous lessons, we've created our tables and added some records to them. We'll now query our database.
A query is a way to search your database and bring back results. Queries can be quite simple, like searching a table for a list of books by a certain author. Or they can be quite complex, like searching multiple tables and combing fields, adding equations and logic. Let's start with a simple query.
Suppose you want to know what books you have in the Romance Genre. You also want to know the name of the author for each book. To do that, click on the Create Ribbon at the top of Access. Locate the Queries panel and the Query Design item:
When you click on Query Design, you'll see a new screen open up in the main window. You'll also see the Show Tables dialog box appear:
Select the tables you want, which in our case are all three tables. Click the Add button:
The Show Table dialog box doesn't go away on its own, so click the Close button to get rid of it.
When you click Add, you'll see the tables appear in the top half of the window and a Query Designer in the bottom half:
The bottom half is the import one, all those columns and rows. This is where you construct a query. The columns are so you can select a field in a table. The rows allow you to narrow down what you want displayed. They also allow you to enter a Criteria on a particular field. You'll see what this means when we construct a Criteria in a moment.
If you query designer looks a bit cramped, you can widen the columns and the height. Hold your left mouse button down on the lines indicated below. Keep the left mouse button held down and drag to a new position:
But click the dropdown box in the first column, where it says Field:
The dropdown shows you a list of tables and their fields. The table name comes first then the field name. Notice that all three tables and their fields are listed. When you select a field, you're saying that you want that field displayed in your results.
Select the tblAuthors.FirstName item. The first column will then change to this:
So we want to show the FirstName field from the tblAuthors table in our query.
Now do the same for two more columns. In the dropdown for the second column, select tblAuthors.LastName. In the dropdown for the third column, select tblBooks.BookTitle. Your Query Designer will then look like this:
We want to discover how many books we have in the Romance genre. To do that, select tblBGenres.BookGenres for the fourth column:
Now we need to enter something in the Criteria box for the fourth column. Click inside this box and enter this:
= "Romance"
The space doesn't matter. But the double quotes mean you want a text search. Your Query Designer should look like this (the Criteria is highlighted):
Once you have built a query, you have to run it to see the results. On the Design Ribbon at the top of Access, then, locate the Results panel and the Run item:
When you click on Run, Access displays the results of your query in a new window:
As you can see, we have two books in the genre Romance.
To save your query, either click the Save icon in the top left of Access, or you can right click the tab name Query1 and select Save from the menu that appear. (Another way is to try closing down the query. Access will prompt you to save it.) Save it with the name qrySearchRomance. Once you save a query, it will appear in the All Access Objects panel on the left, under a Queries heading:
You can close down the query, now. To open it back up, simply double click it in the All Access Objects area. By default, double-clicking will run the query. If you want the Query Designer instead, right click the query name and select Design:
Let's set up a more useful query, though. Instead of just searching on the Romance genre, we'll have a popup box asking users to input a genre of their choice. This is quite easy!
Input Query Boxes
To save yourself some time, you can copy and paste a query. Right-click qrySearchRomance under Queries in All Access Objects. From the menu that appears, select Copy:
Now right-click again and select Paste. You should a small dialog box appear asking you to name your query. Call it qrySearchGenre and click OK:
You should now have two queries:
Right-click the new query and select Design View from the menu:
In the Criteria box under BookGenres, delete Romance. Instead type the following:
[Enter a Book Genre]
The square brackets cause a popup to appear. The text between your square brackets acts as a prompt, as you'll see. Your Query Designer should look like this (the new addition is highlighted):
Run your query, just like you did before. This time, you should see a popup box. This one:
The text you entered between square brackets is above a text box. Type a genre into the text box. Type Thriller:
Click OK to run the query. Access will display the results:
This is a much more useful query than the last one. Now, we can search the database for any query, rather than the hard-coded one we had for Romance.
If you want to open the query again, right click on the tab, where it says qrySearchgenre. Select Design View from the menu:
You can also click the Design View icon in the bottom right of the screen:
To execute the query again, click the Run icon in the Ribbon at the top of Access. You'll see your popup dialog box again, where you can enter a new genre.
Incidentally, the SQL View item on the menu and the SQL icon is interesting to explore. Click the icon and you'll see this complicated looking text:
SELECT tblAuthors.FirstName, tblAuthors.LastName,
tblBooks.BookTitle, tblGenres.BookGenres
FROM tblAuthors INNER JOIN (tblGenres INNER JOIN tblBooks ON tblGenres.ID
= tblBooks.Genre) ON tblAuthors.AuthorID = tblBooks.AuthorsID WHERE
(((tblGenres.BookGenres)=[Enter a Book Genre]));
This is the actual query. SQL stands for Structured Query Language. When you design your query using the Query Designer, selecting from all those dropdown boxes and entering your Criteria, that long text is what Access is really constructing under the hood. You could type a query here, instead of using the Designer.
OK, let's move on and see what Access Reports are. To explore query criteria in more depth, click the link below.
<-- Previous Lesson: Access Forms | Next Lesson: Access Reports Intro -->