Quick Tutorial On Queries In Microsoft Access 2007

Preparation

We created two tables in the previous post. You’ll need to either do that for yourself again, open the one you saved last time, or follow along with a similar example of your own.

In any case, you need a table that looks a little like this”

Close the table, and we can start with the queries.

A Microsoft Access Tutorial – Query Basics

Queries are the second structure in Access. Tables hold the information, queries contain stored questions. Let’s create one. It’s much easier that way.

Click the Create tab, and then the Query Design button at the right hand end.

If you ever need help in building a query, you can also use the Query Wizard. But for now, we’ll take the direct route.

Access asks you which tables you want to ask questions about. First off, let’s just take a look at the Book table. We can add the Author table later.

he real power in Access is the ability to easily deal with multiple tables at once, but one step at a time.

Click on Book, and click the Add button. The window stays open, so click the Close button.

Access presents you with the query design page.

You can make some adjustments to the way the layout looks by dragging the central divider up or down, and there are shortcuts at the bottom right, in the status bar, that let you change the type of view you are using. More about those later.

The upper portion of the screen contains all of the included tables, with a list of the fields. The lower portion is where the questions are asked.

First, you need to choose which of the fields in the table you want to either ask questions about, or wish to include in the answer. To choose, double-click the field, or drag it to the grid below.

For our example we want to choose Author, Title & Rating.

Once you have the fields in the grid, there are a lot of choices to make. They work line by line.

We have already chosen the fields, and the tables are added automatically. The next thing is the sort. To sort the books by rating for instance, click in the sort box for that column, and change the setting to Ascending or Descending.

You can sort by multiple columns. The priority is from left to right, so if you wanted to sort byRating and then Title, you would need to rearrange the columns. You can just select by the grey bar at the top and drag them around.

The Criteria row is a little more complex, but it’s very easy to use once you get used to it. Criteria are specifications for which records (rows) from the table to show. And for the technical types reading, these are generally what is known as AND criteria. That is, all of the criteria need to be met. If instead you wish to use OR critera (that means that any of the criteria can bet met) then put the criteria on different rows. You can use as many rows as you wish from the one labelled Criteria downwards.

In our case, we want to only see books where the Title starts with “˜S’, and the rating is better than 2. The “˜S’ criteria also includes what is known as a wild card. That is, the title needs to start the letter S, but anything at all is permitted after that.

Numeric criteria are allowed to be defined as limits, rather than specific values, so in the case we can use the “˜>’ operator.

We could spend the whole day talking about criteria and wildcards, but let’s move on.

Now that the we have defined the question we wish to ask, we can pose it to Access, and view the answer. Click the View button in the ribbon or the datasheet view button in the status bar. You can flick back and forth between design and datasheet to make further changes to the query.

It’s important to note that as a general rule, the datasheet view from a query is live. That is, if you make changes to the query results then you make changes to the table data.

Finally, you can save the query for later. There is some confusion with this at times. Saving the query saves the question, not the answer. So that means that next time you run the query, if the data in the table has changed, then the answer might also change. There some other options to grab a snapshot of the data later on if necessary.

Click the Save button in the quick toolbar at the top left of the Access window. Remember that queries are saved along with the tables inside the one Access file on your hard drive.

You often need to connect tables together in queries. For instance in this case, we could add the Author table so that we can make use of the information in it for sorting or further criteria.

As it happens, the lookup that we set up for the Author table means that we already have access to the Author’s last name, but let’s just pretend we wanted to sort the output by the author’s first name instead. After all, these guys (or at least the few who are still alive) are friendly enough. Let’s call them Isaac and Robert, right? Oh, hold on. Those two are dead.

To make this work, add the Author table to the query.

While in Design View, click the Show Table button and add the Author table to the grid.

Because of the lookup that was set up, Access already knows how the tables are related, so you don’t need to worry about that. Drag the First Name field down into the criteria block, then drag it off to the left so you can sort it as a priority.

Click the Datasheet View button to see the difference.

A Microsoft Access Tutorial on Query types

The query we just built, the default type in Access, is called a Select query. It’s essentially a view of the answer to a question. The other types do a number of specific things that might be useful later. I won’t go into too much detail here, but some pointers might help.

Most of these other queries are what is known as Action queries. That is because they actually change data in tables. No changes are made until you click the Run button (the Datasheet view only previews the results) and you will be warned that changes are about to be made.

Update

An update query is used to make changes to the table data in one hit, rather than dealing with the records one by one. For instance, perhaps an author might change his name, or admit to having written a stack of books under a nom-de-plume. An update query would let you select the appropriate records and then change them all at once.

Make Table

A Make Table query works the same way as an Update, but puts the results in a new table. This might be useful where for some reason you need to maintain both sets of data separately.

Append

An Append query lets you select records from one table and add them to the end of another. The most common use for this is for archiving records from a main table to a secondary one.

Delete

A Delete query is extremely useful, but care needs to be taken with using it. This query lets you select some records from a table, and then delete them.

Other

The other types of query (Union, Cross-tab, Pass-through and Data Definition) are for advanced use, and I won’t cover these here.

That’s it for now, until I’m back with a post on Access Forms.

Let me know how it goes with queries, and whether there are any difficulties I can help with in the comments.

Learn MS Access in One Day....

Microsoft Access is a database software package. A database is an organized collection of records. Telephone and address books are examples of paper databases. With Access, you can create a computerized database. For example, you can use Access to organize the students who attend a school, the courses they take, and the instructors who teach them. After you create an Access database, you can search it, manipulate it, and extract information from it. This lesson introduces you to Access windows and teaches you how to create a database.

You use windows to interact with Access. To begin, start Access 2007. You screen will look similar to the one shown here.

Understanding Security

It is possible for an Access database to contain malicious code, such as a computer virus. Access has security settings that disable code and display a security warning when you open a database. If you know a database is trustworthy, you can perform the following steps to enable it.

To enable a database:

1. Click the Options button. The Microsoft Office Security Options dialog box appears.
2. Click Enable This Content.

The Ribbon

You use commands to tell Access what to do. In Access 2007, you use the Ribbon to issue commands. The Ribbon is located near the top of the Access window, below the Quick Access toolbar. At the top of the Ribbon are several tabs; clicking a tab displays related command groups. Within each group are related command buttons. You click buttons to issue commands or to access menus and dialog boxes. You may also find a dialog box launcher in the bottom-right corner of a group. When you click the dialog box launcher, a dialog box makes additional commands available.

Access Objects

To view or hide the objects on the Navigation pane:

• You click the double down-arrows  to view objects. The double down-arrows change to double up-arrows .
• You click the double up-arrows  to hide objects. The double up-arrows change to double down-arrows .

As stated earlier, the Navigation pane stores the objects in your database: tables, queries, forms, reports, macros, and modules. Objects always display with an icon to the right. The icon tells you the object type: table, query, form, report, macro, and module.

Objects
Tables	In Access, data is stored in tables. A table is a set of columns and rows, with each column referred to as a field. Each value in a field represents a single type of data. Each row of a table is referred to as a record.
Queries	You use queries to retrieve specific data from your database and to answer questions about your data. For example, you can use a query to find the names of the employees in your database who live in a particular state.
Forms	Forms give you the ability to choose the format and arrangement of fields. You can use a form to enter, edit, and display data.
Reports	Reports organize or summarize your data so you can print it or view it onscreen. You often use reports when you want to analyze your data or present your data to others.
Macros	Macros give you the ability to automate tasks. You can use a macro to add functionality to a form, report, or control.
Modules	Like macros, modules give you the ability to automate tasks and add functionality to a form, report, or control. Macros are created by choosing from a list of macro actions, whereas modules are written in Visual Basic for Applications.

Understanding Tables

A table is a set of columns and rows. Each column is called a field. Within a table, each field must be given a name and no two fields can have the same name. Each value in a field represents a single category of data. For example, a table might have three fields: Last Name, First Name, and Phone Number. The table consists of three columns: one for last name, one for first name, and one for phone number. In every row of the table, the Last Name field contains the last name, the First Name field contains the first name, and the Phone Number field contains the phone number. Each row in a table is called a record.

All of the data in a table should refer to the same subject. For example, all of the data in the Employees table should refer to employees, all of the data in the Students table should refer to students, and all of the data in the Courses table should refer to courses.
You can view an Access database as a collection of related tables. For example, in a database that contains tables for Employees, Students, and Courses, the Employees table lists the employees, the Students table lists students, and the Courses table lists the courses students can take.
After Access creates a blank database, it opens in Datasheet view and makes available the tools you need to create a table. Datasheet view displays a table as a set of columns and rows. When you view a blank database for the first time in Datasheet view, you see a column named ID. This column is by default the primary key field.
A primary key is a field or combination of fields that uniquely identify each record in a table. No two records in a table should have the same values in every field. For example, the following should not occur in a table.

Last Name	First Name	City
Smith	John	Jonestown
Smith	John	Jonestown

In the real world, it is possible to have two people from the same city with the same first and last name. In cases like this, you can use the ID field as the primary key field and use it to make each record unique. The ID field has a data type of AutoNumber; as a result, Access automatically creates a unique number for each record in the database. The resulting table will look like the one shown here.

ID	Last Name	First Name	City
1	Smith	John	Jonestown
2	Smith	John	Jonestown

Access provides several methods for creating a table. One method is to use the Rename option with the Add New Field column label to give each column the field name you want it to have and then to type or paste your data into the table. Field names can include letters, numbers, and spaces and can be up to 64 characters long. When choosing a field name, try to keep it short.
When you save your table for the first time, Access gives you the opportunity to name your table. Each table name must be unique; hence, two tables in the same database cannot have the same name. The table name should describe the data in the table; can consist of letters, numbers, and spaces; and can be up to 64 characters long. When choosing a table name, try to keep it short.
You can save a table by clicking the Save button on the Quick Access toolbar or by right-clicking the Tables tab and then choosing Save from the menu that appears.

To add fields to a table:

1. Click the Add New Field column label.
2. Activate the Datasheet tab.
3. Click Rename in the Fields & Columns group.
4. Type the field name.
5. Press Enter. Access creates the field.
6. Type the next field name. Access creates the field. Continue until you have created all of the fields in your table.
7. Press Enter without entering a field name to end your entries.

Or

1. Right-click the Add New Field column label. A menu appears.
2. Click Rename Column.
3. Type the field name.
4. Press Enter. Access creates the field.
5. Type the next field name. Access creates the field. Continue until you have created all of the fields in your table.