Access queries are able to do more than just pull existing data out of a table. Query expressions can be used to manipulate or create new columns of data that are calculated on the fly. These values aren’t stored in the database, and are recalculated whenever they’re called for. In this video, learn how to build an expression that displays a sales tax and total amount due.
- [Instructor] In all the queries that we've built so far, we've focused on starting with the data that's in our tables and then filtering it using a variety of techniques. Another very powerful capability of queries is to create new columns that are calculated from existing Data columns. The advantage of this is that we don't need to actually store this data in the database. We can simply calculate it when we need it and then get rid of it when we don't. In our database, we currently have our room rate stored. We can use this to calculate the various taxes that also need to be collected using some simple formulas.
Let's start a new query in Design View and we'll take a look at that. Now, this query is gonna pull out some information from the Guest table, so we can get the first and last name. We also wanna see the Room Assignments table as well as the Room Rates table, which will get us that rate data. Let's go ahead and close the Show Table window and we'll pull down the first name and last name. I'll pull in the check-in date from the Room Assignments table, and finally, we'll get the rate from the Room Rates table. We can take a look at our current state here by just taking a look at the Data Sheet view, and we can see each of our customers, the check-in date, as well as the rates they paid for their room.
Now, what I wanna do is add on the occupancy tax. That's a 6 1/2% tax that we have to add on to our room rates to get the final total that the guest is going to pay for that room. Let's go back into Design View, and we'll add another column of data here, right after Rate. In this next blank field, I'm gonna right-click on it and come down here and choose something called Build. We can also get to the same tool by clicking on the field and coming up to the ribbon and pressing this button here with the Builder icon. Notice it has the three dots here underneath the magic wand, these ellipses matches the same icon that we saw back when we were building tables.
This is going to build up a property for us. Let's go ahead and click on Builder and that'll open up the Expression Builder. Now the Expression Builder is a tool that has lots of depth and it definitely rewards some exploration. At the top is where we're going to construct our formula or the Expression that's gonna get populated back into the query when we're done. On the left hand side on the bottom, we have an Expression Element section. This includes Groups or Functions and Database Objects. Now if you open up your Functions then click on Built-In Functions, you'll see a bunch of Expression categories and then we have some Expressions over here on the far right.
These are different Functions that are built into Access. If you've ever done work inside of Excel, then some of these might be familiar to you because they're really similar to the formulas in Excel. We're not gonna use any of these for this formula so I'm gonna go ahead and actually come back to Functions and collapse that folder again. And I'm gonna expand the folder here that represents the Landon Hotel Database that we're working with. Inside of there, we have folders for each of the different objects. Let's expand the tables object and then finally, we're gonna come down and click on the Room Rates object. This is the Room Rates table and inside of there we have two columns, one for the Rate Code and one for the Rate.
If I double click on that Rate, it's gonna add that up here into our Expression area. This represents the Rate Data out of the Rate table. Now all you need to do is multiply this by .065 to get our occupancy tax. I'll type in the asterisk and then .065. And that's our entire formula. Let's go ahead and press OK. And that'll get applied down here into this new field. Now because we're not pulling data straight out of the data table, it's actually making references to these calculated areas. At the very beginning, it puts in the text EXPR1 and this is actually gonna be the name of the column, when we view it in Data Sheet view.
Let's go ahead and just switch it over right here. You can see that Expression right here. You can also see the calculated values coming out right there so that's 6.5% of 110 is $7.15. Let's switch back into Design View and I'm gonna change the name of the column so instead of saying EXPR1, I'm just gonna type in Occupancy Tax. Make sure you keep the colon, that's actually how Access knows what is the name of the columns so everything before the colon is the name of the column and everything after the colon is the data that'll appear in the column itself. So that's how we can get our occupancy tax.
Let's run it one more time and we'll see that column change here. Let's go ahead and double click just to make sure we can read the entire thing. And so there is the dollar value that we need to collect in tax. Let's go ahead and add that to our rate now to get the total value that the customer is going to be paying for that room. So back into Design View, we'll come over here to the last column and now you can just type in a new formula here instead of going through the Expression Builder. Let's make this a little bit bigger here. I'm gonna type in a column name of Total Due. We'll type in the colon here and so again, everything after the colon is going to be the data that will appear there.
And here all we need to do is add the Room Rate to the Occupancy Tax to get the total. I can do that by typing in a square bracket and then make a reference to the Rate column. Close that bracket, add the plus sign, open another bracket, and then make reference to the Occupancy Tax column. We'll finish that statement with the closing square bracket and that finishes our Total Due. Now, make sure we turn on the column so we can actually see it, so turn on this show tech box right there. Just click it to make sure that there is a check in there. Now if we take a look at our Data Sheet, we should see the Occupancy Tax calculation, as well as the Total Due, which is the sum of those two values.
Let's go ahead and save this query now. Press Control S on the keyboard or you can press the Disk icon here in the Quick Access Toolbar. And we'll call it Occupancy Tax. That'll save it into our Navigation pane so we can get to it easily later, if we need it. So there is just a couple of examples on how you can incorporate Expressions into your query returns. The Expression Builder is a complex tool that helps you build proper syntax when using calculations and formulas. For more on this powerful component, keep your eye out for an upcoming course, where I cover queries in the Expression Builder in depth.
- Determine the essential uses for the Trust Center.
- Explore the functions of the database Navigation pane.
- Recognize the fundamentals of entering data when using Access.
- Identify the necessary steps when importing a table when using Access.
- Break down the fundamentals of filtering and sorting table data in Access.
- Identify the method utilized when building queries in Design view.
- Determine the role of forms in Access.
- Examine all of the elements involved in maintaining a database in Access.
- Explore how to properly protect an Access database with a password.