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:  MS Access Data Types and Properties

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.

 

 

Related Access Database

  • access query expression examples
  • access query functions syntax
  • access template for expense report


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 VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top