Mastering Access Queries is key! Setting criteria, calculating & action queries

"Get Access queries to ask questions of your database. The more you ask, the more answers you get..."

What are Access queries? Well the simple answer is "Access, I have a question for you!" and it goes off and finds the answer. In other words, a query is simply an instruction or request for a report.

The language it uses to communicate and ask that question of your data is known as SQL (Structured Query Language) and uses this SQL query syntax to provide answers very quickly indeed.

The advantage with Microsoft Access database queries however, is that there is no requirement to learn the language at all as it provides a user-friendly screen (known as QBE - Query By Example grid).

For the more technical and 'die hard' out there, please review the SQL query syntax article.

There are many types and variations of an Access database query starting with the simple SELECT query to the more advanced query including ACTION and multi-join queries.

For those new to queries or wish to refresh your memory, let's start with how to build and query Access database objects.

Setting criteria and filtering your data is one of the main objectives of a query. This next link will go into more detail of looking at more examples and techniques of setting criteria and start to provide a good knowledge base to give you the confidence in understanding the syntax and the specific data types which some criteria belong.

Access Query Interface

Calculating in a query allows you to create additional fields to enhance reports further. Please review how to perform calculations using formulae and functions to understand the syntax, rules and the tools available in assisting the building process.

At some point, you may wish to view data as summary values instead. In a query, you can show data in either detail or summary view but not normally both together. The process of creating groups and totals queries will demonstrate the do's and don'ts when applying the correct functions.

Access queries provide a large compliment of functions that can be used in not only a query but a form, report and other higher level objects. It's not possible to list all of them here but you can view and browse functions using the Query Expression Builder tool that also provide other useful buttons and folders to look up and plot.

Access 2010 - Expression Builder

One of the most commonly used query is being able to bring tables together and join them creating a relationship. This suddenly opens up the power of queries and starts to provide true flexible reporting.

There are different types of joins that can be invoked and we start by showing you how to create a join in a query and understand the rules when links are broken, when you change a join type and the impact of stand alone tables in a query.

As you become more comfortable with the principles and practices of writing Access queries, you will start to want to investigate the more advanced query. Listed in the table below are articles on each of the additional query objects that may be of interest.

Query wizards steps you through four different types of Access queries namely Simple, Crosstab, Find Duplicates and Find Unmatched.

Click here to review Query Wizards

Parameter Query prompts you for one or more values when running the query which passes the value(s) into the design of your query.

Click here to review a Parameter Query

Top Value Query returns the top number of records by a value or percentage by suppressing the remaining records in the results set.

Click here to review a Top Value Query

Make-Table Query is one of the four ACTION queries which copies data to another Table or another Access database.

Click here to review a Make-Table Query

Append Query is one of the four ACTION queries which copies and appends data to an existing Table.

Click here to review a Append Query

Update Query is one of the four ACTION queries which changes the values to existing data. This type of query will overwrite data and cannot be reversed and caution should be taken to prevent data loss.

Click here to review an Update Query

Delete Query is one of the four ACTION queries which delete records under criteria to existing data sets. This type of query cannot be reversed and caution should be taken to prevent data loss.

Click here to review a Delete Query

Distinct Query returns unique records or values from potential duplicate data sets. There are query properties which can be set either by value or by record at a time.

Click here to review a Distinct Query

AutoLookup Query works with two Tables joined in a one-to-many relationship where the related field's value from the many side is used to extract its unique related value from the one side and populates data in the related Table.

Click here to review an AutoLookup Query

SQL Queries There are three queries which require some knowledge of the SQL language as the Access query object does not provide a QBE grid (Query By Example). These are not often used but you may find one in particular useful known as the UNION query.

Click here to review SQL UNION queries

Click here to review SQL Pass-Through queries

Click here to review SQL Data-Definition queries

Access queries are the heart of any database as it drives all types of reports, screen enquiries, data manipulation and controls data integrity. Spend the time learning this topic well and it will pay dividends in the long term.

Note: You can download the database file of your version to test the examples explained and illustrated throughout this website.

Return from Access Queries to About Access Databases home page