Lesson 2 | What are queries? |
Objective | Describe how queries can enhance the analysis of your data. |
What are Access Queries?
What can you do with Queries?
Queries are very powerful and you can use them to group your data (something you can also do with a report) so that you can create summary calculations.
For instance, if you want to figure out the average amount of time you spend on a project, you can use a query to calculate it. You will learn a lot about queries in this module but if you find you need more, you may want to dig further into Access's query capability using the Help system or one of the resource books.
Sometimes it can be advantageous to alter the SQL inside a saved query. That is especially common when you are using pass-through queries to another database like SQL Server, but it can also come in handy when you need to nest Access queries several layers deep.
Because the queries a few layers down cannot be modifi ed in a report or form's RecordSource, you may need to change them directly.
Remember that if you use this technique for a pass-through query, you must use the SQL Server syntax of the back-end database, not Access syntax. For example, the wildcard in
SQL Server is %, not *.
Also, SQL Server expects string values to be surrounded by single quotes (`), whereas Access does not care whether you use single quotes (`) or double quotes (`` ).
What are Access Queries?
A query is a question and Access queries enable you to ask questions of your database.
The question might be as simple as
- What is the description for Project 19? or more complex such as
- Which projects for Dynamic Solutions did I work on in August?
Queries can give you a handle on the large amounts of data that are in your database because they enable you to pick and choose, to look at certain data, and to combine data from multiple tables. They also enable you to create new, calculated fields.
When you create a query what you actually create is a
query definition, that is, you define the question you want to ask your database. Each time you run the query, the query produces a datasheet containing all the data currently in your database that meets your requirements. This set of data is sometimes called a
dynaset because it is a dynamic set of data. The query definition normally includes the fields you want to see and any criteria you have. Queries can be created by using a wizard or by using the Query Design view. In this course you will learn about a certain kind of query known as the select query.
The select query, not surprisingly, is used to select data from your database. With a select query you can pull data from any of your related tables. In addition you can even add criteria so that you see only some of your data. However, there are
other kinds of queries that we will not cover in detail in this course. Learn how to open a query.
Action Queries
There is also a class of queries called action queries, which are queries which do not display data, they change it. You can use action queries to delete records, update data (usually based on some formula), append data to an existing table, or make a new table. Another type of query is the Crosstab query. These are similar to Excels pivot tables if you are familiar with them. Crosstab queries usually display summary data. Here is an example of the result of a crosstab query: you create a datasheet with project names down the left and months across the top. In each cell is the number or hours you worked on each project in the given month.
Question: Are "action queries" still available or used in Microsoft Access 2021?
Microsoft Access continues to support the use of action queries. Although there is no specific data about the 2021 version of Microsoft Access, action queries are fundamental components of Access and it is highly likely they continue to be supported.
Action queries in Microsoft Access are a type of query that allows you to perform specific actions on records, depending on the type of action query used. There are four types of action queries:
- Append Queries: This allows you to add records from one or more tables to the end of one or more tables.
- Delete Queries: This allows you to remove records from one or more tables.
- Update Queries: This allows you to modify a set of records in a table.
- Make-Table Queries: This allows you to create a new table from the existing tables based on specified criteria.
Before executing any action queries, it is recommended to back up your database because the changes made by action queries cannot be undone.
When designing action queries, the user needs to switch to the "Design View" and then to the "Query Design" in the "Create" tab. The "Design" tab offers the "Query Type" group, where you can choose from the four types of action queries mentioned above.
Remember, action queries are powerful tools but they must be used with caution because they can modify, append, or delete large numbers of records at once.
If you are using a version of Access post 2021 and you're uncertain whether action queries are supported, I recommend referring to the official Microsoft Access documentation or contacting Microsoft Support for the most up-to-date information.
As with Access 2010 web databases, there are no action queries.
Data macros allow you to
- insert,
- update, or
- edit records.
If you have to perform regular bulk operations where performance will matter, consider using an Access client database to perform those bulk operations instead. It is unlikely that people using web browsers, and especially those working on a mobile device, will need to work with bulk processes.
Even if they needed to do so, it would be too challenging to do it in the browser. The capability to perform bulk operations is one of the primary reasons we think you will appreciate the ability to connect to the SQL Server database directly from a client database.
Have you ever wanted to combine information from multiple tables in your database in an efficient manner?
Microsoft Access offers a powerful query function with an easy-to-learn interface that makes it a snap to extract exactly the information you need from your database. In this tutorial we'll explore the creation of a simple query.
Access Queries History
Of the various components in Access, queries have probably changed the least. Therefore, many
query features should be familiar to you if you are coming from client Access. You still have the same query designer that allows you to
- add tables,
- join them in certain ways,
- select fields to display, and
- apply sorting and filtering
as you have always done. You may also be pleased to find that in comparison to support for web queries in Access 2010 web databases,
the feature set for queries created in Access web apps is larger. While the feature set is still smaller than in the Access client, this module assesses the new architecture driving the creation of queries. Though the
query designer looks familiar, you will learn how queries themselves are stored as
SQL Server objects and how the designer is used to create and modify them.
I will guide you through actions that are different from the
client query designer, such as saving and previewing results from a query. The biggest change presented in queries is the new
- SQL syntax,
- expressions, and
- functions.
By the end of this module, you should have all the tools you need to make the transition from the SQL dialect you use in client Access
databases to the SQL dialect you will use in Access web apps.