Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When the fields in your tables are of a numerical datatype, Access provides additional operators that you can use when writing your queries. One type is called Comparison operators and they sort and qualify data based on the numerical value of the record. Comparison operators are also called relational operators and they don't simply match an exact value or pattern like we saw with the Like operator. Comparison operators return records that match the relationship to a value. For example, whether it's greater than the value you specify or less than or equal to.
Let's build a query that takes a look at this. Once again we're going to go the Create tab and Query Design. This time we need some numerical values. So let's take a look at our Products table. I'll double-click on Products to add it to the Query Design View. Go ahead and close the Show Table window and we'll expand this open so we can see all of the fields. Let's go ahead and build our query. We'll double-click on ProductName, Ounces to get the size, and Price to get the price. Let's go ahead and run our query and we'll see that we have a total of 90 records down here at the bottom.
This is our product name, the size in ounces, and the price to the customer. So that's our baseline. Let's go ahead and go back into Design view and we'll take a look at our comparison operators. Let's say that we are interested in all of the products that are less than or equal to 16 ounces in size. We can write it like this: less than or equal to 16. If we run this query we'll see that we have a total of 36 products that are at 16 ounces or smaller.
Let's go back into Design view and I'll go ahead and get rid of this. We could do the same thing with price. Let's say we're looking for all of the products that are greater than $40 in cost, >40. I'll run that query and we'll see that we have a total 19 products that are over $40. Let's take a look at all of the xomparison operators that we can use to build our queries. The first one is Less Than and this is represented by the less than character.
We can use this to answer questions such as all orders below a $100.00. We have Greater Than and this will be used for all orders above $100.00. We have Equal To and we can use this for statements such as Employee Number is 53 or Department is marketing. Take a moment and notice that this is applying to both numerical and text-based data. We have less than or equal to Worked 32 hours or less for example.
Or worked 40 hours or more would use greater than or equal to. We also have not equal to and this is represented by a less than followed by a greater than character. This would help us find where the Employee ID is not 53 or all departments except marketing. Comparison operators are another tool that you can use when filtering your data to the specific records that you want to work with or review. They're useful when you want to locate records that meet or exceed a certain criteria or to highlight areas of opportunity within your organization's data.
By using comparison operators in your queries, you will be better able to quickly and efficiently locate the records with the most relevancy to your specific questions.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.