You can add a table or query if the data you need isn't in the query, or remove a table or query if you decide you don't need them. Once you add the tables or queries you need, you can then add the fields that you want to work with to the design grid, or remove them if you decide you don't need them.
A join line between field lists tells Microsoft Access how the data in one table is related to the data in the other.
You drag a field from the field list to a column in the design grid to show the field in the query results.
You can add the values in a field or do other computations with the data by specifying the type of calculation to perform.
Use an aggregate function, such as Sum or Avg, to calculate one amount for all the records in each field in the design grid.
Use Group By to calculate separate amounts for groups of records in a field.
Limit results by using criteria
You can limit the records that you see in the query's results or the records that are included in a calculation by specifying criteria.
To limit the records in the query's results, enter criteria in one or more fields.
Use the Or row for alternative criteria in the same field.
Enter criteria for different fields. For example, for orders between 6/1/01 and 6/15/01 ...
... calculate total order amounts, but display only those that are more than $100,000.
You can sort the query's results by specifying a sort order in the design grid.
If you specify a sort order for more than one field, Microsoft Access sorts the leftmost field first, so you should arrange the fields you want to sort from left to right in the design grid.
Sort by ascending or descending order, or remove a sort.
Use the asterisk in the query design grid
When you use the asterisk, you must add fields along with the asterisk to the design grid before you can sort records or specify criteria for those fields.
When you drag more than one field at a time, Microsoft Access places each field in a separate column. If you drag the asterisk to the grid, Microsoft Access places the table or query name in one column and attaches a period and asterisk to the name (for example, Categories.*). After either operation, the datasheet looks the same.
If you type an asterisk in the Field row rather than dragging it, you must type the table name also. For example, type Customers.*.
Considerations when designing or modifying queries in a multiuser environment
If you open a query to make design changes and someone else modifies the query design before you have finished, Microsoft Access displays a message telling you that the design has changed since you started working. You can either replace the new changes with your changes or save your version of the query with a different name. If others are using the query to view data while you're changing the design, they don't see the design changes until they close and reopen the query.