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.
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.
