Selecting Data Using Queries in Microsoft Access 2013
- 3/15/2013
- 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
Filtering by single and multiple combinations of choices
Each row in the query grid allows you to specify a combination of choices, which when taken together will limit the data displayed. Each line on the query grid allows you to specify a separate set of choices. This means that a query can apply very sophisticated sets of criteria to limit the data returned.
Choosing to restrict the data displayed in each column bases the matching on ranges of values, sets of values, and patterns in the data.
Filter to match similar text
In the criteria for a text field, type *al*. Access changes this to Like “*al*”, which will display any record containing the letters al as part of the data in this column.
)
Add a second set of criteria
In the query grid, in the Or row, add further criteria by typing ba*. This displays all records where either the company name starts with the letters ba or the contact name includs the letters al.
)
The following table provides examples of using symbols in criteria to alter how data is matched.
Symbol |
Example |
Description |
* |
Like ‘*John’ or Like ‘John*’ or Like ‘*John*’ |
Wildcard searching in text fields. You do not need to type Like. |
# |
#01/10/2012# |
Matches a date. You do not need to type the pound (#) symbol. |
BETWEEN |
BETWEEN 1 and 4 BETWEEN #01/10/2012# AND #05/10/2012# |
Number or date range comparison. Includes the first and last criteria. |
IN |
IN(1,22,44,55) IN(‘France’,‘USA’) |
Set of data values. |
<>, >, <, <=, >=,!= |
> 25 <> ‘France’ |
General and arithmetic comparison; <> and != both mean not equal to. |
[?] |
Like ‘Fr[?]’ Would match Fra, FrB, frC ... |
Character pattern matching. Match any character (numbers and letters). Different from * in that [?] will include only records with only one character after ‘Fr’. That is, it wouldn’t return ‘France’. |
[#] |
Like ‘000[#]’ Would match 0001, 0002... |
Character pattern match (0–9) for a single character. |
[A-Z] |
Like ‘DNA[A-Z]’ Would match DNAA, dnaB... |
Character pattern match (A–Z) for a single character. |