Microsoft Access 2007 Queries: A Quick Tutorial
Understanding Queries in Microsoft Access
Queries form the foundational element of data manipulation within a database system. They empower users to pose specific questions to the database, effectively extracting targeted information.
Beyond simply retrieving data, queries also facilitate the saving of these inquiries for future use and the execution of actions based on the results obtained.
Building on Previous Access Knowledge
This Microsoft Access tutorial is designed as a logical progression following an initial overview of the product and a subsequent exploration of tables.
Consider this guide an essential next step for anyone seeking to deepen their understanding and proficiency with Microsoft Access.
The Power of Questioning
Prepare to leverage the full potential of Access by learning how to formulate and execute powerful queries.
The ability to ask the right questions is key to unlocking valuable insights from your data.
Key Capabilities of Access Queries
- Data Retrieval: Extract specific information based on defined criteria.
- Question Saving: Store frequently used queries for repeated use.
- Action Execution: Perform operations on data based on query results.
These capabilities combine to make queries an indispensable tool for database management and analysis.
Effectively utilizing queries allows for efficient data handling and informed decision-making.
Preparation
In the preceding article, we established two tables. To proceed, you will either need to recreate these tables, access a previously saved version, or utilize a comparable example of your choosing.
Regardless of your approach, a table resembling the following structure is required…
Once the table is prepared, we can begin working with queries.
A Microsoft Access Tutorial - Query Fundamentals
Queries represent the second core component within Access. While tables serve as data repositories, queries define and store specific questions about that data. Let’s initiate the creation of a query; it’s a considerably simpler process than it might seem.
Navigate to the Create tab and then select the Query Design button located on the right side.
Should you require assistance during query construction, the Query Wizard is available. However, for this tutorial, we will proceed with the direct method.
Access will prompt you to identify the tables you wish to query. Initially, let's focus solely on the Book table. The Author table can be incorporated later.
The true strength of Access lies in its ability to efficiently manage multiple tables simultaneously, but we will address this incrementally.
Select Book and click the Add button. The window will remain open, so proceed to click the Close button.
Access will then present you with the query design interface.
You have the option to adjust the layout by dragging the central divider upwards or downwards. Shortcuts in the status bar at the bottom right allow you to modify the view type. These will be explored in greater detail later.
The upper section of the screen displays the included tables and their respective fields. The lower section is where the query parameters are defined.
To select fields for your query, double-click them or drag them into the grid below. This determines which data will be included in the results.
For this example, we will select Author, Title, and Rating.
With the fields now in the grid, numerous options become available. These options are applied on a line-by-line basis.
We have already chosen the fields, and the tables are automatically added. The next step involves defining the sort order. To sort books by rating, for example, click the sort box for that column and choose either Ascending or Descending.
Sorting can be applied to multiple columns. Priority is determined from left to right. If you wish to sort by Rating and then Title, you will need to rearrange the column order by dragging the grey bar at the top.
The Criteria row is somewhat more intricate, but becomes intuitive with practice. Criteria specify which records from the table should be included in the results. For those with a technical background, these generally function as AND criteria, meaning all criteria must be met. To use OR criteria (where any criteria can be met), place the criteria on separate rows. You can add as many rows as needed below the Criteria label.
In our scenario, we want to display only books where the title begins with "S" and the rating exceeds 2. The "S" criteria utilizes a wildcard, indicating that the title must start with "S" but can be followed by any characters.
Numeric criteria can be defined as limits rather than specific values, allowing the use of operators like "greater than" (>).
A comprehensive discussion of criteria and wildcards could occupy an entire day, so let's proceed.
Having defined the query, we can now execute it and view the results. Click the View button in the ribbon or the Datasheet View button in the status bar. You can toggle between Design and Datasheet views to refine the query further.
It’s important to understand that Datasheet View from a query is generally live. This means that any modifications made to the query results will directly affect the underlying table data.
Finally, you can save the query for future use. There is sometimes confusion regarding this point. Saving the query preserves the question, not the answer. Therefore, if the data in the table changes, the query results will also change when rerun. Options exist to capture a snapshot of the data if needed.
Click the Save button in the quick toolbar at the top left of the Access window. Remember that queries are saved alongside the tables within the same Access file on your hard drive.
Often, queries require connecting multiple tables. For instance, we could add the Author table to utilize its information for sorting or additional criteria.
Due to the existing lookup established for the Author table, we already have access to the author's last name. However, let's assume we want to sort the output by the author's first name instead. After all, these individuals (or at least those still living) are quite approachable. Let’s refer to them as Isaac and Robert, shall we? Oh, wait… they are both deceased.
To achieve this, add the Author table to the query.
While in Design View, click the Show Table button and add the Author table to the grid.
Thanks to the established lookup, Access automatically recognizes the relationship between the tables, eliminating the need for manual configuration. Drag the First Name field down into the criteria block, then drag it to the left to prioritize it for sorting.
Click the Datasheet View button to observe the changes.
Understanding Query Types in Microsoft Access
The query constructed previously utilizes the default query type within Access, known as a Select query. Essentially, this type of query functions as a focused view, providing answers to specific questions posed to the database.
Several other query types exist, each designed for particular tasks that may prove beneficial in future database operations. While a detailed exploration of each is beyond the scope of this discussion, some introductory guidance is provided.
Many of these alternative query types fall under the category of Action queries. This designation stems from their ability to directly modify data contained within tables.
It’s important to note that no alterations are implemented until the Run button is activated. The Datasheet view merely presents a preview of the anticipated results, and a warning message will appear before any changes are permanently applied.
Updating Table Data
An Update query facilitates the modification of table data in a single operation, eliminating the need for individual record adjustments. For example, an author might decide to use a different name, or acknowledge previously unpublished works written under a pseudonym.
This query type allows for the selection of relevant records and the simultaneous application of changes to all of them.
Creating New Tables from Queries
A Make Table query operates similarly to an Update query, but instead of modifying existing data, it generates a new table containing the query results. This is particularly useful when maintaining both the original and modified datasets is required.
Adding Records to Existing Tables
An Append query enables the selection of records from one table and their subsequent addition to the end of another table. A common application of this query type is archiving records from a primary table into a secondary, dedicated archive table.
Removing Records from Tables
A Delete query offers a powerful means of removing records from a table, but it demands careful execution. This query allows for the selection of specific records for deletion.
Exercise caution when utilizing this query type to prevent unintended data loss.
Advanced Query Types
Additional query types, including Union, Cross-tab, Pass-through, and Data Definition queries, are intended for more advanced database management scenarios. These will not be covered in this tutorial.
This concludes the current discussion on Access queries. Future posts will delve into the functionality of Access Forms.
Please share your experiences with queries in the comments section, and indicate any challenges you encounter. Assistance will be provided where possible.
