A Guide to Understanding Microsoft Access 2003 Queries

A Guide to Understanding Microsoft Access 2003 Queries
Page content

Purpose

Microsoft Access 2003 queries are used to query one or more tables or queries in your database. Query essentially means to ask a question. An Access 2003 query asks a question about the data in your Access table based upon criteria you specify when you create the query. Queries are most commonly used to add or delete records from a table, edit existing data in a table or provide a list of filtered data from a table. Queries are used in Access reports and to provide data within forms.

Create Query

There are two initial ways to create a query. The first is Wizard Mode and the second is Design View. Wizard Mode is the easiest and works great for creating simple queries and a template for more complex queries. Design View provides you with a blank query form in which to add tables or other queries. You can specify criteria and even create more advanced SQL queries. Any query can be edited in Design View, no matter how it was created initially.

Wizard Mode

Create a query in Wizard Mode by choosing “Queries” from the main database object window and selecting “Create query by using wizard.” Select a table or query from the provided drop down list and use the buttons between the Available Fields and Selected Fields boxes to add or remove fields. You can select fields from multiple tables or queries. Press “Next” and choose what type of query summary you want. Options include “Detail” and “Summary.” With Detail, you will be able to see every field of every record included in the query. With Summary, you can choose calculations for your fields. Click “Next” and choose a name for your query. For easy recognition later, name the query based upon its purpose. For instance, if your query adds records to the User table, name the query User_Add. Click “Finish” to view the results of your query.

In addition to the simple Wizard Mode, there are also three other wizard modes available. Press the “New” button to view the other wizards. They include Crosstab Query, Find Duplicates Query and Find Unmatched Query. All three provide wizard prompts to help you create some of the more common advanced queries.

Design View

Create a query in Design View by choosing “Create a query in Design view.” Choose your desired tables and/or queries by selecting them one by one and choosing “Add.” Drag and drop your desired fields from the tables and queries onto the “Field” box in the query window. Once a field is added, you can choose sorting options, whether the field is shown in the query results or not and add criteria such as specific text to match.

If you want the tables or queries in your query to be related, drag the related field from one table or query to the related field in another table or query. The related fields must have the same type of information. For instance, if you want the Name field in Table1 to relate to Table2, Table2 must have a field containing names in the same format as Table 1 as well. Creating relationships will allow you to further filter results in your query. To view results, click the “View” button on the toolbar and select “Datasheet View.”

Types of Queries

There are multiple types of queries you can create in Access 2003. In Design View, you can specify the function of your query. Press the “Query Type” button on the toolbar. Options include Select, Update, Append, Delete, Crosstab and Make Table. Select and Crosstab queries allow you to view data from tables and queries. Update, Append and Delete queries are some of the most powerful because they actually manipulate tables by editing, adding or deleting records. Make Table queries allow you to create a new table with the information selected in your query.

Another type of query to create in Design View is the SQL query. SQL queries are created in a text editor and require at least basic knowledge of SQL. If you create a query in Design View, you can view the SQL version of the query by pressing the “View” button and choosing “SQL View.” You can further edit the query in SQL View at any time. Once edited in SQL View, the query will show up as an SQL query in your database object window. SQL queries are the most powerful type of queries you can create in Access 2003. For more information on SQL queries, see the tutorials at W3Schools and Paragon Corporation.