This part contains expressions that one can use to create a calculated field in a query or to sup criteria to a query. You can use criteria in the query to limit the records you work with.  

• Text operations – 

The expressions in the table use the & and + operators to combine text strings and use a built-in function to operate them.  

1. If creates a field called Full Name that displays the value in the First and the Last Name fields.  
2. Creates a field called Address that displays the values. 
3. Creates a field called Product function to use the left function to display the value of the product name. 
4. Creates a field called Type code and then uses the right function to display the last two characters of the values.  
5. Creates a field called Area code.  
6. Name the calculated field and the use function to calculate the total items of the applied discount.  

• Arithmetic operations – 

You can use expressions to add, subtract, multiply and divide the values into two or more fields or controls.  

1. It creates a field that displays freight charges plus 10% in a field called  Prime Freight.  
2. Creates a field called Order Amount that displays the values of the product. 
3. Creates a field called Lead Time and then displays the difference between the values Required Date and Shipped Date.  

• Data operations – 

All databases store and track dates and times. You work with it by setting the date and time fields in the data type.  

• SQL aggregate functions – 

It performs to summarize the data, or you can say, sum, count, and average the data. It allows one to count the number of records in the query, calculate the percentage of freight charges and calculate the average freight on all orders to combine in a total query.  

• Fields with missing data – 

Here, the expression shows work with fields with potentially missing information, such as those unknown or undefined values. You encounter null values, such as an unknown price for a new product or a value that an employee forgot to add. The ability to find the null and values can be a critical part of database operation. 

• Calculated fields with subqueries – 

You can use a subquery to create a calculated field that displays data about the category name, product Id from the categories table is the same as the product one.  

• Match text values and data criteria – 

Here, the expressions determine the whole or partially text values. And the date criteria expressions demonstrate the uses of dates and related functions in the criteria expressions, i.e., Shipped date, Required date, and Order date. 

• Find missing data – 

A null value represents the absence of information and does not represent a zero or any value. Access supports the idea of missing information because the concepts play a vital role in the probability of a database. And here, databases must be able to record information of displaying orders for customers whose fields are null or contains a value.  

• Match record patterns with LIKE – 

The LIKE operator provides flexibility when you are trying to match rows that follow a pattern because you can use LIKE with wildcard characters and defined patterns for Access to match. It finds all records in the Ship Name field.  

• Match rows with SQL aggregates – 

A domain aggregate function must selectively sum, count, or average values. It displays all the orders.  

• Match fields with subqueries – 

You can use a subquery to calculate a value for criteria. It displays product price, unit prices above the average, and the earnings of each sales representative.  

• Update queries – 

You can use an update query to modify the data in one or more fields in a database. The field consists of the Title, date of the project when started, and confirmation code.  

• SQL statement – 

SQL ( Structured Query Language) is a query language used in Access and creates queries. Design View can also be expressed by using SQL.. It displays the values in the First and Last Name field and displays the values of product Id and product name. It also records similar matches of category id. 

TABLE EXPRESSION: 

Here, there are two ways to use expressions in the table – 

• Field default values –

Whenever you design a database, you might want to assign a default value to a  field. Here, Access supplies the default value. When a new record contains the field is created or when an object contains, the control is created. The table represents default values for a field, and if control is bound to a field in a  table, it takes precedence.  

• Field validation rules – 

Using an expression, you can create a validation rule for a field or control. To create a validation rule, we must identify the field's properties or control it. The component holds the text that displays the rule violated.

MACRO EXPRESSION: 

Sometimes, you might want to carry out an action in a macro if the given condition is TRUE. It must resolve either TRUE or FALSE value. 

Sorting and Filtering Records

We know that Access allows us to work with a humongous amount of data. But, going through such data while querying or manipulating is highly difficult and almost impossible for a human. So, we use sorting and filtering functionalities provided by the MS Access application to make our tasks easier. Sorting and Filtering are two tools that help us organize our data in our desired format and then view it. Let us understand each of them:

1. Sorting: In Sorting, you organize data in a particular order, a logical order. This results in the stored data being easier to read and understand than unsorted data. There are a number of ways you can perform sorting on the data. By default, the records are sorted by their id numbers. Some of the other ways include: sorting by name, last name, order_date, city, zip code, category, etc.
2. Filtering: This feature allows you to view only the records or data you want to see. While creating filters, you set the criteria or the constraints for the data you want to view. The filter then hides the unwanted data or records and only the desired ones are displayed to you. This increases efficiency and reduces the distraction caused by unwanted data.