Microsoft Access
Advanced Queries
Types of Queries
- Pattern Match Query
- List of Values Match Query
- Parameter Query
- Crosstab Query
- Find Duplicates Query
- Find Unmatched Query
- Top Values Query
Pattern Match Query
A pattern match query selects records with a value for the designated field that matches the pattern of a simple condition value.
List of Values Match Query
A list of values match query selects records whose value for the designated field matches one of two or more simple condition values.
Parameter Query
A parameter query displays a dialog box that prompts the user to enter one or more critieria values when the query is run.
Crosstab Query
Crosstab queries use aggregate functions to perform arithmetic operations on selected records.
Aggregate Functions:
- Avg = Average of the field values
- Count = Number of the nonnull field values
- First = First field value
- Last = Last field value
- Max = Highest field value
- Min = Lowest field value
- StDev = Standard deviation of field values
- Sum = Total of the field values
- Var = Varience of the field values
Find Duplicates Query
A find duplicate query is a select query that finds duplicate records in a table or query.
Find Unmatched Query
A find unmatched query is a select query that finds all records in a table or query that have no related records in a second table or query.
Top Values Query
A top values query limits the number or records in the query results. You can choose from preset values or enter an integer such as 5 or a percent such as 10% to find a specific number of records.
Anna Stirling
Associate Faculty
Computer Information Systems
Mt. San Jacinto College
Reference:
Adamski, J. J., & Finnegan, K. T. (2011). New Perspectives on Microsoft Acces 2010 Comprehensive. Boston: Course Technology.