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,
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,
NOTE: If Build is grayed out, click on another field, then right-click the Field row of the empty column again.
Step4. To add the MeetingDay field to the expression, in the Expression Categories list,
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.
Step5. To add the concatenation operator, after [MeetingDay], type:
Step6. To add a space after [MeetingDay], type:
NOTE: Be sure to type a space between the quotes.
Step7. To add the concatenation operator, after the quotes, type:
8. To add the MeetingTime field to the expression, in the Expression Categories list,
[MeetingDay] +" "+ [MeetingTime]
Exiting code block
Step9. To complete the expression,
Step10. To position the cursor to resize the column,
Point to the right border of the Expr1 column
11. To expand the Expr1 column,
Double-Click between the border between the column headers
Step12. To view the query results, on the Ribbon,
Step13. To see the Expr1 field, scroll right, if necessary.
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,
Step2. To select the current field name, in the concatenated field,
NOTE: Do not remove the colon since it is needed to separate the caption from the expression.
Step3. To replace the field label, type:
Step4. To view the query result, on the Ribbon,
Step5. To see the Meeting field, scroll right, if necessary.
Step6. To save the changes to the query, press:
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,
Step2. To hide the Meeting Day field,
Click to deselect the Show field checkbox
Step3. To begin selecting the MeetingTime column,
Point to the top of the MeetingTime column
Step4. To select the MeetingTime column, when you see the black down-point arrow,
Click the top of the MeetingTime column
Step5. To delete the column, press:
Step6. To save the query, press:
Step7. To view 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.
Step1. Edit the query to create a column called SemYearSect that combines the SemesterYear and SectionNumber columns.
2. Save the changes.
Step3. Close the query.
NOTE: To view a solution for this Challenge Exercise exercise, see Solution: Concatenating Additional Fields.