One way to manage and organize data in Microsoft Access tables is from the datasheet view, which includes tools for filtering, searching, and sorting. For quick answers, these options can be helpful in finding information. However, there’s a problem: The datasheet features are temporary. Every time you want to find transactions for a specific vendor, for example, or a key client, you would need to re-create the filter or search. The solution to this problem is to use Access queries: custom-made search routines that you store in your database. Queries are a staple of database design and key to data management in most Microsoft Access projects.
Query Basics
As the name suggests, Microsoft Access queries are a way to ask questions about your data, like how many transactions occurred last month or what is the dollar value of each client? Access saves each query in your database like it saves any other database object. Once you’ve saved a query, you can run it anytime you want to take a look at the live data that meets your criteria.
The key feature of a query is its amazing ability to reuse your hard work. Queries also introduce some new features that you don’t have with the datasheet alone:
- Queries can combine related tables. This feature is insanely useful because it lets you craft searches that take related data into account. Microsoft Access includes relationship features to connect tables at the database level, so the connections are then automatic throughout a project. Or, you can create join queries to link related tables only for specific queries.
- Queries can perform calculations. Fields with calculations shouldn’t be stored in specific tables because the values may change. Instead, store only the values that create the calculations and build queries to perform the calculation. This approach ensures results are accurate and current.
- Queries can perform summaries. To analyze large chunks of data, you can group together rows with similar information. For example, you can group all the transactions by an employee, category, product, or vendor.
- Queries can automatically apply changes. If you want to consistently apply an update or change to your data tables, action queries are a huge time saver.
- Queries can be used with other objects. Microsoft Access queries are powerful and versatile because you can query on tables, other queries, and build simple to complex projects to get the results you need for managing data, viewing forms, generating reports, and creating database solutions.
How a Query Works
Some of the things you can control with a query:
- Which fields
- Order of fields
- Which records
- Order of records
Types of Microsoft Access Queries
Microsoft Access supports different types of queries:
- Select Queries: “show me” the data that matches the criteria; not “dangerous” because data is not changed when the query is run.
- Action Queries: perform actions on the records specified by criteria when the query is run. Be cautious because data is changed when the query is run.
- Update
- Delete
- Append
- Make Table
- Calculations may be part of select or action queries and can be calculations with existing fields or added factors such as a provided value.
- Totals summarize data specified by a select query.
- Parameter queries are action or select queries that prompt for criteria when the queries are run.
- Special queries built with a wizard, such as Find Duplicates, are also available within Access.
- Crosstab queries are more advanced queries that summarize table data.
- Queries are often used as the data source for other queries, forms, and reports.
Good to know: Queries don’t store data. Queries do store the settings, options, and parameters for how you want to view and manage your key data, whether it is from Microsoft Access tables or other linked data sources.
Explore and leverage how you can use Access queries to build more power into your Access database projects. Click here for more Microsoft Access articles and to download free handouts on Microsoft Access queries and keyboard shortcuts.
© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
