In MS Access, a query refers to any request for data from one or more tables. It is essentially a tool utilized to extract and retrieve specific information from a database. Queries can be customized to filter, sort, and join tables in ways that offer summarized and meaningful results. By using queries, users don’t need to sift through an entire database; instead, they can identify exactly what they are looking for by setting search conditions. Query design can range from basic point-and-click operations to complex SQL coding depending on the user’s proficiency with the software. In summary, queries allow users to access critical data quickly and efficiently while minimizing the amount of time spent searching for relevant information in large databases.
MS Access Query Tutorial
In this tutorial, we will create an MS Access table and apply the most needed queries so that it will be clear how MS access queries work: From the top ribbon of MS Access, click on Create then choose table design. Insert some fields as mentioned in the below screenshot and save the table as “Table_Products”.
Click on View from the top ribbon and start filling in the table as shown in the below screenshot. Then, Save and close the table:
Tip: The ID field is marked as Auto Number. So, just ignore it as MS Access will generate an auto number for this field.
- From the top ribbon of MS Access, click on Create then choose query design.
- In the left panel of MS Access, drag the table you just saved and drop it in the query’s area.
- Double-click all fields to add them to the query list then click on View which is in the top ribbon.
- Now, you will see the exact data as the original since we did not create any expression yet.
- Save the query as “Query_Products”.
Let’s go back to query design mode by clicking on the arrow under the view icon from the top panel. Then, choose Design View. Add an expression under Product Type exactly as shown in the below screenshot:
Now Click on View. You will see that MS Access filtered the database to include only Product Type “A”.
Using MS Access Expressions
Alternatively, you can do as many expressions as you want. Anyway, try the below expression the same way as we did and see the results:
Under the Quantity field, add an expression like >100 to filter the database for items that are more than 100.
You can add a new expression to multiply the quantity by the price. To do so, please follow these steps:
Use the expression: Total: [Qty] * [Price] and click on View. The query will show all columns in the database including the total for each product:
Read more information about Microsoft Access, For more topics in Microsoft Access, please check our blog here.