Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the key factors in creating a clean and robust database is knowing what data you need to store and what data you can derive or create from other records. For instance, if we think about how our payroll database might work, [00:00:14.2] the number we are after is how much the employee earned at the end of a two week pay period. But will the dollar amount appear in any of our tables? Probably not. More than likely a payroll database will simply include a record for the employee's hourly wage and another record of the number of hours worked on each shift.
When cutting the checks the payroll department will simply add up the number of hours worked during that pay period and multiply that by the hourly wage. Then they will have the total for the check. By using some simple mathematical operations, in this case addition and multiplication, the payroll department is able to derive the value of the check even though it doesn't actually appear anywhere in the database. The Design view in Access allows us to easily add some of these types of mathematical operators to our queries. Let's create a new query in Design view and we'll take a look at that.
Let's go ahead and add our Products table to the query and close the Show Table window. We are not going to use any fields from the Products table. I simply want to illustrate the concept of creating a calculated value in the field headers. So in my first field we could type in the mathematical expression 5+3. If I run this, Access performs that mathematical operation and returns a value of 8. Now there's 90 records here showing, because we've built this query using the Products Table as our table source.
There are 90 products listed in our Products table, so Access returns 90 records. In the next field I am going to go ahead and add another one, 5-3. Let's try 5*3 and 5/3. If we run this query Access performs the math and returns 2, 15 for 5*3, and let's expand this, 1 and 2/3rd for 5/3. So those are four basic mathematical operations that Access understands.
Let's take a look at a couple of others. I will go back in Design view and I'll delete all of these records by highlighting the fields and then pressing Delete on my keyboard. In addition to those four math operations we can use exponentiation. So I can raise 5 to the 3rd power. Here I am using the up caret, which is Shift+6 on your keyboard. If I run that, we will get 5 cubed, which is 125. Okay, back in Design view. Let me delete this out.
There are two additional mathematical operators that we can use for division. Our standard division returns a whole number and a remainder. The second form of division is called integer division and this is represented by the backslash character. Integer division will ground both values and only return the whole number, not the remainder. The third form of division is called modulo and it only returns a remainder. It drops the whole number altogether. So let's see how this could be useful. Let's say that we have a thousand bottles of olive oil. We are packing them into cases of 12 bottles each.
Our standard division would read 1000 divided by 12. Our integer division would read 1000\12. And our modulo division would read 1000 mod 12, with a space on each side of the word mod. So let's run this and see what these return. You can see that our standard division returns 83 and 1/3rd. This is the number of cases that we can fill with our thousand bottles. So we have 83 cases and then we have 1/3rd of another case.
The integer division only returns the number of full cases. So with our 1000 bottles we can 83 full cases. The modulo division returns only the remainder. So after all of our cases are packed we have got 4 bottles left over that didn't fill a new case. So that's how you can use the integer, modulo, and the standard division. Let's return to the Design view and we'll see how we can apply these mathematical operators to our query data. Back in Design view, I will highlight these 3 fields and press Delete.
So now let's say we want to calculate the total price per ounce of our specific oils. We could add a ProductName, our Ounces, and our Price to our query. In the fourth column we want to add a calculated field that takes price and divides it by ounces. We can do that in the Expression Builder. Let's right click in the field and select Build. In the Expression Builder we are going to go ahead and create our calculated field. We could drill down into our tables, so we will go into TwoTrees.accdb.
We will expand our Tables column and we will find our Products table. From our Products table we will take the Price field and I will double-click on that. Go ahead and type a forward slash for divide and then we'll double-click on Ounces, which is our Size field. So now we have a calculated field that will take the price and divide it by the size in ounces. We will say OK and let's go ahead and run this query. We could see that we have specific oils, so the query results will return each oil, the size and ounces, the price to the customer, and then we have a calculated field that calculates the price per ounce for a customer's product.
Let's go ahead and sort this largest to smallest and here we have our highest price per ounce product in our inventory. It should be noted that mathematical operators only work with numerical data. Access does offer some similar functionality that applies to text and we will see that in the next movie.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64651 Viewers
80 Video lessons · 124285 Viewers
52 Video lessons · 60227 Viewers
59 Video lessons · 46062 Viewers