In this example we will use Access 2010 and the Northwind sample database. If you're using an earlier version of Access, you may wish to read Creating Queries in Older Versions of Microsoft Access.
Our goal in this tutorial is to create a query listing the names of all of our company's products, our desired target inventory levels and the list price for each item. Here's how we go about the process:
- Open your database. If you haven't already installed the Northwind sample database, be sure to do so before proceeding. Otherwise, go to the File tab, select Open and locate the Northwind database on your computer.
- Switch to the Create tab. In the Access ribbon, change from the File tab to the Create tab. This will change the icons presented to you in the ribbon. If you're not familiar with using the Access ribbon, read Access 2010 Tour: The User Interface.
- Click the Query Wizard icon. The query wizard simplifies the creation of new queries. We'll use it in this tutorial to introduce the concept of query creation. The alternative is to use the Query Design view, which facilitates the creation of more sophisticated queries but is more complicated to use.
- Select a Query Type. Access will ask you to choose the type of query you wish to create. For our purposes, we will use the Simple Query Wizard. Select this and click OK to continue.
- Select the appropriate table from the pull-down menu. The Simple Query Wizard will open. It includes a pull-down menu that should be defaulted to "Table: Customers". When you select the pull-down menu, you'll be presented with a listing of all the tables and queries currently stored in your Access database. These are the valid data sources for your new query. In this example, we want to first select the Products table which contains information about the products we keep in our inventory.
- Choose the fields you wish to appear in the query results. You can do this by either double-clicking on them or by single clicking first on the field name and then on the ">" icon. As you do this, the fields will move from the Available Fields listing to the Selected Fields listing. Notice that there are three other icons offered. The ">>" icon will select all available fields. The "<" icon allows you to remove the highlighted field from the Selected Fields list while the "<<" icon removes all selected fields. In this example, we want to select the Product Name, List Price, and Target Level from the Product table.
- Repeat steps 5 and 6 to add information from additional tables, as desired. In our example, we're pulling information from a single table. However, we're not limited to using only one table. That's the power of a query! You can combine information from multiple tables and easily show relationships. All you have to do is select the fields -- Access will line up the fields for you! Note that this works because the Northwind database has predefined relationships between tables. If you're creating a new database, you'll need to establish these relationships yourself. Read the article Creating Relationships in Microsoft Access 2010 for more information on this topic.
- Click on Next. When you're finished adding fields to your query, click the Next button to continue.
- Choose the type of results you would like to produce. We want to produce a full listing of products and their suppliers, so choose the Detail option here and click the Next button to continue.
- Give your query a title. You're almost done! On the next screen you can give your query a title. Select something descriptive that will help you recognize this query later. We'll call this query "Product Supplier Listing."
- Click on Finish. You'll be presented with the query results shown in the illustration above. It contains a list of our company products, desired target inventory levels and list prices. Notice that the tab presenting this results contains the name of your query.