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:

The Query Design item on the Create ribbon in Microsoft Access

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:

The Query Design with the Show Tables dialog box

Select the tables you want, which in our case are all three tables. Click the Add button:

The Show Tables dialog box in Access

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 Query Designer in Access

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:

Arrows showing how to adjust the column width and Designer Height in Access

But click the dropdown box in the first column, where it says Field:

The Field dropdown list in the Query Designer with a list of table columns to choose from

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:

The Query Designer with the First Name field selected in the first column

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:

The Access Query Designer showing three fields selected

We want to discover how many books we have in the Romance genre. To do that, select tblBGenres.BookGenres for the fourth column:

The Access Query Designer showing a fourth field selected

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):

A criteria added to the Query Designer for the fourth column

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:

The Run query item selected on the Results panel of the Access Design ribbon

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:

A query added under the Queries heading in the All Access Objects pane

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:

The right-click context menu showing Design View selected

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:

The right-click context menu showing Copy selected

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:

The Paste As dialog box in Access

You should now have two queries:

Two queries showing under the Queries heading in All Access Objects

Right-click the new query and select Design View from the menu:

Right-click context menu with Design View selected in Access

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):

A new criteria typed into the Access Query Designer

Run your query, just like you did before. This time, you should see a popup box. This one:

The empty input box asking for a book genre

The text you entered between square brackets is above a text box. Type a genre into the text box. Type Thriller:

A parameter value entered into an input box in Access

Click OK to run the query. Access will display the results:

Results from an Access query returned using a parameter value

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:

Getting back to Design View in Access by right-clicking a query tab

You can also click the Design View icon in the bottom right of the screen:

The Design View icon in Access

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.

<--Back to the Microsoft ACCESS Contents Page


Email us: enquiry at homeandlearn.co.uk