Welcome and Introduction

Welcome to Access 2016: Analyzing & Modifying Data with Queries.

What You Should Already Know

You should have already attended Access 2016: Structuring & Relating Data or have the equivalent skills. Specifically, you should be able to:

• understand database relationships
• create one-to-many relationships
• create simple relational queries

What You Will Learn

This workshop introduces a broad range of features available in Microsoft Access queries and provides hands-on practice on how to:

• build relational queries and modify query properties
• perform mathematical and text operations in a query
• create parameter queries that allow you to supply criteria when run
• summarize data using groups, calculated expressions, and aggregate totals
• create Crosstab queries to summarize data
• create and run Action queries to create, modify, and delete data
• use Find Duplicates and Find Unmatched queries to locate data

What You Will Need to Use These Materials

To complete this workshop successfully, you will be provided with:

• the use of Microsoft Access 2016
• the exercise files in the Access_AMD folder: UofMW.accdb and EngCourses.xlsx

Getting Started

These materials presume you will begin work from the desktop, and have any required exercise files located in an epclass folder there.

Getting the Exercise Files

Most of our workshops use exercise files, listed at the bottom of page 5 of the materials. In our computer-equipped classrooms, these files are located in the epclass folder, which should already be on the computer desktop. If you are using our materials in a different location, you may obtain the exercise files and detailed installation instructions from our Web site at:

http://ittraining.iu.edu/downloads/

Once you are logged on and have the needed files in an epclass folder on your desktop, you are ready to proceed with the rest of the workshop.

Finding Help

If you have computer-related questions not answered in these materials, you may look for the answers in the UITS Knowledge Base, located at:

http://kb.iu.edu/

Want to Learn More?

IT Training offers many training options for extending your skills beyond this workshop:

• Self-study training: You may access computer-based training from leading commercial vendors, obtain the materials for other IT Training workshops, or access IT Training's own tutorials on a range of topics at:

http://ittraining.iu.edu/online/

• IT Training Tips weblog: Our award-winning blog has short tutorials and training posts on a broad range of topics and technologies. It's written by training and support providers from across the entire IU system; you may read and comment on these at:

Today's Project

Today we will be working with a database that contains information for managing student enrollments at the University of the Midwest.

Our database holds accurate and timely information about the departments, faculty, courses, students, and their enrollments. This information is of little value to us unless we are able to analyze the information and answer questions about it. To examine just the information that we want and to relate it in a meaningful manner, we rely on the use of queries within Access.

We will query the University of the Midwest database to retrieve customized data sets and summarized data sets. We need to be able to see things like which classes students are taking, who is teaching the classes, and where classes are being held. Access queries will help us to sort, filter, summarize, analyze, update, append, and delete the information in our tables.

Exploring the Database

Let's look at the University of the Midwest database we will use in today's workshop. We will launch Access and then open and explore the database.

Step 1.Launch Access.

Step 2.To begin opening an existing database, in the left pane,

Click Open Other Files

Step 3.To continue,

Click

You see the Open dialog box.

We need to specify the name and location of the file to open.

Setting the Location for Opening Your File

When the dialog box opens, it lists a default location from where the file will be opened. All of our exercise files are contained in the epclass folder, located on the desktop. We'll want to change our location to this folder.

We will start at the desktop, since our exercise file folder, epclass, is located there.

Step 1.To move to the desktop,

Click

The current location is now set to the desktop. All of our exercise files are contained in the epclass folder, located on the desktop.

Step 2.To open the epclass folder,

Double-Click

The epclass folder contents are now visible.

We will need to open the Access_AMD subfolder of the epclass folder that contains all of the necessary documents.

Step 3.To open the correct folder,

Double-Click the Access_AMD folder

Step 4.To open the database file,

Double-Click UofMW.accdb

Step 5.To enable the content,

Click

NOTE: Once a file has been opened and enabled from a specific location, Access will consider this location a trusted source and will not show this warning again. For more information regarding trusted documents, refer to the Access online Help documentation.

If you are opening this database from an untrusted network location, you will see the following dialog box:

You can choose to make this document trusted, which will mean that Access will not warn you about enabling content every time you open the database. In general, you should only make documents trusted if they have come from someone you trust, and you were expecting the file. Otherwise, click No.

Step 6.To make the file a trusted document, if necessary,

Click

Now that we have our database open in Access, we will review the tables and relationships.

Viewing Tables

There are twelve tables in the University of the Midwest database: tblCourses, tblDepartments, tblEnrollments, tblFaculty, tblGradeScale, tblLocations, tblMeetingTimes, tblMeetingDays, tblRoles, tblSections, tblSectionsInstructors, and tblStudents. Let's take a moment to look at some of these tables so we have a clear idea of what information they contain. In particular, we will look at the tables we added between Access 2016: Structuring & Relating Data and today's workshop.

Step 1.To view the Students table, in the Navigation pane,

Double-Click tblStudents

The Students table collects information about the students including student_id, first_name, last_name, email, phone, address, academic program, and enrollment date.

Access automatically creates subdatasheets when the table is in a one-to-one relationship or the table is on the "one" side of the relationship and the table's SubdatasheetName property is set to Auto.

Step 2.To view a subdatasheet, next to any record,

Click

The subdatasheet is displayed for the record:

Each enrollment from tblEnrollments for that student is displayed. If the student has no enrollments, you still see the headings for all the columns in tblEnrollments.

NOTE: If a table is on the "one" side of multiple relationships, instead of seeing a subdatasheet, the Insert SubDataSheet dialog will open. This dialog box allows you to pick what data you want to see in the subdatasheet. To learn more about subdatasheets see:

http://go.iu.edu/1mvG

Step 3.To open the Enrollments table,

Double-Click tblEnrollments

This table shows the enrollments and grades for every course a student is enrolled in. Notice that the student enroll in a section of a course rather than directly in a course, since a larger course can have many different sections in which it is offered.

Step 4.To open the Grade Scale table,

Double-Click tblGradeScale

This table is used to translate the letter grades in enrollments into point values. We will use this later when calculating GPAs for students.

Step 5.To open the Locations table,

Double-Click tblLocations

This table contains information about locations on campus and includes information like room number, capacity, type of room, and building information. You might notice that the building information is repetitive and thus not normalized. We will fix this in a later exercise where we will split the building information off into its own table.

Step 6.To open the Meeting Days table,

Double-Click tblMeetingDays

This table contains the days when classes meet.

Step 7.To open the Meeting Times table,

Double-Click tblMeetingTimes

This table contains the times when classes meet.

Step 8.To close all the tables,

Right-Click any tab, Click Close All

The tables are closed.

Viewing Relationships

The structure of the University of the Midwest database includes tables that allow us to track information about the classes offered at the University of the Midwest. Because all of these table are related, we can ask Access to display in one datasheet information about what department a class is in, who is teaching the class, where the class is located and which students are enrolled in the class.

When working with a relational database, it is important to understand the relationships that exist between the tables. Let's open the Relationships window to view how the tables are related.

Step 1. To see the available database tools, on the Ribbon,

Click the Database Tools tab

Step 2.To view the table relationships for the database, in the Relationships group,

Click

The Relationships window appears for the UofMW.accdb database. Note the symbols indicating one-to-many relationships with referential integrity established. All our relationships are one-to-many relationships. The following table summarizes the relationships shown in the Relationships window:

Step 3.To close the Relationships window,

Right-Click the Relationships tab, Click Close

As we have seen in the previous workshops in this series, queries are questions we ask about data. In a relational database, the information necessary for answering a question can be located in different tables. Since relationships have been established between tables, we can write queries that return information from one or more tables.

Working with Criteria

Since we worked with queries in both Access 2016: The Basics and Access 2016: Structuring & Relating Data, we will start by editing some existing queries. When we are revising these queries, we want to be sure that we don't save over the query we are starting with, so before we start working with criteria, let's make it easier to save queries under a different name.

Adding the Save As Command to the Quick Access Toolbar

To make saving queries under a new name easier, we will add the Save As command to the Quick Access toolbar.

NOTE: If you worked through the previous Access workshop materials, the Save As command may already be on the Quick Access toolbar. If so, skip to the next section.

Step 1.To view available commands, on the Quick Access toolbar,

Click , Click More Commands...

Step 2.To find the Save As command, in the pane on the left, if necessary,

scroll down

Step 3.To add the Save As command,

Double-Click the Save As icon

Step 4.To close the dialog box,

Click

The Save As icon is now available on the Quick Access toolbar.

Saving an Existing Query Under a New Name

Now that we have the Save As button on the Quick Access toolbar, let's use it to save the query under a new name.

Step 1.To open an existing query,

Double-Click qrySalariesByRank>70,000Hired2000OrLater

We see 34 records. The current criteria limits the returned records to faculty who are making more than $70,000 and were hired on or after January 1, 2000.

We will switch into Design View before we save it under a new name, because there is currently a bug in Access that sometimes causes problems if you attempt to save it with a different name from Datasheet View. When this happens, the name of the query will change on the tab but the new query won't appear in the object list. When you try to switch to Design View, you see an error message similar to this:

2.To switch to Design View,

Click

Step 3.To save the query, on the Quick Access toolbar,

Click

We see the Save As dialog box.

Step 4.To name the query, in the Save As dialog box, type:

qrySalary>70,000or<40,000Hired2000orLater,
Click

The query is now saved under the new name. Next we will make changes to the query.

Using And Criteria and Or Criteria

We want to alter the query to also see faculty who are making under $40,000 who were hired on or after January 1.

The criteria currently looks like:

In Access 2016: The Basics, we learned that when we put the criteria in the same row, we are creating an AND query, meaning that both conditions must be true for records to be returned.

In Access 2016: Structuring & Relating Data, we learned that we can use the "or" row to include additional criteria. If we put the <40000 in the Salary field "or" row, we would be creating an OR query that displays people making less than $40,000 regardless of hire date. While we could add the hire date to that second row, there is another way we can create the OR criteria we want in a single row.

We want to combine the criteria for the salary, so we find faculty making more than $70,000 or less than $40,000. In addition to using the grid to create an AND query and an OR query, we can also use the logical operators AND and OR. Logical operators are operators whose results are either true or false.

Step 1.To alter the criteria,

Click after ">70000", type: or <40000 Enter

Since both the criteria for salary are on the same row, the criteria for the date in the HireDate field will apply to both faculty making less than $40,000 or more than $70,000.

Step 2.To view the results,

Click

You see a list of faculty earning less than $40,000 or more than $70,000 who were hired on 1/1/2001 or later.

Step 3.To save the changes, press:

Control key+s

Step 4.To close the query,

Right-Click the query tab, Click Close

NOTE: You can also close a query with the keyboard shortcut Control key+w.

Using NOT

Sometimes it's easier to describe what we want to exclude from our results rather than what we want to include. The NOT logical operator allows us to do this.

Let's revise an existing query to see how NOT works.

Step 1.To open an existing query,

Double-Click qryCoursesAndSectionsSummer18

We see 49 records.

Step 2.To switch to Design View,

Click

The query currently selects CourseCode, SectionNumber and CourseTitle and SemesterYear. The criteria for SemesterYear limits the records returned to SU18.

Before we make changes to our query, let's save it under a different name so we don't accidentally overwrite the query.

Step 3.To save the query, on the Quick Access toolbar,

Click

The Save As dialog box appears.

Step 4.To name the query, type:

qryCoursesAndSectionsNotSU18 Enter

Step 5.To position the cursor, in the SemesterYear Column,

Click before "SU18"

NOTE: If you have trouble doing this, you can also Click in the SemesterYear criteria row and then press the Home key.

Step 6.To alter the criteria, type:

Not Enter

When this query was only showing courses in the SU18 semester, we didn't need to see SemesterYear column. Now that we will be seeing courses in multiple semesters, we want to include SemesterYear in the results.

Step 7.To show the field, in the SemesterYear field,

Click the Show checkbox

The criteria in the grid now is:

8.To view the query,

Click

We now see the 178 courses that are not occurring in the SU18 semester. They're sorted by semester and section number since that is the underlying order in the source data.

Step 9.To save the changes, press:

Control key+s

Step 10.Close the query.

Using Wildcards to Define Criteria

Access allows us to use wildcard characters when we want criteria to be more flexible. The wildcard character in criteria means "anything can go here." The wildcard character is the asterisk (*).

Let's open another query that was created previously and edit it to use wildcards.

Step 1.To open the query,

Double-Click qryDeptCoursesFA18NotOnWednesday

To save us time, this query has been partially created. Currently it allows us to view the courses for each department. We want to alter this query to only show classes in the Fall 2018 semester that don't meet on Wednesdays.

Right now the query results show 227 records with information about the Department Name, Semester Year, Section Number, Course Code, Course Title, Meeting Day, and Meeting Time of the courses.

In the previous workshops and with the queries we have created up to this point, when we wanted to look for an exact match on one field, we simply typed what we were looking for in the Criteria part of the column.

Step 2.To go to Design View,

Click

To start with, we would like to see only classes that are in the FA18 semester and meet on a Monday.

Step 3.To enter the criteria, in the SemesterYear Criteria field, type:

FA18 Enter

This is similar to the criteria we have entered previously. Let's use the wildcard to add criteria.

Step 4.To enter the new criteria, in the MeetingDay Criteria field, type:

m* Enter

NOTE: The criteria is not case sensitive.

You should see:

Notice that the criteria looks different than what you entered. Access added the keyword LIKE and the quotes around the m* to convert the criteria to official Access syntax.

Step 5.To view the query,

Click

We see the 50 courses in the Fall 2018 semester that have a meeting on a Monday. Note that some meet Mondays and Wednesdays, some meet Mondays, Wednesdays, and Fridays, and others meet on Monday only, but they all have a Monday meeting.

What if we wanted to see the classes that didn't meet on a Monday? Earlier we learned about the NOT operator. Let's combine the NOT operator with the existing criteria.

Step 6.To return to Design View,

Click

Step 7.To position the cursor,

Click to the left of Like

Step 8.To change the criteria, type:

Not Spacebar Enter

You now see Not Like "m*" in the Criteria field under MeetingDay.

Step 9.To view the query,

Click

There are 39 sections of classes that don't meet on a Monday.

Step 10.To return to Design View,

Click

What if we want to see the classes that don't meet on Wednesdays? Values of Meeting Day for classes that have a meeting on Wednesday include the following:

Notice that the "W" occurs in different positions. To exclude all courses that have a meeting on Wednesdays, we need to use the wildcard twice: once at the beginning to indicate there can be text before the "W", and a second time to indicate there can be text after the "W."

Step 11.To select the current criteria for Meeting Day,

Press & Drag across Like "m*"

Step 12.To change the criteria, type:

*w* Enter

Step 13.To view the query,

Click

We see the 39 courses in the Fall 2018 semester that do not meet on a Wednesday.

Step 14.To save the changes, press:

Control key+s

Next, we will learn how to adjust the display of the results.

Using Query Operators to Combine Data

Our query could display the meeting days and meeting times more efficiently. The appearance and readability of this information could be enhanced by combining meeting day and meeting time together in a single field.

So far, we have built queries by selecting fields from tables and adding them to the query design grid. But we are not limited to displaying only fields that already exist in tables. We can create new fields in the design by building an expression. An expression is essentially an equation that describes the information we want to display.

Step 1.To view the query in the query design grid, on the Ribbon,

Click

The query design grid should look like this:

Let's join the meeting day and meeting time into a single new field called Meeting.

We want to enter the correct syntax so that the Meeting column will be a combination of information from the MeetingDay and MeetingTime fields. However, since there is limited space in the field name row and it is really easy to misspell or mistype the field names, we will use the Expression Builder, which helps us create expressions.

Step 2.To open a contextual menu, in the query design grid, in the empty column to the right of MeetingTime,

Right-Click in the Field row

3.To open the Expression Builder,

Click Build...

NOTE: If Build is grayed out, click on another field, then right-click the Field row of the empty column again.

We see the Expression Builder:

The expression we build goes in the Expression field at the top of the dialog box. While we could type an expression, we'll use the Expression Categories to help us.

Step 4.To add the MeetingDay field to the expression, in the Expression Categories list,

Double-Click MeetingDay

NOTE: If the name of a field we are using in our expression is included in multiple tables selected as part of the query, we would need to specify both the table name and field name.

Next, we are going to use the concatenation operator to combine MeetingDay and MeetingTime into one field. The concatenation operator joins two or more pieces of text. The concatenation operator in Access is the + (plus) sign.

Step 5.To add the concatenation operator, after [MeetingDay], type:

+

Next we will add a space to the results. However, we cannot simply type in a space if we want it included in the results. To make this work, we must enclose the space inside of quotes.

Step 6.To add a space after [MeetingDay], type:

" "

NOTE: Be sure to type a space between the quotes.

Now we will add the second concatenation operator.

Step 7.To add the concatenation operator, after the quotes, type:

+

You see:

8.To add the MeetingTime field to the expression, in the Expression Categories list,

Double-Click MeetingTime

The final expression is:

[MeetingDay] +" "+ [MeetingTime]

This tells Access to concatenate the data in the MeetingDay field with a space and the data in the MeetingTIme field.

Step 9.To complete the expression,

Click

We see Expr1: [MeetingDa in the column after MeetingTime. We can expand the column in order to see the entire expression.

Step 10.To position the cursor to resize the column,

Point to the right border of the Expr1 column

You see the cursor change shape:

11.To expand the Expr1 column,

Double-Click between the border between the column headers

Step 12.To view the query results, on the Ribbon,

Click

Step 13.To see the Expr1 field, scroll right, if necessary.

Now that we are in Datasheet View, we see the new column called Expr1 after the Meeting Days and Meeting Times columns. The Expr1 column contains the values of the MeetingDays and MeetingTimes fields separated by a space, but the column needs a more descriptive title.

Next we will change the caption for the new column.

Renaming the Concatenated Field

The caption for the column, "Expr1" is generic and is not descriptive of the data. We can change this caption in Design View by placing specific text at the beginning of the expression, followed by a colon.

Let's see how this is done.

Step 1.To return to Design View,

Click

Step 2.To select the current field name, in the concatenated field,

Double-Click Expr1

NOTE: Do not remove the colon since it is needed to separate the caption from the expression.

Step 3.To replace the field label, type:

Meeting Enter

The field now has a more readable and informative label.

Step 4.To view the query result, on the Ribbon,

Click

Step 5.To see the Meeting field, scroll right, if necessary.

Step 6.To save the changes to the query, press:

Control key+s

Next we will return to Design View and remove the MeetingDay and Meeting Time columns from the query output.

Hiding and Deleting Columns

We no longer need to see the individual MeetingDay and MeetingTime columns since we now have a column that combines those fields. However, if we delete the MeetingDay column, we would need to keep the criteria. In order to do this, we would need to move the criteria to the new Meeting column. In some cases, if we move the criteria to another column, it may need to be edited and this means extra work for us. Instead, let's hide it from the results and leave the criteria as is.

Step 1.To return to Design View,

Click

Step 2.To hide the Meeting Day field,

Click to deselect the Show field checkbox

As for the MeetingTime field, there is no reason to keep it in the query. We aren't using it to limit the records that are included in the results of the query and the data in it is now redundant.

Step 3.To begin selecting the MeetingTime column,

Point to the top of the MeetingTime column

You should see a black, down-pointing arrow when you are in the correct location:

Step 4.To select the MeetingTime column, when you see the black down-point arrow,

Click the top of the MeetingTime column

The MeetingTime column is selected.

Step 5.To delete the column, press:

Delete key

The MeetingTime field is deleted.

The query design grid looks like this:

Next we will save the query. Because this is essentially the same query as before, just with a better display of the meeting day and time, we can just use the regular save command this time.

Step 6.To save the query, press:

Control key+s

Step 7.To view the results,

Click

The criteria is still applied but the fields we didn't need to see are now not included in the results.

Challenge Exercise: Concatenating Additional Fields

Let's make an additional refinement on our own. Let's combine the SemesterYear and SectionNumber fields into one field called SemYearSect.

Step 1.Edit the query to create a column called SemYearSect that combines the SemesterYear and SectionNumber columns.

The final results will look something like:

2.Save the changes.

Step 3.Close the query.

NOTE: To view a solution for this Challenge Exercise exercise, see Appendix 1: Solution Challenge Exercise: Concatenating Additional Fields on page 104.

Using Parameter Queries

In previous examples, you have seen how to enter criteria directly in the query design grid to limit a query's results. Parameter queries provide a more dynamic way to limit results. They prompt the user for the criteria at the time the query is run. The results will match that of the specified criteria.

Creating a Parameter Query

Suppose we want to limit query results to faculty of a specific department. We could get the desired information by entering criteria in the Criteria field of the query design grid. However, the query would be more flexible if we could enter criteria at the time of execution.

Setting a Single Parameter

We already have a query that lists all faculty by department. We will begin by modifying that query to include parameter criteria so the user can specify a department as they run the query.

Step 1.To open the query,

Double-Click qryAllFacultyByDept

You see 124 records.

Step 2.To switch to Design View,

Click

Step 3.To start to save the query with a different name, on the Quick Access toolbar,

Click

Step 4.To name and save the query, type:

qryFacultyDeptRankParams Enter

The query is saved with the new name, which is displayed on the tab.

Step 5.To specify the appropriate criteria, in the DeptName column,

Click the Criteria field,

type: [Enter Department] Enter

NOTE: The square brackets are re quired, and periods are not allowed when entering parameter criteria.

Step 6.To view the query result,

Click

You see the Enter Parameter Value dialog box:

Step 7.To enter a Department name, type:

Biology Enter

NOTE: The text is not case sensitive.

You see a list of the 34 faculty in the Biology department.

Step 8.To return to Design View,

Click

Step 9.To run this query specifying other departments such as Mathematics or Philosophy,

repeat steps 5 through 7 for other departments

Step 10.To save the changes we made to the query, press:

Control key+s

The changes we have made are saved. Now let's create a query using multiple parameters.

Using Multiple Parameters

Suppose we want to see Biology faculty of a certain rank. We will have two criteria parameters. We can place expressions in multiple fields or place multiple expressions in the same field. Access will prompt the user for the first criteria in the query design grid, then prompt for the second criteria. The query then returns data that matches the specified criteria.

Let's make changes to the design of qryFacultyDeptRankParams to allow for an additional parameter.

Step 1.Return to Design View, if necessary,

Step 2.To begin adding a second parameter, in the Rank column,

Click the Criteria field

Step 3.To enter a parameter criteria function, type:

[Enter Rank] Enter

Step 4.To view the query result,

Click

The parameters are displayed from left to right so the first prompt is for the department.

Step 5.To enter a department, type:

Mathematics Enter

We are prompted to specify a rank.

Step 6.To enter a rank, type:

Professor Enter

The query returns the 7 records of Mathematics faculty who are professors. We can use this query at any time to look for faculty from a certain department and of a specific rank.

Step 7.To save the changes to the query, press:

Control key+s

The changes are saved.

Using Wildcards in Parameter Queries

Earlier we used the asterisk wildcard in the Criteria field to create more flexible criteria. We saw how to narrow the results to those that meet on a certain day of the week or don't meet on a certain day of the week.

In our example, let's say that we are looking for faculty of a specific department but we don't want to type the whole department name. Because wildcards behave differently when used with parameter criteria, we cannot simply substitute an asterisk for the department. Access would treat the asterisk literally and would search for records where the department included an asterisk.

Access does provide syntax for enabling the use of wildcards in parameter queries. Let's see how to modify the expression in the Criteria field to allow the use of wildcards in the parameter query.

Step 1.To return to Design View,

Click

Step 2.To position the cursor, to the left of the parameter criteria, in the DepartName column,

Click the Criteria field

We see the cursor blinking to the immediate left of the criteria.

Step 3.If necessary, to ensure that the cursor is positioned at the beginning of the Criteria field, press:

Home key

Step 4.To use the LIKE operator with your criteria, type:

Like Enter

NOTE: Adding the LIKE keyword is not case sensitive, i.e. "Like" is the same as "like." Access will change the case automatically.

The parameter criteria reads: Like [Enter Department].

Step 5.To set wildcard criteria for the Rank column,

repeat steps 2 through 4

The parameter criteria in the Rank column should read: Like [Enter Rank].

Now when the query runs, we won't have to enter the full value for either department or rank; we can put in just the beginning of the value.

Step 6.To view the query result,

Click

We see a dialog box telling us to "Enter Department." Let's look for departments that start with the letter "p."

Step 7.In the dialog box, type:

p* Enter

We see a dialog box asking us to "Enter Rank." Let's look for ranks that start with the letters "as."

Step 8.In the dialog box, type:

as* Enter

NOTE: You can also mix and match when using the asterisk. For example, you can look for all of the Mathematics faculty by entering "Mathematics" in the Department prompt and by entering an asterisk in the Rank prompt.

NOTE: The * wildcard character will not return null values (that is, empty fields). To see null values in a parameter criteria query, in Design View, add the text Or Is Null after the parameter. For example: Like [Enter the Rank] Or Is Null.

We see the 36 records for the associate professors and assistant professors in the Philosophy and Political Science departments.

Step 9.To return to Design View,

Click

Step 10.To save the changes, press:

Control key+s

The changes are saved.

Using Multiple Parameter Criteria on a Single Field

Queries can be used to search for data where the criteria cover a range of information, like a specific time period or price range. When we want to specify a range, we can combine parameter expressions with operators like this:

>=[Enter a start date] And <[Enter an end date]

In this case >=, <, and And are operators.

Let's try using this method to search for faculty hired within a certain date range.

Step 1.Return to Design View, if necessary.

As we are going to remove the previous criteria, let's save this as a new query.

Step 2.To start to save the query under a new name,

Click

Step 3.To name the new query, type:

qryFacultyHireDateRangeParam Enter

The question we now want to ask deals with Faculty hire date considerations. Since we don't need the previous criteria for the Department and Rank columns, we will delete them before we add the criteria to the HireDate column.

While we could select and delete each of the existing criteria, since we want to remove all the current criteria, let's use a short cut.

Step 4.To position the cursor,

Click anywhere in the Criteria row

Now that our cursor is positioned, let's use a keyboard shortcut to select the criteria. Then we'll delete it.

Step 5.To select and delete the criteria, press:

Control key+a, Delete key

The Criteria fields for all columns are cleared. Next we will add the HireDate to the query and add criteria to it.

Step 6.To add the HireDate field, from the top panel, in tblFaculty,

Double-Click HireDate

While we could type in the Criteria field, the small space limits our ability to see the entire criteria as we type. Access provides the Zoom tool to allow a larger window for viewing the cell's contents.

Step 7.To open the Zoom window,

Right-Click the HireDate Criteria field, Click Zoom...

NOTE: If Zoom is grayed out, click another field and then right-click the Criteria row of the HireDate column again.

The Zoom window opens, providing a larger work space for typing the criteria.

Step 8.To create a parameter query to view faculty hired in a specific date range, type:

>=[Enter a start date] And <[Enter an end date] Enter

Step 9.To view the query result,

Click

We see the Enter Parameter Value dialog box.

Step 10.To enter a start date, type:

1/1/2001 Enter

Step 11.To enter an end date, type:

1/1/2006 Enter

We see the 21 members of faculty who were hired between 1/1/2001 and 1/1/2006. Let's save the changes to this query.

Step 12.To save the changes, press:

Control key+s

Step 13.Close the query.

Challenge Exercise: Adding Parameters to a Query

Let's add parameters to another existing query to allow us to see the professors teaching courses in a specified semester.

Step 1.Open qryCoursesSectionsAndAllFacultySummer18.

Step 2.Switch to Design View.

Step 3.Save it as "qryCoursesSectionsAndAllFacultySemesterParameter."

Step 4.Add the required parameter to the SemesterYear field.

NOTE: If you keep the Or is Null, which is part of the current criteria for SemesterYear, the results will include the faculty for the semester entered and all faculty who are not assigned to teach in any semester. This also means the query can be run with no parameter value entered, which will only show faculty who aren't assigned to teach in any semester.

Step 5.Save and test the query.

Step 6.Close the query.

NOTE: To view a solution for this Challenge Exercise exercise, see Appendix 2: Challenge Exercise: Adding Parameters to a Query on page 104.

Summarizing Data with Totals

Queries can summarize and display information by groups. A group is a collection of related records. A sales database could group all orders by each customer. A property database could group all equipment by manufacturer. Our database could group all faculty by department.

Grouping then allows further analysis of the data. For example, we could total, average, or count some value for each group.

Understanding Totals and Groups

We will create a query that will show the total number of faculty members in each department.

Step 1.To start to create a new query, on the Ribbon,

Click the Create tab, Click

We see the Show Table dialog box. To see the total number of faculty in a department, we need the Departments and Faculty tables.

Step 2.To add required tables to the query design grid, in the Show Table dialog box,

Double-Click tblDepartments,
Double-Click tblFaculty

Step 3.To close the Show Table dialog box,

Click

We see the Departments and Faculty tables in the query designer. We can now select the necessary fields for the query. Since we want to gather information about each department, we will add the DepartName field to the grid.

Step 4.To place the DeptName field in the query, from tblDepartments,

Double-Click DeptName

We will use the EmployeeID field to count faculty for each department since it has a unique value for each faculty member.

Step 5.To add the EmployeeID field to the query, from tblFaculty,

Double-Click EmployeeID

We see two fields in the query design grid. We are ready to calculate totals.

Step 6.To turn on the Totals function, in the Show/Hide group of the Ribbon's Design tab,

Click

We see a new row labeled Total in the query design grid. Notice the default setting of Group By in each cell of the Total row. We will leave that setting in the DeptName column because we want to group by the DeptName field. We will change the Total row value in the other column, EmployeeID, to indicate a calculation we want Access to make in returning these records.

Using Totals in a Query

Let's look at the ways Totals can summarize our data. Access allows the choice of a number of options and aggregate functions in the Total row. Aggregate calculations are predefined operations performed on groups of records. They can provide totals, counts, averages, and other information about field values in all records or in groups of records. Following is a list of the available aggregate functions:

Using the Count Function

The Count function returns the number of unique items in each group. So in our example, if we group by department and choose the Count function in the EmployeeID column, the query will group all faculty by their department and count them. Let's use the Count function to count the employee IDs in each group.

Step 1.To see the various functions,

Click the EmployeeID Total row

We see a drop-down arrow.

Step 2.To see the available aggregate functions, in the Employee ID Total row,

Click

We see the list of functions.

Step 3.To select the Count function, on the drop-down list,

Click Count

The query design grid defines the group as Department and the count as EmployeeID.

Step 4.To view the query result,

Click

You see:

NOTE: The departments are in alphabetical order, but not because we sorted them. They are actually in order by department code, which is the structure of the underlying data.

Step 5.To see the entire label in the first column,

expand the first column of the Datasheet

Step 6.To see the entire label in the second column,

expand the second column of the Datasheet

Notice that the EmployeeID field label has automatically changed to
CountOfEmployeeID to identify the information in the column. We will rename the second field to make it more descriptive.

Step 7.To return to Design View,

Click

Step 8.To position your cursor properly, in the query design grid at the bottom of the screen,

Click before the text "EmployeeID"

Step 9.To change the label, for the second column, type:

Number of Faculty: Enter

NOTE: Remember, you have to include the colon as the separator between the caption and field name, but the colon will not be displayed in the field.

Step 10.To see the result of changing the label, on the Ribbon,

Click

The heading for the second column is correct.

Let's save the query. The name we are giving the query will be appropriate when we complete working on it.

Step 11.To save the query, press:

Control key+s,
type: qryFacultySalaryAggFunctions Enter

Now let's make some additional changes to the query.

Using the Avg Function

Another piece of data that would be useful to include is the average of faculty salaries in each department. We can modify qryFacultySalaryAggFunctionsto retrieve this information.

Step 1.To return to Design View,

Click

We will add the Salary field to the query design grid.

Step 2.To add the Salary field, from the tblFaculty field list,

scroll down, Double-Click Salary

We see three fields - DeptName, Number of Faculty: EmployeeID, and Salary - in the query design grid. Notice that the Total row default is Group By for the newly-added Salary field.

We want to see the average salary for the faculty in each department. We need to change the Total row setting for Salary from Group By to Avg.

Step 3.To select the field to be changed,

Click the Salary field Total row

We see the cursor flashing and the Total options drop-down arrow appears.

Step 4.To select the Avg function,

Click , Click Avg

The query is now set up to group the data by department and display the count of employees and their average salary.

Next we will give the new field a more descriptive name to indicate that a calculation will be displayed.

Step 5.To position the cursor properly, in the Salary field,

Click before "Salary"

Step 6.To rename the field, type:

Average Salary: Enter

Sorting the records so that the highest average salary is at the top could be helpful. We will specify a sort order.

Step 7.To begin to change the sort order,

Click the Salary Sort row

Step 8.To change the sort order to descending, type:

d

The sort order is descending. Let's view these changes to the query.

Step 9.To view the query result, on the Ribbon,

Click

Step 10.Expand any columns, if necessary.

We see a datasheet showing each department, the total number of faculty in each department, and the average salary paid to faculty in that department:

11.To save the changes to the query, press:

Control key+s

The changes are saved.

Understanding More Complex Groups and Totals

A query can be grouped by multiple fields. For example, a query could group three fields to identify an employee: EmployeeID, LastName, and FirstName. In the totals row, all three of these columns would be set to the Group By option.

Next we will add the Rank field to this query in order to view the summary for each rank of faculty.

Step 1.To switch to Design View,

Click

We will add the Rank field to the query design grid between the Department field and the Number of Faculty: EmployeeID field. Double clicking the field name would add it to the end of the grid, but we can also press and drag the field to position it where we want it. Since we want it positioned between DeptName and Number of Faculty: EmployeeID, we will use the press and drag method.

Step 2.To add the Rank field to the grid, starting in tblFaculty, in the top panel,

Press & Drag Rank until it is on top of the Number of Faculty: EmployeeID field

The Number of Faculty: EmployeeID field moves to the third column. Notice that the default Total row setting for Rank is Group By. Since we want to see a group for each rank of faculty, we'll keep that setting.

Let's also add an ascending sort to the Departments field.

Step 3.To list the departments in alphabetic order, in the DeptName field,

Click the Sort field, Type: a

The sort order will be first in ascending order by Departments, then descending order by Total Salary: Salary. Access reads these settings from left to right.

Step 4.To switch to Datasheet View,

Click

Each rank of Faculty is grouped and counted within each department, with the salary summed for each group.

Adding a Parameter

We could expand the usefulness of this query by including a parameter to allow the user to specify a rank. The resulting summary would then show the total salaries for all professors, all assistant professors, or whatever rank was entered. We will add criteria in the Rank column and enter the expression that will prompt the user for a rank of faculty when the query is executed.

Step 1.To return to Design View,

Click

Step 2.To set the criteria to make this a parameter query, in the Criteria row under Rank, type:

Like [Enter Rank] Enter

Step 3.To switch to Datasheet View,

Click

Step 4.To supply a rank for the parameter, type:

Prof* Enter

The query displays the number of employees and their total salaries grouped by Rank and Department:

5.To save the change to the query, press:

Control key+s

Step 6.Close the query.

Next, we will create a query that will calculate the students' grade point average (GPA).

Adding a Complex Calculated Field

You have seen how aggregate calculations are created in queries and that they summarize data for a group of records. You can also make calculations on a row-by-row basis, using calculated fields. Calculated fields contain customized expressions. The concatenated Meeting field that we made earlier is a simple example of a calculated field. Calculated fields are created and recomputed each time a query is executed.

In the next query, our eventual task is to generate the GPA for each of the students. To determine this, we will need to use both calculated fields and aggregate functions.

GPA values are calculated by the following formula:

Sum(PointValue*CreditHours)/Sum(CreditHours)

We'll create our complex calculated field by combining multiple simpler calculations.

Adding the Weighted Field

The first calculation we need is the weighted grade, by multiplying the PointValue of the student's grade by CreditHours available for the course.

Let's begin by opening a query that is partially completed.

Step 1.To open the query,

Double-Click qryGPACalc

We see 486 records. These records are the enrollments where a grade has been assigned.

Let's look at the design of the query.

Step 2.To switch to Design View,

Click

You see the following tables in Design View: tblStudents, tblEnrollments, tblGradeScale, tblSections, and tblCourses. In the query design grid, you see the last_name, first_name, student_id, PointValue, and CreditHours fields. Also notice the fields are sorted in ascending order by last_name, first_name and student_id.

Step 3.To open a contextual menu, in the query design grid, in the empty column to the right of Credit Hours,

Right-Click the Field row

Step 4.To launch the Expression Builder,

Click Build...

NOTE: If Build is grayed out, click another field, then right-click the field row of the empty column again.

The Expression Builder dialog box appears.

Step 5.To add the first value to the expression, in the Expression Categories field,

Double-Click PointValue

[PointValue] appears in the Expression field and <Value> appears in the Expression Values field.

Step 6.To add the multiplication operator, type:

*

Step 7.To add the next value to the expression,

Double-Click CreditHours

We are almost finished building the expression.

Step 8.To close the Expression Builder,

Click

The Expression Builder is now closed.

As we saw earlier, Access has named this field "Expr1."

Step 9.To replace the field label, in the query design grid,

Double-Click "Expr1", type: Weighted Enter

We have created an expression that names the new field Weighted and tells Access to multiply the value in the CreditHours field by the value in the PointValue field for each record.

Let's view the query result.

Step 10.To view the query result,

Click

There are 486 records returned.

Notice the Weighted column isn't quite wide enough to show us all the data. Let's fix the width before we move on.

Step 11.To widen the column, on the right border of the Weighted column,

Double-Click

The Weighted column formatting leaves much to be desired, however we are going to hide this column before we finish, so we won't worry about it.

For each student in the list, we see PointValue, CreditHours, and Weighted:

NOTE: Each student's records may appear in a different order.

Notice that for some students, like Theresa Adams, there are records without a value in the PointValue column. However, these records don't account for all the students who didn't receive grades. We know this because the Enrollments table has 503 records, and we are only seeing 486 records. This can be explained by the relationship between the underlying tables.

Exploring Join Properties

If we examine the enrollments data, we can see the difference can be accounted for by the records where no grade was reported. Why don't these records appear in this dynaset? It's due to the join properties of the relationship between tblGradeScale and tblEnrollments:

The relationship between the tblEnrollments and tblGradeScale tables is established using the Grade field, from tblEnrollments, and the LetterGrade field, from tblGradeScale, as we see in the previous diagram. This relationship is using the join type called inner join. In Access 2016: Structuring & Relating Data, we learned that this type of join only returns the records where the data exists on both sides of the relationship. This means when the Grade field is empty, there isn't a match between the two tables, and these records are excluded from our results.

So what are the records that appear in our results that don't have a PointValue? These are students who received an incomplete in a course. If a student is given an incomplete for a course, the instructor records the grade as an "I." Since the grade "I" appears in both tblEnrollments and tblGradeScale, these records are included in the results. However, there is no point value associated with I grades in tblGradeScale, and if we don't exclude them from the GPA calculation, the results of our calculation will be inaccurate.

Excluding Records Without A PointValue

Let's exclude any records where the PointValue column is blank.

Step 1.To return to Design View,

Click

Remember, when a value hasn't been entered into the database, Access sees the value as a null value. In Access 2016: Structuring & Relating Data, we discussed null values and the special keywords we use when working with them.

In this case, we only want to see records where the PointValue field isn't empty or in other words isn't a null value. So the criteria we will enter for the PointValue will be Is Not Null.

Step 2.To specify the appropriate criteria, in the PointValue column,

Click the Criteria field, type: Is Not Null Enter

While we need to add the PointValue field to the query in order to set criteria on the field, it does not actually have to be visible in the results of our query. Remember, we can deselect the Show checkbox to remove the column from being displayed in the results. However for now, as we aren't done with our calculation, we will leave it visible.

Let's view the query result.

Step 3.To view the query result,

Click

There are 472 records returned. Notice records for students who received an incomplete are now excluded from the results. Let's save this query.

Step 4.To save the changes to this query, press:

Control key+s

Using Aggregate Functions on Calculated Fields

Now that we have eliminated incomplete grades, we need to sum together the rest of the records for each student. Remember, the Totals function allows us to use predefined operations, including Sum, on groups of records.

Remember the equation for GPA is:

Sum(PointValue*CreditHours)/Sum(CreditHours)

We created the Weighted calculated column that shows the results of multiplying the PointValue by the Credit Hours. So the equation we are working with at this point is:

Sum(Weighted)/Sum(CreditHours)

What we need to do now is to sum the rows that are for the same student together. So if a student has grades in three classes, the values are added together to give us a sum value for each student's Weighted and CreditHours columns. This will give us the total weighted points the student has earned and the total credit hours each student has taken.

Step 1.To return to Design View,

Click

Step 2.To turn on the Totals function, in the Show/Hide group of the Ribbon's Design tab,

Click

Now let's set the Total row to sum for both Weighted and Credit Hours.

Step 3.To select the CreditHours Total row,

Click the CreditHours Total row

Step 4.To set the function to sum, in the CreditHours Total row,

Click , Click Sum

Step 5.To rename the CreditHours field,

Click before "CreditHours",
type: Total Credit Hours: Enter

Remember, if we didn't do this, the name of the field in our results would be "SumOfCreditHours." We don't need to do it for Weighted since we already renamed it in the last section.

Step 6.To set the Weighted aggregate calculation,

Click the Weighted Total field, Click , Click Sum

Step 7.To view the query result,

Click

There are now 368 records showing. Notice that many students still appear in multiple rows. That doesn't seem right. Let's explore what the issue is.

Step 8.To return to Design View,

Click

The query design grid looks like:

As we showed earlier, the fields with Group By in the Total fields control how many groups there are. Notice that we have Group By in the Total fields for last_name, first_name, student_id, and PointValue.

The fields we use to group when using aggregate functions can drastically change the results we get. For example, if we had selected last_name and first_name, but not included student_id, the final GPA values would be merged together for students with the same name.

The extra rows we're seeing can be explained by the grouping on the PointValue field. The grouping on the PointValue field combines all courses in which a student earned the same grade. There is a separate row for each different grade a student earned; for example, unless a student only earned a B+ in all of her classes, they won't all appear in one single row. We really only want to group on last_name, first_name, and student_id.

However, we don't want to just remove PointValue from the query. It is being used to establish the criteria for eliminating enrollments where the student has an incomplete. It might seem that unchecking the Show checkbox so the field didn't appear in the results would fix this, but that is not the case.

What we need to do is change the Total field from Group By to Where. Setting the Total field to Where allows the field to be used for establishing criteria to limit the data, but not be used to create groups.

NOTE: Setting the Total field to Where isn't required if you do want to group by that field. For example, in the previous query, we added parameter criteria for Rank and also grouped by Rank, so we used Group By.

Step 9.To change the Total row setting for PointValue,

Click the Total row, Click , Click Where

Notice that changing the Total row to Where deselected the Show field. Whenever the Total row is set to Where, the Show checkbox is automatically deselected. Access does this because the field is only used to set criteria.

Step 10.To view the query result,

Click

There are 175 results returned. We now see a row of data for each student with the sum of their weighted points and a sum of the total credit hours. All we have left to do is to divide the weighted points by the total credit hours.

Step 11.To save the changes we have made, press:

Control key+s

The changes are now saved.

Using Results of Aggregate Functions in Calculated Fields

Now that we have the sum total for the values in the Weighted and Total Credit Hours fields for each student, we need to use those values to create another calculated field to divide the value of the Weighted column by the value of the Total Credit Hours column.

Step 1.To return to Design View,

Click

Step 2.To open a contextual menu, in the query design grid, in the empty column to the right of Weighted,

Right-Click the Field row

Step 3.To launch the Expression Builder,

Click Build...

NOTE: If Build is grayed out, click another field, then right-click the field row of the empty column again.

The Expression Builder dialog box opens.

Step 4.To start the expression, in Expression Categories list,

Double-Click Weighted

NOTE: If you don't see Weighted, close the Expression Builder and save the query. Then reopen the Expression Builder.

Step 5.To add the division symbol, type:

/

Step 6.To finish the expression, in Expression Categories list,

Double-Click Total Credit Hours

NOTE: If you don't see Total Credit Hours, close the Expression Builder and save the query. Then reopen the Expression Builder.

Step 7.To close the Expression Builder,

Click

The Expression Builder is now closed.

As we have seen previously, the default name Access gives this field is "Expr1." We would like to change this to something descriptive.

Step 8.To replace the field label,

Double-Click Expr1, Type: GPA Enter

If we attempted to view the query right now, we would see a Enter Parameter Value dialog box. Access will show this dialog box whenever it runs across a object name or expression it doesn't understand. Notice for GPA the Total row is currently Group By. While it might seem we would change the Total to Sum, that is not the case. If we did and then tried to view the query results, we would get an error message similar to:

In the table of aggregate functions earlier there was a function called Expression. This is used to return a calculation based on the results of other aggregate functions. This is what we need here since both Weighted and Total Credit Hours are the results of aggregate functions already in the query.

Step 9.To set the function to Expression, in the Total row under the GPA column,

Click , Click Expression

Step 10.To view the query result,

Click

Step 11.Expand the GPA column, if necessary.

We see the results of the calculation. Notice the formatting is not appropriate for GPA values. While we could widen the GPA column so we can see all of the data, but what we really want is to limit the number of decimal places returned.

Formatting a Column

We would like the GPA to have just two decimal places. Let's fix the formatting of the GPA.

Step 1.To return to Design View,

Click

Step 2.To see the properties for the GPA column, if necessary,

Click an empty part of the column, Click

The Property Sheet appears to the right side of our screen.

Step 3.To set the format for the field, in the Property Sheet,

Click the empty space next to Format,
Click , Click Fixed

Step 4.To set the number of decimals, in the Decimal Places row, type:

2

Step 5.To view the query result,

Click

You see:

There are 175 results returned. Before we leave this query, let's hide the Total Credit Hours and Weighted columns.

Hiding Columns in Datasheet View

We needed the Total Credit Hours and Weighted columns to calculate the GPA, however we don't really need to see them in the query results. It would seem that you could just deselect the Show cbeckbox in the query design for these two columns. However, if we tried this, when we viewed the query, parameter dialogs would appear, prompting us for values for both Total Credit Hours and Weighted. Earlier, we mentioned that Access shows parameter dialogs whenever it doesn't understand an object or expression. Deselecting the Show checkbox actually alters the SQL used in the query. In our case, this would mean Access wouldn't know what the values of Weighted and Total Credit Hours were for the GPA calculation.

There is a way around this: we can hide the columns in Datasheet View. Hiding values in Datasheet View doesn't alter the SQL; instead it sets their width to 0. This means they are hidden from display, but the SQL statement does not change.

Step 1.To select both the columns,

Point to the Total Credits column heading, Press & Drag over to Weighted

Step 2.To hide the columns, on the Home tab of the Ribbon, in the Records group,

Click , Click Hide Fields

Step 3.To save the changes to the query, press:

Control key+s

Now the query for calculating the GPA is complete. Before we close this query, let's look at what Access is doing behind the scenes for us.

Understanding Structured Query Language (SQL)

Structured Query Language (SQL) is a standardized data query language that was created for the purpose of working with databases. For those intending to work with server databases such as Oracle and SQL Server, knowledge of SQL is essential. One subset of SQL can be used to form statements that create, retrieve, update, or delete data.

Access automatically translates a query into the Access version of SQL, but it is not necessary to know SQL in order to create queries in Access. Knowing SQL, however, could enable you to customize more powerful Access queries. Also, when working with forms and reports in Access, there will be times when you are asked to save changes to the SQL statement. By exploring the SQL View now, you will be more familiar with the terminology.

We can easily view the SQL version of queries we create. Let's explore the SQL View of this query.

Step 1.To select a different view of this query, on the Ribbon,

Click , Click SQL View

When this window first opens, all the text is selected. Let's make it easier to read by deselecting the text.

Step 2.To deselect the text,

Click anywhere in the window

You see:

The SQL View displays this query in an SQL statement. Notice SQL keywords appear in all uppercase letters: SELECT, AS, FROM, INNER JOIN, ON, WHERE, GROUP BY, and ORDER BY. Note that each field mentioned in the statement is identified in the format tablename.fieldname.

NOTE: For more information on Structured Query Language, consider taking the workshops: SQL: Data Retrieval and SQL: Advanced Data Retrieval and Data Modification.

Step 3.Close the query.

We are done working with select queries. Now, we will move on to working with the some of the other types of queries Access allows us to create.

Summarizing Data with Crosstab Queries

Access offers another tool for summarizing data—the Crosstabquery. A Crosstab query uses a function to calculate data that is grouped by two types of information—one down the left side of the datasheet and another across the top. The specified calculation is performed at each row-column intersection resulting in a spreadsheet-like summary of the objects specified in the row and column headers. Crosstab queries allow the display of data in a format that facilitates analysis of grouped and calculated data.

Crosstab queries have at least three fields:

• Column heading field—identifies one parameter about the data that is to be studied. In the Car Sales database example that follows, it is car color. The query datasheet then contains one column for each color of car that has been sold. Only one column field is permitted in a Crosstab query.
• Row heading field—identifies another parameter about the data that is to be studied. In the Car Sales database example below, these headings are car make and model. The query datasheet contains one row listing each. Crosstab queries can have a maximum of three row heading fields.
• Summary value field—supplies data to be calculated at each intersection of a row and a column. In the following Car Sales database, this calculation is a count of cars sold to show which color of each model was selling the best. Any "0" values will display as blank cells.

The following Crosstab query example from a car sales database demonstrates these fields:

NOTE: The row sum field that you see in the previous diagram, which totals all calculations for the row, is optional.

Since Crosstab queries are a more advanced type of query, there is a wizard for creating them, however the Crosstab Query Wizard has limitations that make it less than ideal. The main limitation is that queries created using it need to be based on a single table or query. If a Crosstab query requires fields from multiple tables, a query that contains the needed information must first be created, and then that query can be used with the Crosstab Query Wizard.

In the next exercise, we will create a Crosstab query that pulls data from multiple tables. Rather than pull that data together into a query and use it as the data source for the wizard, we will create a Crosstab query in Design View.

Building a Crosstab Query in Design View

Let's create a Crosstab query in Design View that will show departments, courses and enrollments. The row headings will be DeptName and CourseTitle. The column heading will be SemesterYear. In the intersecting cells, a count of EnrollID will show students enrolled in each semester/year.

The query results will look like this:

Let's create this Crosstab query.

Step 1.To begin creating a new query, on the Ribbon,

Click the Create tab, Click

We see the query design window and the Show Table dialog box.

Step 2.To add the tables we want to the query design window,

Double-Click tblEnrollments, tblSections,
tblCourses, tblDepartments

Now that we have selected the tables we need to build the Crosstab query, we can close the Show Table dialog box.

Step 3.Close the Show Table dialog box.

We see the selected tables in the query design window. Before we create the query itself, let's save and name the new query.

Step 4.To save this query, press:

Control key+s, Type: qxtbEnrollmentsByDepartmentsAndCourses Enter

NOTE: The "qxtb" is the standard Access naming convention prefix for Crosstab queries.

Creating the Crosstab Query

By default, Access assumes new queries will be Select queries and the query design grid accommodates that query type. The first step in building a Crosstab query is to change the query type from the default Select Query to Crosstab Query.

Step 1.To define the query type, in the Query Type group on the Ribbon,

Click

Crosstab and Total rows have been added to the query design grid.

We want to first add to the grid the fields that will correspond to the rows in the spreadsheet. In this case, the fields are DeptName and CourseTitle.

Step 2.To add the first row heading field to the query, in tblDepartments,

Double-Click DeptName

Step 3.To add the second row heading field to the query, in tblCourses:

Double-Click CourseTitle

Two fields have been added to the query. Now we need to label them as row headings.

Step 4.To position the cursor, in the DeptName column,

Click the Crosstab cell

We see a drop-down arrow.

Step 5.To designate this field as a row heading,

Click , Click Row Heading

Step 6.To designate the CourseTitle field as a row heading, in the CourseTitle column,

Click the Crosstab cell, Click , Click Row Heading

We see the designation of Row Heading for both the DeptName and CourseTitle fields.

Next we need to define the column heading field, which will specify the information to be displayed in the columns across the query result. Remember, only one field can be set as a column heading. In our example, we want to see the semester and year of the enrollments, and this information is contained the SemesterYear field.

Step 7.To add the SemesterYear field to the query, in the tblSections field list,

Double-Click SemesterYear

The field is added to the query design grid. SemesterYear will be a column heading.

Step 8.To designate this new field as a column heading, in the SemesterYear column,

Click the Crosstab cell,
Click , Click Column Heading

The SemesterYear field is now a column heading.

Finally, we want to add the field that will calculate the data to display at the intersection of the rows and columns. Since every enrollment has a unique
EnrollID, we will count the values using that field.

Step 9.To add the EnrollID field to the query design grid, in the tblEnrollments field list,

Double-Click EnrollID

We see the field added to the query.

Step 10.To set this field to perform the Value calculation, in the EnrollID column,

Click the Crosstab cell, Click , Click Value

For the Value field, there is another step to determine the operation that should be performed on the values. We want to count the items, so we need to specify that calculation in the Total row.

Step 11.To set the calculation to Count, in the EnrollID field,

Click the Total cell, Click , Click Count

The query design grid looks like this:

Adding a Total Column in Design View

We would like to have a total column in our Crosstab query. Let's see how to create a total column.

Since we are again counting number of enrollments in the total column, the EnrollID field is the field to use. Let's add the EnrollID field to the query design grid for a second time.

Step 1.To add the EnrollID field to the query a second time, from the field list,

Double-Click EnrollID

The field is added with the Total cell and the Crosstab cell showing the default settings. We need to change these cells to create the Total field.

Step 2.To set the calculation to be performed, in the second EnrollID field,

Click the Total cell, Click , Click Count

We must also choose the Crosstab setting for this column. We need to indicate that it is a row heading.

Step 3.To define this field's purpose, in the second EnrollID field,

Click the Crosstab cell, Click , Click Row Heading

The query design grid looks like this:

The fourth column tells Access to count the number of EnrollID values per column, and the fifth column tells Access to count up all EnrollID values in a row to generate the row total. As we have seen in earlier examples, Access will give the automatic caption of CountOfEnrollID when we run the query. Let's give it a more readable caption using the same method we have used before.

Step 4.To set the caption,

Click at the beginning of the second EnrollID field, type: Total Enrollments:, press: Enter

Step 5.To see the query results, on the Ribbon,

Click

Step 6.Expand the column headings.

We see the query result listing departments and their courses, which are in alphabetical order:

7.To save the query, press:

Control key+s

Step 8.Close the query.

Challenge Exercise: Building Another Crosstab Query

Let's create another Crosstab query that lets us see the data in a different way. We want to see how many students are enrolled in the sections of courses each semester.

Step 1.Create a new query called:

qxtbEnrollmentsByCourseAndSection

Step 2.Add the row headings.

Step 3.Add a column heading.

Step 4.Add a value.

Step 5.Add a total column.

The finished Crosstab query should look something like:

6.Close the Crosstab query.

NOTE: To view a solution for this Challenge Exercise exercise, see Appendix 3: Challenge Exercise: Building Another Crosstab Query on page 105.

This completes our look at Crosstab queries. Next we will investigate Action queries and their effects on data and the database.

Changing Data with Action Queries

Up to this point in the workshop, we have built Select queries, Totals, and Crosstab queries, which create dynasets that show the most current information in the selected fields and tables. The results of a query may be different at any given time, depending upon the underlying data.

Another type of query is the Action query, which makes changes to the underlying data. Instead of just viewing data, Action queries can easily alter data by updating field values, deleting records, or adding new records. It is easy to see that Action queries can be very powerful tools in performing global data management operations on one or more tables at once. At the same time, they can also be dangerous since an erroneously written query can make incorrect changes to thousands of records. We will keep this in mind and learn how to protect our data as we explore Action queries.

Understanding Action Queries

Select queries will serve users' needs most of the time. That's why they are the default query type. However, sometimes Action queries are needed.

The following table lists the different types of Action queries in Access:

In today's exercises, we will create these Action queries:

• We will use Make Table queries to move building information into its own table.
• We will use a Delete query to remove some extraneous rows from some data we import into our database.
• We will use an Append query to add a set of records from an Excel spreadsheet to an existing table.
• We will use the Update query to modify telephone number prefixes in a large number of records.

Backing up Tables

Because Action queries modify data, they are very powerful. However, if the specifications or expressions used to design an Action query are incorrect, we are left with bad data after the Action query is run. Therefore, we should always make copies or backups of the tables before running an Action query on them.

In our Action query exercises today, we will make changes to tblLocations, tblCourses, and tblFaculty. Let's make backups of these tables before we start building Action queries.

Step 1.To select the tables,

Click tblCourses,
press: Control key and Click tblFaculty,
press: Control key and Click tblLocations

Step 2.To make a copy of the tables, press:

Control key+c

Now that we have made a copy of the tables, we need to paste copies of them into our Navigation pane.

Step 3.To paste the copy the tables, press:

Control key+v

The Paste Table As dialog box appears:

The dialog box is asking for a table name. It also gives options for pasting only the structure (i.e., creating a new table with the same structure but no data), the structure and data, or appending the data into an existing table (i.e., allowing the addition of the copied data into another table).

Let's use the default setting, Structure and Data, to make an exact copy of the table. We'll add a "zz" prefix so that all the backup tables will be listed together at the bottom of the tables group in the Navigation pane.

Step 4.To name and create the new table for courses, type:

zzBackup_tblCoursesToday'sDate Enter

Since we copied and pasted all three tables at once we are now prompted to name the Faculty table. Access will prompt for names in the order you selected the tables if multiple tables are selected when copying and pasting.

Step 5.To name the table, type:

zzBackup_tblFacultyToday'sDate Enter

We are now prompted to name the Locations table.

Step 6.To name the table, type:

zzBackup_tblLocationsToday'sDate Enter

All three tables are backed up in case we don't get the expected results, and in case we need to access the original data.

Verifying Table Backups

Let's open the tables and verify that the copy happened correctly.

Step 1.To open the tables,

Double-Click tblCourses, tblFaculty, tblLocations, zzBackup_tblCoursesToday'sDate, zzBackup_tblFacultyToday'sDate, zzBackup_tblLocationsToday'sDate

Our tables and their backups are now open. Let's switch between them and verify that they have the same number of records.

Step 2.To view tblCourses,

Click the tblCourses tab

Notice that tblCourses contains 99 records. Let's check the backup.

Step 3.To view the backup of tblCourses,

Click the zzBackup_tblCoursesToday'sDate tab

The backup also has 99 records.

Step 4.To verify that both tblFaculty and its backup have 124 records,

repeat steps 2 and 3

Step 5.To verify that both tblLocations and its backup have 124 records,

repeat steps 2 and 3

Step 6.To close all tables,

Right-Click any tab, Click Close All

All tables are closed.

Creating Make Table Queries

The Make Table query is a select query that pulls data from a database based on certain criteria, and then saves that data into a new table. The new tables that result from Make Table queries can be used for different purposes. For example, a table can be used to hold inactive records for a specific point in time and then used as the basis for historical reports about that period. The Make Table query is also useful for making some information in an existing table available to other users without exposing all fields of the table. New tables can also enhance the performance of very complex queries based on multiple tables. The table becomes the record source for forms or reports, and the query doesn't have to run each time those objects are opened.

Using a Query to Create a Table

As we pointed out earlier, the Locations table was added to the database after the Access 2016: Structuring & Relating Data workshop. Let's take a look at the data currently stored in the Locations table.

Step 1.To open the table,

Double-Click tblLocations

As we can see, the Locations table keeps track of information about campus locations. However, when the Locations table was created, it wasn't properly normalized. Notice that every record for a location includes repetitive information for building code, building name, street address, city state, and zip codes. If one of these values needed to be changed for a building, we would have to update every location record for that building. It also increases the chances of data entry errors since that information has to be entered over and over.

To fix this, we will create a Buildings table to hold the information that is really about the building and not about a specific room in a building.

Step 2.Close tblLocations.

Step 3.To begin creating a new query, on the Ribbon,

Click the Create tab, Click

The tblLocations holds both location and building information. We want to move the building information to a new table.

Step 4.To select the table we need to create this query, in the Show Table window,

Double-Click tblLocations

Step 5.Close the Show Table dialog box.

The fields we need to create tblBuildings are BuildingCode, BuildingName, StreetAddress, City, State, and Zip, we'll add those to the design.

Step 6.To add the needed fields to the grid,

scroll down as needed, Double-Click the BuildingCode, BuildingName, StreetAddress, City, State, and Zip fields

Step 7.To preview the query, on the Ribbon,

Click

The result is disappointing in that buildings are listed for every location in tblLocations. This is in part due to the data not being normalized. We will fix this shortly.

For our purposes here, we only want to see unique building records instead of duplicates. We can modify the properties of the query to get that result.

Step 8.To return to Design View,

Click

There are properties associated with different elements of a query's design. We want to see the properties of the query so we will set the focus on the query.

Step 9.To make the query the selected object,

Click the empty gray area in the top half of the Query window

Step 10.To view the query properties, in the Show/Hide group of the Design tab, if necessary,

Click

The Property Sheet appears:

NOTE: If you only see a few properties, click somewhere in the top half of the query workspace, outside the tblLocations field list.

There are two properties that we need to examine: Unique Records and Unique Values. If the Unique Records option is set to Yes, only records that are unique in their entirety would be displayed. It may at first seem like this is what we want, but since each record has a different office, changing this setting would not actually change the query results. We would get the same result that we have now since every record has a unique location id in the Locations table.

When the Unique Values option is set to Yes, Access examines the data in the query results and displays only unique values in those results, without taking into consideration the entire record in the source table.

Since we want every unique building to display in this query, we will set the Unique Values option to Yes.

Step 11.To set the Unique Values option, in the Unique Values field,

Double-Click No

The Unique Values property is now set to Yes.

Step 12.To see the results of the query, on the Ribbon,

Click

We see the names and addresses of the 7 buildings of the University. Now that we have verified that the query is finding the records we intended, we can run the Make Table query.

Running a Make Table Query

We need to tell Access that we want this to be a Make Table query instead of the default Select query type.

Step 1.To return to Design View,

Click

Step 2.To designate the query as a Make Table query, on the Design tab of the Ribbon, in the Query Type group,

Click

You see the Make Table dialog box:

This dialog box is asking us to name the table that will be created. In the bottom half of the dialog box, we have options for saving this new table in the current database or in another database. We will accept the default and save the table in our current database. But keep in mind that we could save the created table in a separate database if we wanted to.

Step 3.To name the table, type:

tblBuildings Enter

We already know the query is giving us the data we want, but running it from the View button really just gave us a Select query view of the results. It did not create a new table. To initiate the action of making the new table, we need to use the Run Query button, the red exclamation point icon, on the Ribbon.

Step 4.To run the Make Table query, on the Ribbon,

Click

A dialog box appears:

Access is telling us that we are about to create a new table with all of the selected records from our query.

Step 5.To complete the creation of the new table,

Click

The created table, tblBuildings, is now listed in the Navigation pane. Since it is unlikely we would run this query again we won't save this query.

Step 6.To close the query without saving changes,

Right-Click the query tab, Click Close, Click

Adding a Primary Key to the Buildings Table

While we have created our new table called tblBuildings, it does not yet have a primary key nor is it related to any other tables. There must be a primary key assigned before we can establish relationships to other tables.

Let's assign a primary key to the Buildings table.

Step 1.To open tblBuildings in Design View, in the Navigation Pane,

Right-Click tblBuildings, Click Design View

We will designate the Building Code as the primary key.

Step 2.To select the BuildingCode field, if necessary,

Click the BuildingCode field

Step 3.To set the BuildingCode field as the primary key, on the Ribbon, in the Tools group,

Click

We now see the key icon next to the field name indicating it has been set as the primary key.

Step 4.To save the changes to the table design, press:

Control key+s

Step 5.Close the table.

Creating Relationship for the Buildings Table

The Buildings table needs to be related to the Locations table.

Let's relate the Locations and Buildings tables.

Step 1.To access the Relationships window, on the Ribbon,

Click the Database Tools tab, Click

Step 2.To start to add a table, on the Ribbon, in the Relationships group,

Click

Step 3.To add the Buildings table to the Relationships window,

Double-Click tblBuildings

Step 4.To close the Show Table dialog box,

Click

Step 5.To position tblBuildings closer to the tblLocations table,

Press & Drag the tblBuildings title bar and move it to the left of tblLocations

The table is now next to tblLocations.

Step 6.To expand the field list, if necessary,

Press & Drag the bottom border of the field list

Step 7.To create a one-to-many relationship, from tblBuildings to tblLocations,

Press & Drag BuildingCode in tblBuildings onto BuildingCode in tblLocations

You see the Edit Relationships dialog box:

8.To enforce referential integrity,

Click the "Enforce Referential Integrity" checkbox

We will also cascade updates in case a building's code ever needed to be changed.

Step 9.To cascade update related fields,

Click the "Cascade Update Related Fields" checkbox

Step 10.To create the relationship,

Click

Notice that the fields we used to create the Buildings table are still in the Locations table. We will remove these duplicate fields next.

Step 11.To close the Relationships window,

Right-Click the Relationships tab, Click Close

A dialog box will appear, asking if we want to save changes to the layout of the Relationships.

Step 12.To save the layout changes,

Click

Removing Duplicate Fields

When we used the Make Table query to create the Buildings table, the data also remained in the Locations table. We would like to remove the duplicate information. It might seem that a Delete query would be used to do this but that is not the case. Delete queries allow us to delete rows of data, not fields.

Let's remove the duplicate fields,

Step 1.To open the Locations table, in the Navigation pane,

Right-Click tblLocations, Click Design View

Step 2.To select BuildingName,

Click the gray box to the left of BuildingName

Step 3.To select the rest of the duplicate columns,

Shift key+Click the gray box to the left of Zip

Step 4.To start to delete the columns, press:

Delete key

You see a dialog box asking if you want to permanently delete the selected fields and data in the fields:

Since all this information is now stored in the Buildings table, we can delete it from tblLocations without losing data.

Step 5.To finish deleting the fields,

Click

Step 6.To save the changes to the design, press:

Control key+s

Step 7.Close the table.

Creating an Append Query

The Append query allows records to be restored to or added to an existing table. Append queries allow us to add new records to the database without going through time consuming data entry. This approach can be an alternative to bringing data directly into an existing table using the import tools, especially when there are mismatches between existing fields and those imported.

Importing Data to Append

The English department at the University of the Midwest is going to start using the database we have been creating. We already added the English department to tblDepartments. The English department has been keeping the list of their courses in an excel file. We want to add these records to tblCourses. Since the structure of the spreadsheet doesn't match our table structure, the spreadsheet's data needs to be imported into the database and placed in a new table; then we can append the data from that table into our existing Employees table.

Step 1.To begin importing data, on the Ribbon,

Click the External Data tab

We now see the various options available to us when dealing with external data in Access. We want to import a Microsoft Excel file into Access.

Step 2.To import an Excel file, in the Import & Link group,

Click

We see the Get External Data -- Excel Spreadsheet dialog box.

We will navigate to the Access_AMD subfolder and choose the correct file to import.

Step 3.To begin finding the correct file, to right of the File name text box,

Click

Step 4.Navigate to the Access_AMD subfolder within the epclass folder.

Step 5.To select the spreadsheet file to import,

Double-Click EngCourses.xlsx

Step 6.To continue, if necessary,

Click the "Import the source data into a new table in the current database" radio button, Click

You see the Import Spreadsheet Wizard:

This screen in the wizard has a "First Row Contains Column Headings" checkbox at the top. If we look at the data showing at the bottom of the screen, we see that the first row does in fact contain headings for the columns.

We want to select this option.

Step 7.To select the First Row Contains Column Headings option,

Click the "First Row Contains Column Headings" checkbox

We see the first row in the preview window change appearance. These column headings will be treated as field names from this point forward, not as data to be imported.

Since this table is only temporary and we will be deleting it once we have appended our data to the Courses table, we want to create this table as fast as possible. The option we have changed is the only option we need alter so we want to skip the rest of the steps in the wizard.

Step 8.To finish the wizard,

Click

We see a dialog box noting that the data was successfully imported. It also allows us to save the import steps, which would be useful if this were a data source from which we imported regularly. Since that is not the case here, we won't save them at this point.

Step 9.To close the dialog box without saving the import steps,

Click

Since we skipped the step where we could have named the table, notice the name of the new table is EngCourses, which is the same as the worksheet name in Excel. It doesn't conform to Access naming conventions, but remember that it is only a temporary table anyway.

Reviewing the Imported Data

Before we append the data to the Courses table, we should review what we imported to be sure the import went as expected.

Let's open the table.

Step 1.To open the new table, in the Navigation pane,

Double-Click EngCourses

We see the 57 records that were imported. As we skipped the step where we could make decisions on the primary key, Access assigned an autonumber as the primary key of our table.

There is also something a little odd in our data. Notice there are some rows where the ClassCode, Description, Dept, and CreditHours fields are empty. These rows only have information in the ClassTitle column.

Let's take a better look at the ClassTitle column.

Step 2.To expand the ClassTitle column,

Point between the ClassTitle and Description headings, Double-Click to expand the ClassTitle column

Looking at the data in rows 9, 18, 27, and 35, we can see that this information is really a note about the course above the columns. If we are going to keep notes on the courses, we should add a Notes field to the Courses table and store this type of information in that column. In our case, we just want to get rid of the stray information.

We could manually delete the extra rows, since there don't appear to be that many. However, we could use a Delete query to more easily remove the extra rows.

Step 3.To start to close the table,

Right-Click EngCourses tab, Click Close

Step 4.To skip saving the changes to the layout of the table,

Click

We will now create a Delete query.

Using Delete Queries

It is generally a bad practice to delete data. Once the data is gone, it may not be able to be recovered.

Let's say a student had graduated and left the University of the Midwest. If we deleted that student and referential integrity wasn't enforced, we would then have orphan records for that student's enrollments in tblEnrollments, and we would only have a student ID with no other information about that student in our database.

On the other hand, if referential integrity was enforced, to delete the student, we would need to turn on Cascade Delete Related Records for the relationship between tblStudents and tblEnrollments. If we deleted the student at this point, we would remove both the student record and the records of any courses the student enrolled in, including their grades. This would delete important historical enrollment and grade information.

In either case, if the student wanted a transcript of their course at the university at some time in the future, it would be very difficult, if not impossible, to provide this information to the student.

An alternative to deleting data is to include a Yes/No field indicating whether the record is active or inactive. This means that the data is still available for us to refer back to. The one real downside to having an active field is that we need to remember to take this field into account when writing queries.

Running a Delete Query

In the table we just created we don't have to worry about relationships as it isn't yet related to other tables. It also is one of those rare instances where using a Delete query makes sense. A Delete query removes entire records from the database. We can delete the data we imported because it's new, and we don't want the records we are removing to ever become integrated into our database. Let's use a Delete query to remove the stray comment rows.

Step 1.To start creating a new query, on the Ribbon,

Click the Create tab, Click

Step 2.To add the EngCourses table to the design, in the Show Table dialog box,

Double-Click EngCourses, Click Close

Step 3.To select all the fields, in the EngCourses field list

Double-Click

All the fields are selected.

Step 4.To place all the fields on the query design grid, with all the fields selected,

Press & Drag any field to the query design grid

All fields are displayed on the query design grid.

NOTE: If you Double-Click the asterisk, all fields will display in the query dataset, but the fields won't show up individually on the query design grid.

Step 5.To change the query type, on the Design tab, in the Query Type group,

Click

The grid now displays a Delete row. The query design window should look like this:

We want to delete any of the rows that are just comments. The English departments practice was to always put comments in the ClassTitle column. If there is only a value in ClassTitle, then the row only contains a comment. So we can check to see if there is a null value in the ClassCode field to eliminate the notes.

Step 6.To add the Criteria to select the notes, in the ClassCode field,

Click the Criteria cell, Type: Is Null

Step 7.To view the records to be deleted,

Click

We see the 7 records of the notes in the query results. This verifies that our query is only pulling the records we want to get rid of.

Step 8.To return to Design View,

Click

Step 9.To run the Delete query,

Click

We see an alert warning us we are about to delete 7 records and giving us the opportunity to confirm that intention:

Step 10.To continue the delete process,

Click

At this point, we could save the Delete query, but it is unlikely that we will ever rerun this same query. However, we will use what we have with modifications to append the remaining data.

First, let's confirm that the data has been deleted.

Confirming the Delete

While Access showed us a message confirming the deletion, we want to be sure that the data we expected was deleted. Let's take a look at EngCourses to be sure the rows were deleted.

Step 1.To open the EngCourses table,

Double-Click EngCourses

There are 50 records in the table. Remember, when we looked at the data after we imported this table, there were originally 57 records. The current number of records confirms that 7 records were deleted.

Step 2.Close the table.

You see the query design grid.

Running An Append Query

Now that we have only the data we want in the EngCourses table, we can change our query to an Append query to allow us to add the remaining records to tblCourses.

One thing we need to deal with when appending data from one table to another is the differences in how the tables are structured. Before we try to append the records from EngCourses to tblCourses, we need to study the structure of the two tables. Are their fields compatible?

These images compare fields in both tables:

We can readily identify some potential roadblocks to the append action. The table EngCourses has:

• Field names that do not match the names in tblCourses
• The field ID that does not exist in tblCourses

As the graphic shows, the two tables that will be used for the Append query do not have matching fields. Let's proceed and see how Access helps us to deal with these issues using the Append query.

Step 1.To switch to the Query Tools Design tab, on the Ribbon, under the Query Tools contextual tab,

Click the Design tab

Step 2.To change the query type, on the Design tab, in the Query Type group,

Click

A dialog box appears asking which table it should append the selected records to. Notice the radio buttons that would allow us to append to a table in a different database if preferred. However, we want to append these records to tblCourses in our current database, so we will keep the default choice.

Step 3.To select the table you want to append to, on the drop-down list,

Click , Click tblCourses

Step 4.To confirm your settings,

Click

The dialog box closes.

We need to remove the criteria we used for the Delete query.

Step 5.To remove the criteria,

Press & Drag across "Is Null", press: Delete key

Now we can deal with the difference in the structure of our tables.

Matching Fields to Append

Notice the Append To row that has been added to the query design grid. Where Access recognizes a match between field names, the destination field name is filled in. In the other fields, ID, ClassCode, ClassTitle, and Dept, Access left the Append To row blank. It needs help to identify the destination field.

Step 1.To start identifying the destination field for the ClassCode column,

Click the "Append To" field in the ClassCode column

Step 2.To select the matching field,

Click , Click CourseCode Tab key

We have now added the missing link for the CourseCode field of the append.

Step 3.To identify a match for the ClassTitle field, in the Append To row:

Click , Click CourseTitle, press: Tab key Tab key

Step 4.To identify a match for the Dept field, in that column's Append To row,

Click , Click DeptCode

The ID field is a field that exists only in EngCourses. We cannot append a field to a table if that field does not exist in the destination table.

At this point we have two choices. We could go to the design of the tblCourses and add an ID field. Or we could simply choose not to append the field from EngCourses to tblCourses. Since this is the autonumber field Access automatically added and it is not needed for our data, we will delete the ID field from this append.

Step 5.To select the ID field in the query design grid, with the black downward-point arrow,

Click the gray selector bar at the top of the ID column

Step 6.To delete the field, press:

Delete key

We can continue to preview and then run the query.

Step 7.To view the records that will be appended,

Click

There are 50 records of courses which will be appended to tblCourses when the query runs. Let's return to Design View and run the Action query.

Step 8.To switch to Design View,

Click

Step 9.To run the query,

Click

You see an Access alert box telling you that you are about to append 50 rows of data to the tblCourses table:

Remember that you cannot Undo the append once you have confirmed it!

Step 10.To append the records,

Click

The records are added to tblCourses. It is more likely that an Append query would be rerun as there are workflows where someone puts data into an Excel file and then it is appended to an Access database at set time periods. Since this is the case, we will save this query.

Step 11.To save the query, on the Quick Access toolbar,

Click , type: qappEngCourses Enter

NOTE: The "qapp" is the standard Access naming convention prefix for Append queries.

Step 12.Close the query.

Let's look at tblCourses to see how the record count has changed.

Step 13.To view tblCourses,

Double-Click tblCourses

As a result of the append, our table now has 149 courses instead of 99.

Step 14.Close tblCourses.

Creating an Update Query

The one other important type of Action query is the Update query. It can be used to change data in multiple records in a table at once. This is easier than changing the records one by one.

The Faculty table holds work phone numbers for each faculty member. All of these employees currently have the area code of 812. We have just received word that some of the employee phone numbers are being assigned new area codes. Each number with a 776 exchange, as in (812) 776-2488, will switch from the 812 area code to a 444 area code.

Changing each individual phone number manually would be very time consuming. We can design an Update query to indicate which numbers need to change and modify just the area code portion of the affected numbers in one action.

Step 1.To create a new query, on the Ribbon,

Click the Create tab, Click

The Show Table dialog box appears. We will add the Faculty table to the query design grid.

Step 2.To add the table,

Double-Click tblFaculty

The Faculty table is added to the query design grid.

Step 3.Close the Show Table dialog box.

The Phone field is the only one we need to update, so we will add it to the form.

Step 4.To add the Phone field to the query design window, in the field list,

Double-Click Phone

Next we need to change the query type.

Step 5.To select the Update Query type, on the Ribbon, in the Query Type group,

Click

A new Update To row is added to the query design grid. We will use this field to specify the change to the phone numbers. But first, we will use the Criteria field to specify the records to be changed. We only want to change numbers that have 776 as the set of numbers following the area code.

Step 6.To enter an expression to describe the numbers we will change, in the Criteria field, type:

Like "(812) 776*" Enter

NOTE: Make sure to type a space between the (812) and the 776.

This expression specifies the criteria for the phone numbers that we are looking for. The asterisk is the wildcard we have used before and indicates that anything else could follow these first eight characters

Now that we have the code to find the phone numbers with the proper area code and exchange, we can tell Access how to update the phone numbers. Since the expression we need is complex, let's use the Zoom tool to create it.

Step 7.To access the Zoom tool for the Update To row, in the Phone column,

Right-Click the Update To field, Click Zoom...

The Zoom window opens.

Step 8.To enter an expression to describe how the phone numbers will change, type:

"(444)"+Right([Phone],9)

The Right function is a built-in function that selects a given number of characters from the right side of a field. In plain English, this expression says, update the phone number to (444) plus the existing 9 characters from the right side of the Phone field. It's 9 characters because there is a space before the local phone number and the local phone number has seven digits plus a hyphen between the third and fourth digits.

Step 9.To close the Zoom tool,

Click

Step 10.To preview the query result,

Click

There are 28 records selected. Unfortunately, the datasheet only shows the list of phone numbers matching the criteria we specified. It does not preview the numbers as they will be modified when we run the update query. Since changing data without testing could be dangerous, we will use a different method to test the Update To expression.

Testing the Update Query

To test the accuracy of the Update expression and its effect on the existing records, we will copy and paste the expression describing the changed numbers into a new field, change the query type back to Select query, and view the values displayed by the expression. We will see the result of the Update expression in the same way we saw the Weighted calculated field results earlier in this workshop.

Step 1.To switch to Design View,

Click

Step 2.To expand the Expr1 column,

Double-Click between the Phone column and the empty column after it

Step 3.To select the Update expression,

Press & Drag across "(444)"+Right([Phone],9)

Step 4.To copy the expression, press:

Control key+c

Continuing this test of the expression, we will switch the query type from the current Update type to the Select query type.

Step 5.To switch from an Update query to a Select query, in the Query Type group of the Ribbon,

Click

The query design grid changes back to the Select query design grid without an Update To row. To test the update, we will use two fields: the Phone field and a calculated field that will use the update expression.

Step 6.To create the second field, in the column to the right of the Phone field,

Click the top row, press: Control key+v, Enter

The grid should now look like this:

7.To view the query result,

Click

This query result shows the phone numbers with 776 as their exchange and it shows, in the second column, those numbers with the new area code.

Step 8.Verify that yours is correct.

Running an Update Query

Now that we have confirmed the update will work as expected, we will switch the query type back to Update, and run the query.

Step 1.To switch to Design View,

Click

Step 2.To change the query type back to Update, on the Ribbon,

Click

The rows of the Update query return with our update expression in place. The second column we created was only needed to test the expression. While we no longer need that second field, Access will ignore it when we run the query so we will just leave it in place.

Step 3.To run the query, on the Ribbon,

Click

We see an Access alert message that we are about to update 28 rows:

Step 4.To continue with the update operation,

Click

To see the result of this query, we must look at the information in tblFaculty. Let's close the query and check the updated phone numbers in the table.

Step 5.To close the query without saving changes,

Right-Click the query tab, Click Close, Click

Viewing the Update

Let's check the results of the update in tblFaculty.

Step 1.To see the effect of the Update query on faculty phone numbers, in the tables list,

Double-Click tblFaculty

We could see the modified phone numbers more clearly by sorting these records by the Phone field.

Step 2.To place the cursor, in the Phone column,

Click in any cell

Step 3.To sort the records by phone number, on the Ribbon, in the Sort & Filter group,

Click

We see that all faculty with local the phone number exchange of 776 now have an area code of 444.

It is easy to see that Update queries can change large amounts of data in one quick step.

Step 4.Close tblFaculty without saving.

We have seen that Action queries are suitably named. They result in changes to the database that can be helpful and time saving. Remember that it is important to back up the original data before running Action queries to protect yourself against mistakes.

Using the Find Duplicates Query Wizard

In spite of our best efforts to store data efficiently in databases, errors happen that result in duplication of information. Sometimes data—which may or may not be accurate—is imported from other sources. Some departments may be using databases that were poorly designed years ago, but users have continued to enter data into them anyway. And, of course, humans make data entry errors. When it is time to clean up the data, the Find Duplicates Query Wizard can help with that task.

Finding Duplicate Records

In our database, tblStudents holds some records that have been entered over time and came from multiple sources. The possibility exists that some students may have been accidentally entered multiple times. If the primary key is entered incorrectly and doesn't match a primary key already in the table, then Access won't prevent that data from being entered. This is true of data we append from other sources or data directly entered into the database.

We will use the Find Duplicates Query Wizard to search for duplicate records in
tblStudents.

Step 1.To activate the Create tab, on the Ribbon,

Click the Create tab

Step 2.To begin building the Find Duplicates query, in the Queries group,

Click

Step 3.To indicate that we want to create a Find Duplicates query,

Double-Click Find Duplicates Query Wizard

The first step of the wizard opens. The wizard asks for the name of the table we need to search for duplicates.

Step 4.To choose the table for our search,

scroll down and Click Table: tblStudents

NOTE: We could view names for queries or for tables and queries by clicking the appropriate radio button.

Step 5.To move to the next step in the wizard,

Click

The next window is asking for the fields that will indicate duplication. In other words, what would make two records look suspiciously similar? Two records with the same first name and last name, while not conclusive, would warrant a closer look. Let's look for records with matching first and last names.

Step 6.To select the fields with possible duplicated information,

Double-Click last_name, Double-Click first_name

Step 7.To move to the next step of the wizard,

Click

This window asks for other fields that should display in the result set. If the query finds two students named Jim Jones, how will we know if they are the same or different individuals? For our exercise today, we will choose to see the email, local_phone, and local_address fields. Those three fields will give a check of further similarity.

Step 8.To choose other fields for the query display,

Double-Click email, local_phone, and local_address

Step 9.To move to the next step of the wizard,

Click

This last step of the wizard asks for a query name.

Step 10.To name the query and complete the wizard, in the Name field, type:

qryStudentsFindDuplicateNames Enter

The Find Duplicates query result set includes 28 instances of students with the same name. Much of the information for emails, phone numbers, and addresses is different between the records. This may be because a student has moved but instead their record being updated, a new record was created for a student. Also some of these names might just be two students who share the same name. Notice that the two Brandon Diaz records contain information that is almost identical, except for the email address. The email addresses are similar enough to suggest that a data entry error may have cause this duplication. This information provides a starting point for researching and correcting the data problems. We will not delete any data at this point because it is unclear which records are incorrect; however, if we know which records were false records, we could delete them from this query.

Step 11.Close the query.

Now let's look at the wizard for finding unmatched records.

Using the Find Unmatched Query Wizard

The Find Unmatched Query, as its name implies, displays records in one table or query that have no match in a related table or query. For example, the Find Unmatched Query can be used to detect existing records in an inherited table that break rules of referential integrity for the database. In a sales database, for example, these might be records of orders made by a customer who does not exist in the primary customers table. When the user attempts to create a relationship and enforce referential integrity between these two tables or if he/she attempted to append new orphaned records to a table with a relationship established, Access will display an error message because of a referential integrity violation. The message states that current records in the table violate referential integrity rules, but it does not identify those records. A Find Unmatched Query will quickly discover the problem records.

Finding Unmatched Records

We will create a Find Unmatched Query to find any sections of courses that don't have enrollments. In other words, we will check to see which sections listed in tblSections are not listed in tblEnrollments.

Step 1.To activate the Create tab, on the Ribbon, if necessary,

Click the Create tab

Step 2.To start a new query wizard,

Click

In the list of options for building a new query, we see the wizard for the purpose of finding unmatched records. We will use the wizard.

Step 3.To select and begin the wizard,

Double-Click Find Unmatched Query Wizard

The first step of the wizard appears. It asks us to name the table holding the records we want to review. tblSections is the correct choice.

Step 4.To select the table and move to the next step of the wizard,

scroll down and Double-Click Table: tblSections

The next wizard window asks for the table or query that might hold matching records.

Step 5.To choose the second table and move to the next step of the wizard,

Double-Click Table: tblEnrollments

You will see:

The new window asks for the name of the field that joins these two tables, the field on which the tables are related. Access correctly identifies that the tables are related by SectionKey and SectionID since a relationship between these two tables has already been created on those fields.

Step 6.To move to the next step of the wizard,

Click

In this window, we can indicate the fields that we want displayed about the records in this search.

Step 7.To choose all fields to display,

Click

Step 8.To move to the next step of the wizard,

Click

This window asks for a query name.

Step 9.To name the query and finish the wizard, in the Name field, type:

qrySectionsWithoutEnrollments Enter

The query result shows 32 sections without enrollments.

Step 10.Close the query.

Summary of Queries

You have seen how powerful Access queries can be in this workshop and the many varied options you have for creating and using them. Once information is gathered from data using queries, you can build forms against the query results to create a nice interface for data entry tasks, or you can build reports to share the data with others. For more information on creating forms and reports, take the Access 2016: Designing the Database Interface workshop.

We have reached the end of the workshop, so we are ready to close Access.

Step 1.Close the Access database.

NOTE: To find more training and information about Access, go to:

http://ittraining.iu.edu/Access/

Wrapping Up

We've reached the end of these materials. At IT Training, we value your opinion very much, and want to hear your feedback about what we are doing well in these materials, or how they might be improved.

If you are in our classroom, please follow your workshop instructor's guidance and take a few moments to fill out the workshop evaluation form. Also, before leaving, please log off your computer.

If you are working through these materials on your own, please take a few moments to fill out the materials' evaluation form:

http://ittraining.iu.edu/eval.aspx?mat=1730

Thank you for participating in
Access 2016: Analyzing & Modifying Data with Queries

Contributions to These Materials

Appendix 1: Solution Challenge Exercise: Concatenating Additional Fields

Step 1.View the query in the query design grid.

Step 2.Open the Expression Builder.

Step 3.Create the following expression,

[SemesterYear]+" "+[Section Number]

NOTE: Be sure to type a space between the quotes.

Step 4.Set the caption of the new field to SemesterYearSect

Step 5.Move the SemesterYearSect field after SemesterYear.

Step 6.Remove the Section Number field.

Step 7.Hide the SemesterYear field.

Step 8.To view the query result.

Step 9.Save the changes and close the query.

Appendix 2: Challenge Exercise: Adding Parameters to a Query

Step 1.Open qryCoursesSectionsAndAllFacultySummer18.

Step 2.Save it as qryCoursesSectionsAndAlFacultySemesterParameter.

Step 3.Switch to Design View.

Step 4.Change the criteria for the SemesterYear field to:

[Enter the SemesterYear] Or is Null

Step 5.Save and test the query.

Step 6.Close the query.

Appendix 3: Challenge Exercise: Building Another Crosstab Query

Step 1.Add tblCourses and tblSections to a new query.

Step 2.Change the query type to a Crosstab.

Step 3.Add the CourseTitle, and SectionNumber fields to the query

Step 4.Set the Crosstab row to Row Heading for CourseTitle and SectionNumber fields.

Step 5.To add the SemesterYear field to the query.

Step 6.Set the Crosstab row to Column Heading for the SemesterYear field

Step 7.Add the EnrollID field to the query design grid.

Step 8.Set the Crosstab row to Value for EnrollID.

Step 9.Set the Total row for the EnrollID field to Count.

Step 10.Add a EnrollID to the query design a second time.

Step 11.Set the Crosstab row to Row Heading for the second EnrollID field.

Step 12.Set the Total Field to Count for the second EnrollID field.

Step 13.Set the caption for the second EnrollID field to Total Enrollments.

Step 14.View the query.

Step 15.Save the query and close it.