When creating queries using calculated, or filtered records, it's always a good idea to properly document your workflow so that your results will be easily understood by other users. One way to do this is to rename the field headers in your queries to give them a more meaningful and accurate name. Let's build a query that does this. I'll go to the Create tab and go into Query Design. Let's create a query based off of our products table. I'll select it from the Show Table window and choose Add. I'll go ahead and close Show Table, and we'll add some fields from our products table down into the query design grid down below.
Let's go ahead and add ProductID, the name. Let's scroll down. We'll choose power, and I'll choose suggested retail price. Now if I go ahead and run this, you'll see that the column names here, product ID, name, power, and let me just expand this one here, suggested retail, these all come straight out of the data tables that provide the data. We can rename what these say, by using aliases in the query design view. Let's go ahead and go back in to design view here, and then I'll change the name for this product ID field. To do that, I'm going to right-click here and just go in the zoom box, so I have a little bit more room to type.
In fact, I can actually change the font size here, so it's a little easier to see. I'll change it to 14 points. Okay. In order to create an alias, all we need to do is go to the very beginning of the line, and type in whatever we want the alias to say. In this case I want to type in SKU, which stands for stock keeping unit. I'm going to finish the alias with a colon. Anything before the colon is the new name of the column. Anything after the colon is the data column that's coming out of the original data table. So in this case we have a column named SKU.
And the data is going to be the product ID from the products table. Let's go ahead and say OK, and Access updates this view down here in the field. Let's go ahead and add aliases to these other columns. We don't necessarily need to go into the zoom box. Sometimes it's just easier to see there. But you can also type directly into these fields. For instance, name, I'm going to change that to product description. Make sure you type in a colon to tell Access what's the alias and what's the data. The next one here says power. I want to be a little bit more specific. This is actually power in watts. So I'll rename my column to reflect that.
Again I typed a colon after the word watts. Next we're going to name the suggested retail column to price in dollars. And in parentheses I'll put the dollar symbol. Finish that with a colon to tell it what's the name and what's the data. Now let's go ahead and run this query to see the change. Now we can see our new column headers. This one says SKU, then product description, then power in watts here, and then price in dollars. Let's go ahead and save our query. We can either press the save icon up here on the quick access tool bar, or simply press Ctrl+S on your keyboard.
It's going to ask me for a name, and I'm going to call it products with alias, go ahead and say OK, and that gets added down here into our navigation pane. The data is still coming out of the table from the same location. By clearly identifying fields returned by your queries, you'll aid other users who will be working with your database and will help clearly communicate the meaning of a field in forms and reports. Adding a custom field header is a simple and effective way to document the meaning of a particular column in your queries, and we'll see this become more important as we move into creating some complex calculated fields later on in the course.
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks