Building expressions

- In all of the queries that we've built so far, we've focused on starting with the data that's in our data tables and then filtering them using a variety of techniques. Another very powerful ability 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 at all. We could simply calculate it when we need it and then get rid of it when we don't need it. In our database, we currently have our room rates stored and we can use this information to calculate the various taxes that need to be collected after a guest's stay. Let's go ahead and create a query that's going to do that for us. Once again, I'll go to the Create tab, and start with a Query in Design view. I'm going to add in the Guest table, the Room Assignments table, and the Room Rates table here. That will add those three tables to our design and we'll go ahead and close the Show Table window. From the Guest table, I'm going to double-click on first name and last name. From the Room Assignments, I want to find the date that they checked in, so then we'll pull out the check in date here, and then finally, from the Room Rates table, we'll get the rate that they paid for that specific room. In this database, the rate is being calculated dependent on the day of the week that the client checked in on. So, if it's a weekend, it'll be charged at one rate, and if it's a weekday, it'll be charged at a different rate. If we just run this query the way it is right now, we'll just see information that's being pulled straight out of our data tables, so we can the different clients, the day that they checked in, and the rate for the room that they were in. Let's switch back into Design view and we can start adding in additional columns that aren't present in our database. I'll come over here to the next column, the next empty one, and I can either right-click on it and choose the Build option here, or I can come up here to the Design tab of the ribbon, and we'll press this Builder button here. Notice that the icon is a magic wand with these three ellipses dots on the bottom of it. We've previously seen a button with the three dots on it, back when we were creating tables, and that also started a builder, so you'll see that this visual convention travels through the interface here inside of Access. Either way, either by right-clicking or by pressing this button up here, go ahead and launch the Builder on that next empty column over here to the right of Rate. That will start up the Expression Builder here and we can take a look at this window. Here on the left, we have some expression elements and we have different folders that represent the different things inside of our database. For instance, there is a folder here that represents our functions and I can go into there and find our built-in functions and that we have some expression categories here that open up. So, we have some date, time functions and those are over here and I can scroll through here and see program flow functions and text functions and so on. If you've ever worked with Excel, then you might know that Excel has lots of different functions built into a function library and this is essentially the same thing here inside of Access. So, we have a financial function library with lots of financial functions that we can apply to our data. We're not going to be using any functions right now, so let's go back to the very beginning and we'll collapse the functions folder here, with that minus sign, and I'll expand the folder that represents our database that we're currently working in. I'll press the plus button and you'll see the different objects inside of our database. So, we'll see a different folder for our tables, our queries, our forms, and our report objects, and we can go into any of these. For instance, I'll click on the tables one to see all the different tables within our database. In this case, I want to scroll down here and find the Room Rates table. When I click on it, you'll see the different categories or the different fields that appear that are inside of our Room Rates table. If I want to make use of one of these fields in our calculation, I just need to double-click on it. So, I'll go ahead and double-click on Rate, and that gets populated up here into the expression area where we're going to type in our formula. Now, in order to calculate the occupancy tax, we need to multiply the rate times the tax rate, which in this case, is six and a half percent, so I'm going to type the asterisk character to multiply, and then I'll type .065. That will multiply whatever is in the rate field times the .065 to return the occupancy tax. When I go ahead and say OK, that adds that in here to the new column over here. Notice that since we're calculating this data, that the table row remains empty, and that's because this data isn't coming from a table, it's coming from the result of a calculation. Let's go ahead and expand this column. I'll double-click here to expand the column out so we can see the full expression here. You'll also notice that it has this Expr1 and then a colon here, The Expr1 is going to be the name of the column when it gets returned in our query results. We can change that to whatever we'd like. Just make sure that you keep the colon there, that's the important part. I'll go ahead and highlight the Expr1, and type in Occupancy Tax. Once I've done that, we can go ahead and run our query to see the results. Now I've got the first name, last name, check in date, and rate. This data is coming from the data tables, but then I also have the occupancy tax that is showing me the tax rate that is multiplying this rate value here times six and a half percent. Now, this data is showing up with varying degrees of decimals here, So, we've got some with two decimals, and some with three decimals. So, let's go back into our Design view and tell it that this is a currency data type. Just like when we created our tables here, we can go ahead and click on this field here, and I'm going to open up the Property Sheet, which is in a slightly different location than when we were creating our tables. We'll come up here into the ribbon and press this Property Sheet button, and then over here in the Property Sheet, we have a format property and if I click there, one of our options is indeed currency. So, I can switch the format of this data to currency here, and then I'll close the Property Sheet. Once again, I can run the query and you'll see that it's now being returned as dollars instead of just a random decimal value. So, now that I have the occupancy tax, we want to combine that with the rate to get the total due for each visit. Go back into Design view and we can do that as well. In the next empty column over here to the right, I could right-click and go back into the Expression Builder, but once you get used to the syntax, you can actually just write them right inside of here. So, let me show you how that would work. Instead of writing in here though, I'm going to right-click and choose the Zoom option. That will give me some more room to see what we're doing, and I'll change the font up to 16, just so we can see it a little bit bigger. So, in this case, I want to create a column called Total Due. So, I'm going to type in the text Total Due, followed by a colon. That will be the column name when it gets returned. Then we just need to type in the formula that's going to add the rate here, to the occupancy tax. In order to do that, we just make use of the field names that we want to add together, and we wrap those in square brackets. So, I'm going to type in a square bracket rate, closing bracket, and then a plus, followed by a square bracket, and occupancy tax. We'll add in the closing square bracket, so now we're saying we're going to create a new column, called Total Due, and the data is going to be the value of rate plus the value of the occupancy tax. Go ahead and say OK. That adds it in here to the next new column. I'll press the Run button here, and we can see that we now have a total due that's adding up the rate field and the occupancy tax for each person's visit. So, this is another really useful query. Let's go ahead and save it into our navigation pane. I can either press the Save icon up here, on the Quick Access toolbar. You'll notice when you hover over that, you get the shortcut key Control S, which is the same as it is throughout the Office Suite, In fact, most of Windows uses the same shortcut. So, I'll press Control S on my keyboard, and we'll name this as Occupancy Tax. Go ahead and say OK and the new query gets added down here into our navigation pane. So, those are just a couple of examples on how you can incorporate expressions into your query returns. The Expression Builder is a complex tool that helps build the proper syntax when using calculations and formulas. I definitely recommend spending some time with the Expression Builder, and taking a look at some of the nearly 200 built-in functions that come with Access.