Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
In the last movie, we took a look at how we can use a conditional statement to return one of two values. We would supply an expression, and depending on whether that expression is true or false, we would get a branching decision tree. Now we can nest conditional statements together, so instead of a true part, we might evaluate to a second IIf statement, or instead of false, we might go to a second or third IIf statement that way. Now if we nest multiple conditional statements together, we can get a branching decision-making tree. This will function much like a flowchart where we'll start at the top and depending if the statement is true or false, we'll move left or right, we'll evaluate with a second expression, and again move left and right from there.
At the end of the chain, we can have four expressions returned, four different values depending on how it flowed through the chart. Let's go ahead and take a look at this concept in Access and we'll apply it to a sales tax calculation. So let's create a query that decides whether or not we should be charging sales tax to our customers. Let's assume that the Two Trees Olive Oil Company has a physical presence in the state of California and Texas and any customers purchasing product in those states will need to get charged the sales tax. We can go into our Create tab and create a new query in Design view and we'll add a few tables.
We'll add the DirectCustomers table, the Orders table, and the Products table. Let's go ahead and close Show Table and we'll add our fields. From the Customers table we'll add CustID and FirstName. We'll also add the State so we can identify the state whether we need to charge tax or not. From the Products table, we need the Price. So we can find out how much tax to charge. In the fifth field, we'll build our conditional. Right-click and say Build and let's find our conditional function. We'll go to Functions > Built-In Functions > Program Flow, and double-click IIf.
So the first thing we need to do is find out if the customer lives in California. We'll write State = California. We'll go into our tables, find our customer, and grab their State. We'll type in =ca to find out whether that customer lives in California and we'll wrap that in quotation marks. If the customer lives in the state of California, we need to multiply their order total by 0.08% to get the amount of sales tax to add.
So let's go into their Products table, we'll find Price, and we'll multiply that by 0.08. So that takes care of our California customers. If the customer doesn't live in California, we need to find out whether they live in Texas. So for the dalse part, we'll start another conditional. We'll go back up to our Built-In Functions > Program Flow, and double-click IIf again. This time we want to find out if the customer lives in Texas. Go back to DirectCustomers, find their state, and we'll say =tx.
Again, remember our quotation marks. If that's true, we'll charge the Texas state sales tax at .0.05%. We'll go to Products, Price, *.05. Finally, if this expression evaluates a false, the customer does not live in Texas, we won't charge any tax at all. So we'll just enter in 0. Let's go ahead and add an alias to our column header. We could do that by clicking the very beginning and we'll type in Sales Tax followed by a colon.
So there's our completed conditional. If the customer lives in California, we'll take the price and multiply it by .08. If they don't live in California, we'll go to the next part, which asks do they live in Texas. If they do live in Texas, we'll multiply the price by .05. And if they don't live in Texas either, we'll put in a 0. Okay, let's go ahead and evaluate this expression by running our query. And here we can see how much sales tax we're going to add to each customer who lives in either the state of California, or if I scroll down we'll find a customer that lives in the State of Texas.
Let's go ahead and add a couple of more fields to finish the query. We'll go back into Design view and we'll add a calculated field here to add the sales tax to the price to get their total cost. This time I'll just right-click and go to the Zoom window and we'll type this manually. So the first thing we want is an alias that says Total Cost followed by a colon. Then we want to take the Price field from our query and we'll wrap that in square brackets, and we'll add it to our sales tax field, again wrapping it in square brackets.
So now we have the calculated field called Total Cost that will take the price and add it to the sales tax. Let' go ahead and say OK and run our query one more time. So here's the customer's final Total Cost after tax, if there is any. So that's how we can use a nested conditional statement to expand our options even further.
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.