New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using mathematical operators

From: Access 2010: Queries in Depth

Video: Using mathematical operators

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.

Using mathematical operators

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.

Show transcript

This video is part of

Image for Access 2010: Queries in Depth
Access 2010: Queries in Depth

46 video lessons · 13227 viewers

Adam Wilbert
Author

 
Expand all | Collapse all
  1. 9m 9s
    1. Welcome
      1m 10s
    2. Using the exercise files
      41s
    3. Introducing the database
      4m 29s
    4. Previewing the course
      2m 49s
  2. 17m 17s
    1. Understanding queries
      3m 31s
    2. Following naming conventions and best practices
      2m 56s
    3. Using the Query Wizard
      5m 21s
    4. Exploring the design interface
      5m 29s
  3. 26m 39s
    1. Defining criteria
      5m 40s
    2. Understanding comparison operators
      3m 19s
    3. Defining the column headers
      2m 49s
    4. Exploring the property sheet
      7m 32s
    5. Printing query results
      2m 41s
    6. Working with joins
      4m 38s
  4. 14m 14s
    1. Understanding parameter queries
      4m 27s
    2. Obtaining parameters from forms
      5m 17s
    3. Creating a combo box
      4m 30s
  5. 23m 24s
    1. Understanding the Totals field
      5m 31s
    2. Creating aggregate calculations
      3m 31s
    3. Exploring the Expression Builder interface
      4m 28s
    4. Using mathematical operators
      5m 46s
    5. Applying text functions
      4m 8s
  6. 24m 23s
    1. Understanding dates as serial numbers
      2m 42s
    2. Specifying a range of dates or times
      3m 47s
    3. Formatting dates
      4m 31s
    4. Using other Date/Time functions
      3m 47s
    5. Defining today's date
      2m 41s
    6. Calculating time intervals
      6m 55s
  7. 20m 9s
    1. Introducing the conditional IIf function
      2m 57s
    2. Creating an IIf function
      7m 31s
    3. Nesting IIf functions
      4m 57s
    4. Using the Switch function
      4m 44s
  8. 20m 41s
    1. Understanding the reporting tool
      2m 13s
    2. Building the form
      6m 57s
    3. Building the query
      5m 4s
    4. Building the report
      3m 30s
    5. Finalizing the reporting tool
      2m 57s
  9. 25m 37s
    1. Finding duplicate records
      2m 17s
    2. Identifying unmatched records
      2m 29s
    3. Creating crosstab results
      2m 57s
    4. Creating backups
      1m 29s
    5. Creating update queries
      3m 22s
    6. Making, deleting, and appending records
      5m 36s
    7. Uniting tables
      3m 16s
    8. Embedding SQL code in queries
      4m 11s
  10. 1m 0s
    1. Next Steps
      1m 0s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.