When you want to select specific data from one or more sources, you can use a select query. A select query helps you retrieve only the data that you want, and also helps you combine data from several data sources. You can use tables and other select queries as data sources for a select query. This topic provides an overview of select queries, and gives steps for creating a select query, by using the Query Wizard or in Design view. Show
If you want to use the Northwind sample database to learn more about how queries work, see the article Introduction to queries. In this article
OverviewWhen you want to use data, you rarely want to use all of the data from one table. For example, when you want to use data from a Contacts table, you usually want to look at one specific record, or maybe just the telephone number. Sometimes you want to combine data from more than one table, such as combining Customer information with Order information. To select the data that you want to use, you use a select query. A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two. Benefits of using a queryA query lets you:
Basic steps to create a select queryYou can create a select query by using the Query Wizard or by working in Design view. Some design elements are not available when you use the wizard, but you can add these elements later by using Design view. Although the two methods are somewhat different from each other, the basic steps are essentially the same:
After you have created a select query, you run it to see the results. To run a select query, you open it in Datasheet view. If you save the query, you can reuse it whenever you need, for example, as a data source for a form, report, or another query. Use the Query Wizard to create a select queryYou can use the Query Wizard to automatically create a select query. When you use the wizard, you have less control over the details of the query design, but the query is usually created faster than if you did not use the wizard. Moreover, the wizard can catch some simple design mistakes and prompt you to perform a different action. Before you beginIf you use fields from data sources that are not related to each other, the Query Wizard asks you if you want to create relationships. The wizard opens the Relationships window for you, but you must restart the wizard if you edit any relationships. Therefore, before you run the wizard, consider creating any relationships that your query needs. For more information about creating table relationships, see the article Guide to table relationships. Use the Query Wizard
Top of Page Create a query by working in Design viewYou can use Design view to manually create a select query. When you use Design view, you have more control over the details of the query design, but it is easier to make design mistakes, and it can take longer than using the wizard. Create a query
Step 1: Add data sourcesWhen you use Design view, to add data sources, you add the data sources and fields in separate steps. However, you can always add more data sources later if you want.
Automatic joinsWhen you add the data sources, if the sources already have relationships defined between them, those relationships are automatically added to the query as joins. Joins specify how data from related sources should be combined. Access also automatically creates a join between two tables if they have fields have compatible data types and one field is a primary key. You might want to adjust the joins that Access creates. Access determines what type of join to create based on the relationship the join represents. If Access creates a join but there is no defined relationship, Access creates an inner join. If Access automatically creates the correct joins when you add the data sources, you can skip ahead to Step 3: Add output fields. Use the same data source several timesIn some cases, you want to join two copies of the same table or query, called a self-join, that combines records from the same table when there are matching values in the joined fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead. When you add a data source a second time, Access appends _1 to the name of the second instance. For example, if you added the Employees table twice, the second instance would be named Employees_1. Step 2: Join related data sourcesIf the data sources that you add to a query already have relationships, Access automatically creates an inner join for each relationship. If referential integrity is enforced, Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol (∞) to show which table is on the "many" side. If you add queries to your query, and have not created relationships between those queries, Access does not automatically create joins between those queries, or between queries and tables that are not related. If Access does not create joins when you add data sources, you should usually add them yourself. Data sources that are not joined to any other data source can cause problems with the query results. You might also want to change the type of a join from an inner join to an outer join, so that your query includes more records. Add a join
Change a join
After the joins are ready, you add output fields — fields that have data that you want in the query results. Step 3: Add output fieldsYou can easily add a field from any of the data sources that you added in step 1.
Use an expression as an output fieldIf you want to perform calculations or use a function to produce query output, you can use an expression as an output field. An expression can use data from any of the query data sources, as well as functions, such as Format or InStr, and can also contains constants and arithmetic operators.
Step 4: Specify criteriaThis step is optional. You use criteria to limit the records that your query returns, on the basis of whether field values meet the criteria that you specify. Specify criteria for an output field
Multiple field criteriaYou can use criteria with multiple fields. When you do, all the criteria in a given Criteria or Or row must be true for the record to be included. Specify criteria by using a field that you don't want to outputYou can add a field to your query design and not include the field's data in the query output. You do this if you want to use the field's values to limit the query results, but don't want to see the field values.
Step 5: Summarize dataThis step is optional. You might want to summarize data, especially if your data is numeric. For example, you might want to see the average price, or total sales. To summarize data in a query, you use the Total row. By default, the Total row is not displayed in Design view.
Step 6: View the resultsTo see the query results, on the Design tab, click Run. Access displays the results of your query in Datasheet view. To make further changes to the query, click Home > View > Design View to switch back to Design view. Change your fields, expressions, or criteria and rerun the query until it returns the data that you want. Top of Page What does a query display in Access?A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two.
What does the query design grid display?The query design grid in Query Design view is the fastest way to get a query going, and to see visually that you're selecting and sorting on the right fields. However, Query Design view also has the SQL view window for accessing the SQL statement directly, as well as the datasheet view for testing.
What is the query object in Access?Query. An object that provides a custom view of data from one or more tables. 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.
|