An object where a subset of fields and records from one or more tables (or queries) are selected and presented in one datasheet. Remember that Access data is only physically stored in tables, even though it can be viewed and edited through other objects such as queries, forms, and reports. Each time a query is opened, it presents a current view of the latest updates to the database.
Query Design View
(Home tab) Use to add, delete, or move fields in an existing query, to specify sort orders or to add criteria. This view can also be used to design a query from scratch.
(pl.; Criteria = singular) - Specify conditions (criteria) to limit the number of records to be shown in a resulting datasheet. Criteria are NOT case sensitive.
In Query Design View (top left), it shows the list of fields available from the underlying table(s). The lower edge can be dragged down to show more fields.
Thin gray bar above each field in the query grid. This is used to move and delete fields.
Reorganizing records in ascending or descending order based on the contents of a field (otherwise they are organized by the data in the primary key field). To sort by MORE THAN ONE FIELD, create a query.
Will display only those records that match given criteria, but are not as powerful or flexible as queries. Queries are saved objects, while filters are temporary. CANNOT BE SAVED! They are a quick way to display data from a current datasheet.
Symbols used by substitutes for unknown characters in a word. Two of the most common are:
* = Represents any group of characters
? = Any single character
Criteria on the same row of the query design grid are AND criteria (records must meet the criteria for ALL of the criteria on one row to be displayed).
Criteria placed on different rows of the query design grid are OR criteria (records may be true for EITHER row to be displayed). If additional rows of criteria are added, the number of records displayed will increase since the record needs to be true for the criteria in only ONE of the rows to be displayed!
Can NOT calculate sums, averages, counts, and other types of summary statistics across records. But queries can.
CAN be used to select a subset of records in a datasheet. Also resulting datasheet can be used to enter and edit data. And resulting datasheet can be used to sort, filter, and find records. QUERIES can do all this too.
They ask "questions" about data. They present the answer in a datasheet, which you can sort, filter, and format. They can be used multiple times. Each time it is opened, it displays a current view of the latest updated to the database.
Allows you to select a subset of fields and records from one or more tables and then present the selected data as a single datasheet.
Simple Query Wizard
(Create tab:Queries group) prompts you for information it needs to create a new query.
Any edits you make in a query datasheet
Are permanently stored in the underlying tables, and are automatically updated in all views of the data in other queries, forms, and reports.
Query Design View
Used to add, delete, or move the fields in an existing query, to specify sort orders, or to add criteria to limit the number of records shown in the resulting datasheet.
Query Design View
Presents the fields you can use for that query in small windows called field lists.
query design grid (query grid)
Displays the field names, sort orders, and criteria used within the query.
When used on a table or query datasheet temporarily displays only those records that match given criteria.
Filter by Selection
Filtering by the selected field value is a fast and easy way to filter the records for an exact match.
Means ALL criteria must be true to select the record. You narrow the number of records that are selected in your search.
Is Not Null
Finds criterion in all records where there is ANY ENTRY in the field, even if the entry is zero (0).
When any one criterion must be true in order for the record to be selected. You increase the number of records selected in your search.
Does NOT allow you to add custom headers, footers, images, or subtotals as reports do.