Queries

Queries

In this article, we're going to go more in depth and talk about queries.

About Queries

If you want to review, add, or change data in your database, you can use a query. Using a query can make it quicker because you can ask specific questions about the data in your database that would be hard or too time consuming to answer by looking through your data. Queries can also filter data, perform calculations, and summarize data. In addition, they can automate data management tasks and review changes before you make those changes permanent. Of course, you can also use a query to supply data to a report.

Kind of Queries

There are many types and variations of queries in Access. Let's keep it simple so you get comfortable with queries and going more in-depth is as easy as putting Cheese Whiz on a cracker.

The types of queries we're going to talk about in this section are the types you'll find in the Query Wizard.

The types of queries listed are:

• Simple Query
• Crosstab Query
• Find Duplicates Query
• Find Unmatched Query

In this section, we're going to learn about these four queries and how to complete them using the wizard.

Creating a Simple Query

Queries are the "errand boys" of Access 2013. You ask them a question regarding your data, and they bring you the answer. 

As we said, the query is your errand boy. So, let's say that you want to find a certain type of data within a table. The tables we created as examples are simple tables that aren't overloaded with fields (columns) and rows (records), but in a real database, this wouldn't be the case.

Here's the table we're going to use:

As you can see, for our database, we've decided to create tables for books. Our database will store a book collection.

Now, let's say we want to see all the authors that we have in our collection, and that's the only data we want to see. If we had fifty columns of data, along with hundreds of rows, it could get overwhelming. To find the authors, we're going to run a query to find them.

To do this, click the Create tab on the ribbon. Next, click the Query Wizard.

Once you click the Query Wizard, this dialogue box will open:

Select Simple Query Wizard, then click OK.

Select "Table: Books" from the Tables/Queries drop-down box, if it's not already selected.

In the snapshot above, we selected Author from the left (it's no longer there), and pushed the top arrow button. That put Author in the selected field.

Click Next.

The next window (pictured above) asks us to name the query, then we can either open the query or modify the design. For now, let's just open the query. Click Finish.

You can now see your query. We already have an Author query, so maybe we'd have created a query to find page numbers in books, cover price, or any other information. This is just an example to show you how easy it is.

Your query now appears in the Navigation Pane:

Creating a Crosstab Query

A crosstab query takes rows (or records) of data and transforms them into columns. Let's show you how to do it so you can get a good visualization.

Once again, go to the Query Wizard by clicking the Create tab:

Click on Crosstab Query Wizard, then click OK.

Select a table or query (if you already have performed a query) that contains the fields that you want for the crosstab query.

If you select a table which has no data, you'll get this message:

This is very important for you to remember going forward. In order to perform a crosstab query, you must have a table or query with at least three numeric, date, or text fields.

So, we're going to select Books, just so you we can show you how to do a crosstab query.

Now we can select which fields we want to be row headings. We're going to select Title.

Select the field by clicking on it on the left, then click the top arrow. The field is now moved to the right.

Click Next.

Now we're asked to select column headings.

We're going to select Author, then click Next.

Now, you're asked to select the field that you want calculated in the query on the left. You're also asked to select a function from the right that will calculate and put data in the query.

We're going to select ISBN as the Field, and Count as the Function. 

Click Next.

Name your query, then leave the box checked to view it. Click Finish.

Above you'll see a picture of our query. You can see each titles ISBN number as it is in our Books table. You can also see the number of ISBNs under the corresponding author.

However, let's play with our Books table to show you even more what a crosstab query can do. Let's use the Price for each book in the Books table instead of an ISBN. Just keep in mind that these are real book titles and authors, but fictional prices.

Here's our table again:

Let's do another crosstab query. Our row headings and column headings will stay the same as they were in the other query. The only difference is in the screen where we select the Fields and Functions, we select Price and Avg.

Here's our results:

Creating a Find Duplicates Query

You can also use a query to find duplicate data in your tables. This makes normalization easier. To do this, go back to the Query Wizard and select Find Duplicates Query Wizard.

Click OK.

We've selected Books again since it is our only table at the moment. Click Next.

Now, we've selected Author for the field where we want to look for duplicate values.

Click Next.

If you want the query to show any other fields, pick those in this screen. Perhaps we wanted to see book titles beside duplicate authors. You don't have to select anything.

Click Next, then name the query.

Click Finish.

Below you'll see our query.

We can see the duplicate data, along with the titles as we requested to show alongside that information.

Creating a Find Unmatched Query

If you want to compare tables to identify records in one table that don't have any corresponding records in another table, you use an Unmatched Query.

Let's learn how to do this. Go to the Create Tab and click the Query Wizard. By now, you know exactly where that is.

Click Find Unmatched Query Wizard, then click OK.

Now, select the table that has unmatched records. For example (using our tables), if we had a list of books in our Books table, but there wasn't an order for that book in the Orders table, we would select the Books table.

Click Next.

Now, select the table that's related, and click Next.

Now, find fields that the two tables have in common as shown below. They may already be selected, as they are related. In this case, we would select the ISBN fields.

Click Next.

Now select the field that you want displayed in the query, such as ISBN, Author, and Title.

Click Next.

Name your query and click Finish.

This is our result:

This shows that we have orders for all books except for these listed here.

Sort & Filter

As you can clearly see, you can use queries to find data in your tables or other queries. However, you can also use Sort & Filter to find data. When you filter data, only the records with values that you specify will show. The rest of the data will remain hidden unless you remove the filter.

Access has filters that are built into every view for you. If you can use the filter or not depends on the type and values of the field. For example, you can't filter columns in datasheets or controls in forms that are bound to expressions. 

Let's say we want to view books in our Books table that cost a certain price. To do this, go to the Home tab. First, we're going to go back to our table and select the Price column.

Then, we're going to go to the Sort & Filter group on the ribbon and click Selection.

We want to see books that do not equal $25.00. (We could also click Between to specify a certain range.)

Here are our results:

Click Toggle Filter on the ribbon to see filtered or unfiltered results.

You can also use Access filters for a certain data type. These filters are menu commands in the following views:

• Datasheet
• Form
• Report
• Layout

You can use these filters:

Common filters filter for specific values or a range of values.

Filter by selection filters all rows in a table that match the selected value in a row. This filters the datasheet view.

Filter by form filters several fields in a form or datasheet, or if you are looking for a specific record. Click on Advanced in the Sort & Filter group to use this filter.

Advanced Filler is a filter type where you define filter criteria. Click Advanced on the ribbon, then Advanced Filter/Sort. We'll learn about criteria in just a few minutes.

Creating Simple Forms

A form is an Access database object that's used to create an interface for a database application. We know how difficult that sounds, but it's really not. A "bound" form is directly connected to your data, such as a table or query. It's used to enter, edit, or display the information from the data. You can also create an "unbound" form that doesn't link to a data source (such as a table or query) but still contains buttons, labels, or other controls that you need to operate the application.

Creating a Single Item Form

A single item form displays information about one record. Once you create the form, you can modify it in Layout or Design view.

Let's learn how to create it so you can see exactly what a report does and how easy it is to create one.

Go to the Navigation Pane in Access (you should have the database you created open). Click the table or query that you want to use to create the form. For this example, let's click the Books table.

Now, go to the Create tab. Go to the Forms group and click Form.

Access creates the form for you.

If you go to Layout view, you can edit the design while the form is still showing data. You can do things like adjust the size of text boxes.

Now, to use the form, go to Form view. To do this, go to the Home tab. In the Views group, click View, then Form View.

If you want, you can now change the data in your table by editing the form. Let's change the price to $25.00 for the first book ("Lying with Strangers") in the form. Click in the field labelled "Price" and type in the new value of 25.

Once you do that, you'll see a little pencil on the left hand side, as shown below.

To change the price in your data, click that little pencil. It will now turn into an arrow.

The price has now been changed in the table. Double-click on the Books table to open the table and see.

Navigating Forms

So far, we have seen forms that only show a single record at a time. However, if your table has more than one record, you are able to see them in the form.

At the bottom of the form, there is a toolbar that displays record information:

This section shows:

· The number of the current record, and the total number of records (for example, 1 of 6)

· Buttons for moving to the next and previous record

· Buttons for moving to the first and last record

· A button for creating a new record

· Filtering and search functions

To navigate between records, you have a few choices.

Click on the Next Record button, which is highlighted here:

The next record will be displayed, and the toolbar at the bottom will show the next record number.

To search for a record, type a value to search for in the Search box. In this example, we're going to search for "Belmont", which is part of a book title that we have.

Type the word "Belmont" into the search box at the bottom. It doesn't matter if it is upper or lower case.

As you type, you'll notice that the next available record starts to appear and the word is highlighted. The record for the book "A Death in Belmont" is now displayed.

Automatic Subdatasheet Creation

An automatic Subdatasheet sounds complicated, but it's really not. If Access finds a table that has a one-to-many relationship with the table or even the query that you used to create the form (remember, a query comes from a table), then it will add a Subdatasheet based on the table or query.

A one-to-many-relationship occurs when each record in one table may have many linked records in another table, but each record in the second table may have only one corresponding record in the first table. We have one of these already – our Orders and Books table.

Here's an example. If you create a form based on our Books table, as we did above, and a one-to-many relationship exists between the Books table and the Orders table, the Subdatasheet will display all the records that exist in the Orders table that relate to the current Orders record you're using to create the form. You can go to Layout view, if you want, and delete the Subdatasheet. However, if there's more than one table that has a one-to-many relationship, Access will not add anything to the form.

An easier way to understand what this is if you did a report for a single customer using the Customer table. Let's say the customer had placed more than one order that appeared in the Orders table. All the orders for the customer (which are linked records linked by primary keys and foreign keys) will be displayed in a Subdatasheet.

Creating Multiple Item Forms

A multiple item form is also known as a continuous form. It displays information from more than one record at a time. The data is arranged just as it is in a datasheet with rows and columns with multiple records being displayed. But because it's a form and not actually a datasheet, you can customize it more and add features like graphics, buttons, etc.

To create a multiple item form, go to the Navigation Pane and select a table or query. (We're selecting a table again.)

Go to the Create tab. Go to the Forms group and click Multiple Items. If you don't see that, click More Forms, then go to Multiple Items.

Here's our form:

Once again, go to the Home tab and switch to Form view to use the form.

To close a form, click on the "X" in the top right of the form window. If Access asks you to save the file, choose either Yes or No, depending on if you want to save the file.

Creating a Split Form

With a split form, you see two views of your data: the form view and the datasheet view. The views are connected to the same data source and are synchronized. If you select a field in one part of the form, the matching field in the other part of the form is selected too. Data can be added, deleted, or edited in either part as long as the record is updateable.

To create a new split form:

Go to the Navigation Pane and select a table or query. We're using a table, of course. Make sure it's open in the Datasheet view by double-clicking the table name to open it.

Go to the Create tab, select More Forms, and then choose Split Form.

The Split Form is now created. You can make updates in either pane and they will be updated in the other pane accordingly.

To create a split form from an existing form, open the form you want to use in Layout View. To do this, right click on the form in the Navigation Pane, then click Layout View.

View the Property Sheet by clicking on Design, then Property Sheet, or press F4.

Choose Form from the dropdown list that's at the top of the Property sheet.

Go to the Format tab on the Property sheet. Go to the Default View dropdown and select Split Form.

This message will appear.

Click OK, and then make sure you save the form if you haven't already.

Close the form, either by clicking on the X on the top right of the form window, or right clicking on the form title bar and selecting Close.

When you open the form again, you'll see it is now in Split View.

Creating a Navigation Form

A navigation form is just as it sounds. It creates a Navigation Control. If you're going to publish your database to the web, you'll need this type of form because the Navigation Pane won't show.

To create a navigation form:

Go to the Create tab. Go to the Forms group and click Navigation. Choose the type of form you want. We chose the Horizontal Tabs.

Drag the form or report that you want to add from the Navigation Pane to the tab that says Add New.

Below you'll see the navigation form that you created: