nder Tables/Queries, click the table or query that contains the field.
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.
For each number field, choose one of the following functions:
Sum The query returns the sum of all the values of the field.
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.
Create a query
Step 1: Add data sources
When 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.
On the Create tab, in the Other group, click Query Design.
Double-click each data source that you want to use or select each data source and then click Add.
When 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 times
In 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 join