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.

Step1. 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.

Step2. 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.

Step4. 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.

Step5. 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.

Step6. 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.

Step7. To add the concatenation operator, after the quotes, type:

+

You see:

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

Double-Click MeetingTime

The final expression is:

[MeetingDay] +" "+ [MeetingTime]

Exiting code block.

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

Step9. 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.

Step10. 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

Step12. To view the query results, on the Ribbon,

Click

Step13. To see the Expr1 field, if necessary, scroll to the right.

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.

Step1. To return to Design View,

Click

Step2. 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.

Step3. To replace the field label, type:

Meeting Enter key

The field now has a more readable and informative label.

Step4. To view the query result, on the Ribbon,

Click

Step5. To see the Meeting field, scroll right, if necessary.

Step6. 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.

Step1. To return to Design View,

Click

Step2. 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.

Step3. 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:

Step4. 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.

Step5. To delete the column, on the keyboard, 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.

Step6. To save the query, press:

Control key + S

Step7. 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.

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.

This section has less guidance than the previous sections. If you would like to see a walkthrough of this section, watch the video Concatenating Additional Fields.

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

The final results will look something like:

Step 2. Save the changes.

Step3. Close the query.

NOTE: To view a solution for this exercise, see Solution: Concatenating Additional Fields.