Access 2010
Designing a Query
What are queries?
Video: Creating a Simple Query in Access 2010
Watch the video (4:32).Queries are a way of searching for and compiling data from one or more tables. Running a query is like asking a detailed question of your database. When you build a query in Access, you are defining specific search conditions to find exactly the data you want.
How are queries used?
Queries are far more powerful than the simple searches or filters you might use to find data within a table. This is because queries can draw their information from multiple tables. For example, while you could use a search in the customers table to find the name of one customer at your business or a filter on the orders table to view only orders placed within the past week, neither would let you view both customers and orders at once. However, you could easily run a query to find the name and phone number of every customer who's made a purchase within the past week. A well-designed query can give information you might not be able to find out simply by examining the data in your tables.
When you run a query, the results are presented to you in a table, but when you design one you use a different view. This is called Query Design view, and it lets you see how your query is put together.
Click the buttons in the interactive below to learn how to navigate the Query Design view.
Query Criteria
Entering query criteria lets you specify exactly what type of information you want your query to retrieve. Simply type the desired criteria in the Criteria: row of the field you wish to search.
Here, the criteria have been set so that the query will search for records with "Raleigh" in the City field, and zip codes that are not "27610", "27615", or "27616" in the Zip Code field.
Showing or Hiding Fields
You may wish to include fields in the design of your query, but hide them in the query results. To hide a field, simply uncheck the checkbox in the Show: row of that field.
Sorting
You can sort the data retrieved by a query. Simply click in the Sort: row of the field you wish to sort, and select either an Ascending or Descending sort. By default, query results are not sorted.
Field and Table Names
The first row of the Design Grid contains the names of the fields included in the query. Directly beneath each field name is the name of the table that field belongs to.
The Design Grid
The bottom part of the Query Design View is called the Design grid. It contains a table that lists all the fields included in the query. Within this table, you can set criteria to specify which information the query should retrieve.
The Object Relationship Pane
All of the tables you choose to include in your query will appear as small windows in the Object Relationship Pane. Each window contains a list of every available field within that table.
Run Query
After you have designed your query, click the Run Query command to view the results of the query in a table.
Query Views
Click the View drop-down arrow to switch to another view of your query. In most cases, you will only need to use two main views: Datasheet View and Design View.
Datasheet View lets you view your query results in the form of a table. Design View, featured here, allows you to view and modify the design of your query.
