Ms Access Query Examples on Expression Syntax Functions

In this tutorial, let discuss about Ms Access Query Examples. If you still remember, query in Ms Access is the feature to review, add, update, or delete data from the Access database. Before creating a query, first you need to create at least 2 tables.

To create query, go to Create menu then select Query Wizard or Query Design. The difference is with Query Wizard, you can create more specific query. However, you may select some options in the setup. Meanwhile, with Query Design you can create simple query, with more interactive interface.

 

In this tutorial, we use the Query Design and we’re going to create a query for Purchase Details Report.

 

1. Select two or more tables or queries. In this tutorial, we use Inventory Transaction table and Purchase Order.

2. Then, go to the Property box below the tables and relationship design.

3. Select the desired field to be applied in the query.

Tips: it is highly recommended to select the ‘Table’ first. After you fill the ‘Table’, the ‘Field’ drop down menu will show only the field name come from the selected table.

4. You can sort the record in query by selecting the option in ‘Sort’. You can sort multiple fields in a record too.

Read Also:  File Extension of MS Access 2003-2016 Version

5. ‘Show’ checkbox is to display the field into the query. You can use the feature to adding the unwanted (to be shown) field that needed for conditional field syntax.

6. In ‘Criteria’, input the syntax to create specific condition (optional).

7. ‘or’ is part of criteria field. This is if you want to add multiple conditional syntax.

8. In addition, we add subtotal field. For creating subtotal field, insert this function:

Subtotal: [column1]*[column2]

Note: column1 and column2 are example names. Change them to your own field.

9. After you’ve done with the properties box, go to Design tab > Run.

If it done right, it should be appeared like the picture below:

Note: if you add more than 2 tables or query, sometimes you might have received an error message like the picture below. This is because you have a criteria field that include the same fields from 2 or more tables. To solve it, you can go to SQL view and write the SQL code, or create a sub-query so you will only have 2 displayed table/query.

 

 



Access Database Tags: #free ms access query examples #microsoft access 2003 query criteria examples #microsoft access 2010 query criteria examples #microsoft access query design examples #ms access query by form example #ms access query examples criteria #ms access query examples download #ms access query expression examples #ms access query expression syntax #ms access query function examples #ms access union query syntax error in from clause

Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes


Must read×

Top