How to query MS Access database?
Combine data from several data sources. A table usually only displays data that it stores. A query lets you pick and choose fields from various sources, and specify how the information should be combined.
Use expressions as fields. For example, you could use the Date function as a field, or you could use the Format function with a field to control the way the data from the field is formatted in the query results.
View records that meet criteria that you specify. When you open a table, you see all the records. A query is a handy way to save a selection of records.
Basic steps to create a select query
You 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:
- Optionally, specify criteria to limit the records that the query returns.
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 query
You 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 begin
If 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.
Use the Query Wizard
- On the Create tab, in the Queries group, click Query Wizard. Note if you're using Access 2007, click Create > Other > Query Wizard.
- In the New Query dialog box, click Simple Query Wizard, and then click OK.
- Next, you add fields. You can add up to 255 fields from as many as 32 tables or queries.
- Under Available Fields, double-click the field to add it to the Selected Fields list. If you want to add all fields to your query, click the button with the double right arrows (>).
- When you have added all the fields that you want, click Next.
If you did not add any number fields (fields that contain numeric data), skip ahead to step 9. If you added any number fields, the wizard asks whether you want the query to return details or summary data.
Do one of the following:
- If you want to see individual records, click Detail, and then click Next. Skip ahead to step 9.
- If you want to see summarized numeric data, such as averages, click Summary, and then click Summary Options.
In the Summary Options dialog box, specify which fields you want to summarize, and how you want to summarize the data. Only number fields are listed.
Avg The query returns the average of the values of the field.
Min The query returns the smallest value of the field.
Max The query returns the largest value of the field.
If you want the query results to include a count of the records in a data source, select the appropriate Count records in data source name check box.
Click OK to close the Summary Options dialog box.
If you did not add a date/time field to the query, skip ahead to step 9. If you added a date-time field to the query, the Query Wizard asks you how you would like to group the date values. For example, suppose you added a number field ("Price") and a date/time field ("Transaction_Time") to your query, and then specified in the Summary Options dialog box that you want to see the average value of the number field "Price". Because you included a date/time field, you could calculate summary values for each unique date/time value, for each day, for each month, for each quarter, or for each year.
Select the time period that you want to use to group the date/time values, and then click Next.
Note: In Design view, you can use an expression to group by any time period you want, but the wizard only offers these choices.
On the last page of the wizard, give the query a title, specify whether you want to open or modify the query, and then click Finish.
If you choose to open the query, the query displays the selected data in Datasheet view. If you choose to modify the query, the query opens in Design view.
Create a query by working in Design view
You 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.
Step 1: Add data sources
When you use Design view, because you use the Show Table dialog box 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.
- On the Create tab, in the Other group, click Query Design. Note if you're using Access 2007, click Create > Other > Query Design.