Options to create data queries in Access 2016

Access 2016 provides several options that allow you to design and run queries to return exactly the information you need. In this article, we will learn how to modify and sort queries in Query Design View, as well as how to use Totals functions to create queries that can compute data. You will also learn more about other query options in Access 2016.

Edit Access 2016 queries

After the query has been created, you can edit query criteria, link between tables, choose to sort, hide fields in query results.

To modify the query you do the following:

When you open an existing query in Access 2016 it will display in the Datasheet View, meaning that you will see the query results returned as tables. To modify the query, you must enter Design View mode. There are two ways to switch to this mode:

Go to Home > View > select Design View in the drop down menu.

Options to create data queries in Access 2016 Picture 1

In the lower right corner of the Access window, find a small view icon, click Design View at the far right.

Options to create data queries in Access 2016 Picture 2

When in Design View, change the query as desired, then select Run to update the results.

Sort query results

Access 216 allows you to apply multiple types of arrangements at the same time when designing queries. This helps you see the data exactly the way you want.

A sort option can include one or more of a sorted column called multilevel sort. This sort allows you to sort by a previous criterion, then add the following criteria. For example: If you have a table containing customers and their addresses, you can choose to sort the records by city, then in the alphabetical order of their names.

When there is more than one sorting criteria in the query, Access treats left to right. This means that the arrangement on the left will be applied first. In the example below, customers will be sorted by City first and then Zip Code.

Options to create data queries in Access 2016 Picture 3

Apply multilevel sort

Open the query, switch to Design View. Find schools that want to arrange ahead. In the Sort: row, click on the drop-down arrow and select the sort of incremental or descending order corresponding to Ascending / Descending.

Options to create data queries in Access 2016 Picture 4

Repeat the above action for other schools (if needed). Remember, the sort is applied from left to right, so any additional sort must be applied to the fields to the right of your main sort. If necessary, reorder the fields by clicking on the field name and dragging it to the new location.

Options to create data queries in Access 2016 Picture 5

Click the Run command to apply the sort:

Options to create data queries in Access 2016 Picture 6

The query results will appear with the sort you created:

Options to create data queries in Access 2016 Picture 7

If you just want to rearrange the results table without modifying the query, go to Home > Sort & Filter group> click on the Advanced drop-down menu> create manual sort as usual. When finished, click Toggle Filter to apply the sort.

Options to create data queries in Access 2016 Picture 8

Hide the field in the query

You open Design view, find the field you want to hide, click the checkbox in the Show row : to remove it:

Options to create data queries in Access 2016 Picture 9

Click Run to update the query results. To re-display the field, repeat the above actions and click the checkbox in the field to create.

Create a gross query (total query)

When working with digital data, returning normal results may not satisfy you. You want to calculate the total number of items sold, the number of customers purchased, the amount of goods sold in the month, what to do? Access provides some functions to work with such digital data:

1. Count: Count the total number of items
2. Sum: Add values ​​together
3. Average: Find the average of the values
4. Maximum: Returns the highest value
5. Minimum: Returns the lowest value
6. First: Returns the first or earliest value
7. Last: Returns the last or most recent value

For example:

We want to find the total number of sold items for each category, so we will use the query to display all the items sold, then calculate the total sold for each item.

Create or open a query you want to use to make a composite query. On the Design tab> Show / Hide > Totals group:

Options to create data queries in Access 2016 Picture 10

A Total row will be added in the design section, with all values ​​in the row being Group By . Select the cell you want to calculate in Total :, a menu will appear:

Options to create data queries in Access 2016 Picture 11

Select the type of calculation you need, here we choose Sum .

Options to create data queries in Access 2016 Picture 12

Select Run on Query Tools Design to run the query:

Options to create data queries in Access 2016 Picture 13

The result returned will be as follows:

Options to create data queries in Access 2016 Picture 14

Parameter query

A parameter query allows creation of queries that can be easily updated to reflect new criteria, or search terms. When you open the parameter query, Access prompts you for the search term and displays the results for that search.

Query to find duplicates

This type of query allows you to find both duplicate records in the database to delete them. Duplicate records can negatively affect the integrity of the database.

Through these two articles, you know how to create the most basic queries in Access, the next part we will learn about reporting in Access 2016.

Next lesson: Create reports in Access 2016 and use advanced reporting options

Previous article: Create data queries in Access 2016 from simple to complex