- By Andrew Couch
- Selecting all columns from a table
- Selecting individual columns from one or more tables
- Joining tables to see unmatched or missing records
- Filtering by single and multiple combinations of choices
- Adding calculations with the expression builder
- Returning the top matched records
- Eliminating duplicate values
- Creating a summary calculation
- Prompting to filter data with parameters
- Creating a crosstab query with the Query Wizard
- Simplifying a problem with a query by using other queries
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
Prompting to filter data with parameters
If you have a query that you regularly use with different criteria, rather than changing the design of the query each time you want to change the criteria or creating multiple copies of the query with different criteria, you can define a set of parameters so that when you open the query it automatically asks you to enter values for these parameters and then displays the appropriate data.
This type of query can be also used to provide data for a form or report. This means that you can have a report that, when opened, prompts for parameters before displaying the results.
Prompt with query parameters
Type a name for the parameter in square brackets. Ensure that the name is different from any column name in your query, or the parameter will not work.
Type a data type for the parameter, and click OK.
Type the parameter name without square brackets. IntelliSense will assist you here.
When you open this query, type a value for the parameter to see the restricted set of results.