When you want to select details data native one or an ext sources, you can use a pick query. A select query helps you retrieve just the data the you want, and also helps you combine data from numerous data sources. You deserve to use tables and also other pick queries together data sources for a choose query. This topic provides an introduction of pick queries, and also gives steps for producing a select query, by making use of the ask Wizard or in style view.
You are watching: Which is the primary advantage of creating a new query based on an existing query?
If you want to usage the Northwind sample database come learn more about just how queries work, view the article development to queries.
In this article
When you desire to usage data, you hardly ever want come use all of the data indigenous one table. Because that example, as soon as you want to usage data from a contacts table, you normally want to look in ~ one certain record, or maybe simply the telephone number. Occasionally you want to incorporate data from more than one table, such together combining Customer details with order information. To choose the data the you want to use, you use a pick query.
A choose query is a database object the shows info in Datasheet view. A query go not store data, it display screens data that is stored in tables. A query can present data native one or much more tables, from various other queries, or native a combination of the two.
Benefits of utilizing a query
A query allows you:
View data just from the fields you are interested in viewing. When you open a table, you check out all the fields. A query is a handy method to save a choice of fields.
Note: A query just points come data, it does not keep data. When you conserve a query, you space not saving a copy of the data.
Combine data from number of data sources. A table normally only displays data the it stores. A query lets you pick and choose areas from assorted sources, and specify how the info should it is in combined.
Use expressions as fields. For example, you can use the Date duty as a field, or you can use the Format function with a field to manage the method the data native the field is formatted in the questions results.
View documents that satisfy criteria that you specify. When you open a table, you check out all the records. A questions is a handy way to conserve a an option of records.
Basic actions to develop a choose query
You can develop a choose query by utilizing the ask Wizard or by functioning in style view. Some style elements are not easily accessible when you use the wizard, but you can add these facets later by using style view. Return the two approaches are somewhat different from every other, the straightforward steps are essentially the same:
Choose the tables or queries the you want to usage as sources of data.
Specify the fields that you want to incorporate from the data sources.
Optionally, point out criteria to border the records that the query returns.
After you have created a pick query, you run it to check out the results. To operation a select query, you open it in Datasheet view. If you save the query, you have the right to reuse it whenever girlfriend need, for example, as a data source for a form, report, or an additional query.
Use the questions Wizard to develop a choose query
You can use the ask Wizard to automatically create a select query. As soon as you use the wizard, you have actually less regulate over the details that the questions design, yet the query is usually developed faster 보다 if girlfriend did not use the wizard. Moreover, the sorcerer’s can catch some an easy design mistakes and also prompt girlfriend to execute a various action.
Before girlfriend begin
If you use areas from data sources that space not related to each other, the questions Wizard asks girlfriend if you desire to create relationships. The wizard opens up the Relationships window for you, yet you must restart the magician if friend edit any type of relationships. Therefore, prior to you run the wizard, take into consideration creating any kind of relationships that your questions needs.
For an ext information about creating table relationships, watch the post Guide come table relationships.
Use the ask Wizard
On the Create tab, in the Queries group, click Query Wizard.
In the New Query dialog box, click Simple ask Wizard, and then click OK.
Next, you add fields. You can add up to 255 areas from as many as 32 tables or queries.
For each field, execute these 2 steps:
Under Tables/Queries, click the table or questions that contains the field.
Under Available Fields, double-click the field to add it to the Selected Fields list. If you desire to include all areas to your query, click the button with the dual right arrows (>>).
When you have added all the areas that girlfriend want, click Next.
If girlfriend did not add any number areas (fields the contain numeric data), skip front to step 9. If you included any number fields, the magician asks whether you desire the query to return details or an introduction data.
Do one of the following:
If you desire to watch individual records, click Detail, and then click Next. Skip front to step 9.
If you desire to see summarized numeric data, such as averages, click Summary, and then click Summary Options.
In the Summary Options dialog box, point out which fields you want to summarize, and also how you want to summarize the data. Just number fields are listed.
For every number field, pick one the the complying with functions:
Sum The query return the sum of all the worths of the field.
Avg The query returns the average of the worths of the field.
Min The query return the smallest worth of the field.
Max The query return the biggest value the the field.
If you desire the query outcomes to include a count of the documents in a data source, choose the proper Count documents in data resource name check box.
Click OK come close the Summary Options dialog box.
If girlfriend did not add a date/time field to the query, skip front to step 9. If you included a date-time field to the query, the questions Wizard asks you just how you would choose to group the day values. Because that example, expect you added a number field ("Price") and a date/time field ("Transaction_Time") to your query, and also then mentioned in the Summary Options dialog crate that you want to check out the typical value that the number ar "Price". Since you had a date/time field, you can calculate review values because that each distinctive date/time value, because that each day, because that each month, for each quarter, or because that each year.
Select the time period that you desire to usage to team the date/time values, and then click Next.
Note: In architecture view, you deserve to use one expression to group by any kind of time period you want, but the magician only supplies these choices.
On the last web page of the wizard, offer the query a title, specify even if it is you desire to open up or modify the query, and then click Finish.
If you pick to open the query, the query screens the selected data in Datasheet view. If you pick to change the query, the query opens in architecture view.
optimal of web page
Create a questions by functioning in architecture view
You can use style view to manually create a select query. Once you use style view, you have much more control end the details the the ask design, however it is less complicated to make architecture mistakes, and also it deserve to take much longer than using the wizard.
Create a query
Step 1: include data sources
When friend use design view, to add data sources, you include the data sources and fields in separate steps. However, girlfriend can always add much more data sources later if girlfriend want.
On the Create tab, in the Other group, click Query Design.
Double-click each data resource that you want to usage or select each data source and climate click Add.
When you add the data sources, if the sources currently have relationships defined between them, those relationships room automatically included to the query together joins. Join specify how data from associated sources should be combined. Accessibility also automatically creates a join between two tables if lock have areas have compatible data types and one ar is a primary key.
You might want to change the join that accessibility creates. Access determines what kind of join to create based on the partnership the join represents. If access creates a join yet there is no characterized relationship, access creates an within join.
If accessibility automatically create the correct joins once you include the data sources, you deserve to skip front to step 3: add output fields.
Use the very same data source several times
In some cases, you desire to join two duplicates of the very same table or query, called a self-join, that combines records from the very same table when there are corresponding values in the join fields. Because that example, say you have actually an employees table in i beg your pardon the ReportsTo ar for every employee"s record displays his or she manager"s ID rather of name. You might use a self-join to display screen the manager"s surname in every employee"s document instead.
When you include a data resource a 2nd time, accessibility appends _1 to the name of the 2nd instance. For example, if you added the employee table twice, the second instance would be called Employees_1.
Step 2: join related data sources
If the data resources that you add to a query currently have relationships, accessibility automatically create an inner join for every relationship. If referential integrity is enforced, access also display screens a "1" over the sign up with line to present which table is on the "one" next of a one-to-many relationship and also an infinity price (∞) to present which table is on the "many" side.
If you include queries to her query, and also have not developed relationships in between those queries, accessibility does not instantly create joins between those queries, or between queries and tables that space not related. If access does not produce joins as soon as you include data sources, you have to usually add them yourself. Data sources that room not join to any type of other data resource can reason problems with the query results.
You might likewise want to readjust the type of a join from one inner join to an outer join, so that your questions includes more records.
Add a join
To add a join, drag a ar from one data resource to a corresponding field on another data source.
Access display screens a line between the two fields to display that a join has actually been created.
Change a join
Double-click the join you desire to change.
The Join Properties dialog box appears.
In the Join Properties dialog box, testimonial the three options.
Click the option that you want to use, and then click OK.
After the joins are ready, you add output fields — areas that have data that you desire in the questions results.
Step 3: add output fields
You can easily add a ar from any kind of of the data sources that you added in action 1.
To include a field, drag the ar from a data source in the upper pane the the questions design window down come the Field heat of the architecture grid, in the bottom pane the the query design window.
When you add a field this way, access automatically filling in the Table heat of the architecture grid come reflect the data resource of the field.
Tip: If you want to quickly include all fields down come the ar row the the query design grid, double-click the table or query name from the top pane to highlight all the fields in that source and then traction them all under to the architecture grid in ~ the same time.
Use one expression as an output field
If you desire to execute calculations or usage a function to develop query output, you can use an expression as an calculation field. An expression deserve to use data from any type of of the ask data sources, and also functions, together as layout or InStr, and also can additionally contains constants and arithmetic operators.
In an empty shaft of the query design grid, right-click the Field row, and also then click Zoom on the shortcut menu.
Step 4: specify criteria
This step is optional.
You use criteria to border the records that her query returns, on the basis of whether field values accomplish the criteria that you specify.
Specify criteria for an output field
Specify any alternative criteria in the Or row, below the Criteria row.
If girlfriend specify alternating criteria, a ar value have the right to meet any of the listed criteria and also be had in the ask result.
Multiple field criteria
You have the right to use criteria through multiple fields. When you do, all the criteria in a given Criteria or Or row must be true because that the record to it is in included.
Specify criteria by making use of a ar that you don"t desire to output
You can add a ar to her query design and not encompass the field"s data in the query output. You execute this if you desire to usage the field"s values to border the questions results, yet don"t want to see the ar values.
Add the ar to the architecture grid.
Clear the check box in the Show heat for the field.
Specify criteria as you would certainly for an output field.
Step 5: summarize data
This action is optional.
You can want to summary data, specifically if her data is numeric. For example, you could want to watch the mean price, or full sales.
To summarize data in a query, you usage the Total row. By default, the Total row is not shown in style view.
With the query open up in style view, top top the design tab, in the Show/Hide group, click Totals.
Access display screens the Total row in the query style grid.
Step 6: view the results
To check out the ask results, top top the design tab, click Run. Accessibility displays the outcomes of your query in Datasheet view.
To make further transforms to the query, click Home > View > Design View come switch back to design view.
See more: Tracy Name Meaning Of Tracy In The Bible, Is Tracy A Hebrew Name
Change her fields, expressions, or criteria and rerun the query until it returns the data the you want.