Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
As with the mathematical operations that we looked at in the previous movie, Access provides us with a few ways that we can modify and combine text-based data types. For instance, we could extract a person's initials from a table of first and last names. We can do this using what Access calls string functions. The key here is to think about your text-based data as a simple string of characters or individual letters and we'll use their positions to help extract the pieces that we need. Let's go under the Create tab and we'll create a new query in Design view. This time we'll look at our Employees table. We'll double-click to add it and we can close Show Table.
We'll add the FirstName and LastName to our query. In the third field, we'll invoke our Expression Builder by right-clicking and selecting Build. Now the Expression Builder will help us build our String functions. Let's build an expression that will combine the first name and the last name into a single column. We'll go into our TwoTrees database, we'll look through our tables, and we'll find our Employees table. Let's grab the FirstName by double-clicking on it. Now to join two text fields, you'll use the concatenate symbol which is the ampersand or Shift+7 on your keyboard.
This will join the FirstName to whatever we put after it. Let's go ahead and double-click on LastName. Now we have a calculated field which will take the characters in the FirstName and it will append the characters from the LastName to it. Now in order to make this legible, we want to add a space in between the two. We could do that by inserting a space between here, wrapping the space in quotation marks, and using another concatenate symbol. So now we have the characters in the FirstName, joined to a space, joined to the characters in the LastName.
Let's go ahead and say OK and run this query and we'll see that we have the dirst and last name being combined into a third column. Let's go back into Design view, right- click on this field, and return to the build. Okay, so let's highlight this and we'll delete this expression out. Let's take a look at some of the built-in functions that can help us with text data. If we go into our Functions in the Functions Built-In to Access, we have a whole category called Text and there's a long list of expressions that apply to text fields.
Let's take a look at the Left expression. Down in the bottom of the Expression Builder, Access tells us that the Left function will extract a certain number of characters from the left side of a string. This will allow us to extract the employee's initials from their First and LastName fields. Let's double-click on Left to add it to our expression and we can see that we have two required components. The first is a string, what characters are we going to extract from. Here we're going to feed it our FirstName field from the tables, Employees' FirstName.
The second requirement is how many characters you want to extract. We just want the first one so I'll type 1. Let's go ahead and do another one to join this to the LastName. We'll use our concatenate and then we'll add another function for Left. Built-In Functions > Text > Left. This time we'll feed it our LastName field from the Tables > Employees, and LastName. Again, we only want the first character.
So my finished expression says that we're going to extract the left 1 character from the FirstName and then we're going to add to it the left 1 character from the LastName. Let's go ahead and say OK and run our query to see the results. Now we have a field that shows the employee's initials from the First and LastName fields. Now as you saw in the Expression Builder, there is a whole long list of expressions that we can use that apply to text fields. I would encourage you to use the Expression Builder not only as a tool to help you build expressions, but as a tool you use to learn about expressions.
By going through the list, you can explore some of the other functions and see how that may work. By combining some of these functions in our queries, we can come up with some interesting new ways to present our results. When feeding our query results into a report or simply reviewing the data sheet, the appropriate and creative use of string functions can increase the legibility of our data.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.