A Guide to Microsoft Access Queries

Microsoft Access provides you with the tools you need to create both simple and complex queries. Queries are one of the most powerful features of Microsoft Access. Queries allow you to view existing filtered data from a table, edit data or add and delete data. Microsoft Access queries can be created to include both tables and existing queries and can contain multiples of both.

Microsoft Access queries are frequently used to provide data for forms and reports. When you use a form to add, delete or edit data, a query is used. The results in a report are typically generated from one or more queries. Queries can also be used in macros to perform multiple tasks at once.

Microsoft Access queries can be created in one of two ways. The first is through a wizard and the second is through a design view template. Let's look at the wizard options first as this is the option many users will prefer. Any queries created using a wizard can be edited at any point in design view.

You have two options on how to create a query using a wizard. You can either press "New" or select "Create a query using a wizard" while in the Queries section of the database window. The first option provides you with several types of wizards, while the second will only allow you to create a simple query. When you press "New," you have the options of Design View, which we will skip for now, Simple Query Wizard, Crosstab Query Wizard, Find Duplicates Query Wizard and Find Unmatched Query Wizard. The Simple Query Wizard is the only option you have if you choose "Create a query using a wizard." No matter which method you choose, follow the prompts in the wizard you chose to create your query.

Design view provides you with a template to add tables and queries. Press either "Design" or "Create a query in Design view." Both lead to the same template. Choose the tables and queries you want by selecting them from the "Show Table" list. Press "Add" to add a selected table or query. When finished, close the dialog box. Drag fields from the table and query field lists to the "Field" box. You can only add one field per cell.

Below each field you add, there are several other cells. The first is the name of the table or query the field originated from. The next allows you to sort query results based upon the selected field in either ascending or descending order. The next is the Show field, which allows you to hide or show the field results in the query. The final section is the Criteria field. This is where you can add specific criteria such as matching text or numbers, WHERE clauses, LIKE clauses and other types of criteria.

While in design view, you can create table and query relationships. These relationships only apply to the query you are creating. Relationships allow you to further filter query results by basing one table or query on another. For instance, you can relate the Date fields in two different tables or queries to only show records when Table 1 matches Query 2. Drag the related field from one table or query to the matching field in another table or query. Right click the line between the two for further relationship options such as basing Query 2 on Table 1 instead of vice versa.

In addition to creating basic queries, you can create different types of queries. In design view in Microsoft Access, press the "Query Type" button on the toolbar above the query window. Select the type of query you want to create. Types include Select Query, Update Query, Delete Query, Crosstab Query, Make Table Query and Append Query. The default type is Select.

For more control over your query, create an SQL query. You must be in design view first. SQL queries can be created after you have chosen a query type and created a base query in either design view or wizard mode. Press the "View" button on the toolbar and select "SQL View." This opens a text editor window. You can edit the SQL as much as necessary. If you do not know SQL, see the tutorial at W3Schools for examples.

While editing a query in design view, you can view the results at any time by selecting "Datasheet View" from the "View" button on the toolbar. As a side note, any time you create queries that edit or manipulate data in your tables, create a back up copy of the table in question or a backup of your database. If the query does not perform as you expected, you may lose or corrupt important data. When you are first learning how to create queries, create a test database. This will allow you to test any type of query without fear of data loss.