Join Adam Wilbert for an in-depth discussion in this video Using mathematical operators, part of Access 2007: Queries in Depth.
In the last movie, we took a look at the seven mathematical operators that Access has at our disposal. Let's see that how we can use these actual data. We'll go up to our Create tab and we will create a new query in Design view. Then I am going to add our Products table to our query and we are done with the Show table window, so I can close that. Now, for this query, we are interested in finding out the price per ounce of all of the products that we carry. That may include the ProductName, the Price, and the size, which is in the Ounces field. Now we are going to create the mathematical expression that will give us the price per ounce of each product.
Let's go ahead and right click and say Zoom to bring up the Zoom window. So, the expression is going to look something like this: price/size. Now, this isn't actually the correct syntax that we need. We need to be more specific so that Access knows exactly where these values are coming from. So I am going to go ahead and rename price, and I am going to tell it that It's coming from the table products, tbl_products, and it is coming from the field named price. And this is the syntax that Access requires. We need to wrap the table name in square brackets, and then we separate each by a period.
I am going to do the same thing for our size field. So the size is coming from the table products and is coming from the ounces field. So once again, we are taking the ounces field from the table products. So our full expression reads "take the price in the products table divided by the size, which is in ounces, from the products table." We'll go ahead and say OK and Access gives it a Expression 1 alias and we can go ahead and rename this. We want to call this dollars per ounce.
If I run this, we will see that we have got a calculated field that gives us the price per ounce of each of our products. If I were to sort base off of this, I can click on the downward arrow and say Sort Largest to Smallest. We will see the highest price per ounce product that we carry. So by using some other mathematical operators to combine the data that's in our data table, we can come up with new values that might give us some insight into our data. It should be noted these mathematical operators will only work with numerical data. Access does offer some similar functionality that applies to text and we'll take a look at those in the next movie.
- Naming conventions and best practices
- Working with joins and primary keys
- Using comparison operators
- Printing query results
- Creating parameter queries
- Creating calculated fields
- Using the Expression Builder
- Making conditional statements
- Appending queries
- Updating queries