Microsoft 2010 Access Unit B
Query Wizard
(Create tab) Prompts you for information to create a new query.
Simple Query Wizard
(Create tab:Queries group) prompts you for information it needs to create a new query.
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.
Criteria
(pl.; Criteria = singular) - Specify conditions (criteria) to limit the number of records to be shown in a resulting datasheet. Criteria are NOT case sensitive.
Query
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.
Query
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.
Filters
Are NOT commonly used as the source of data for a form or report. But queries are.
Filters
Are NOT saved as an object in the database. But queries are.
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.
Datasheet printout
By default, contains the object name and current date in the header. The page number is in the footer.
Filters
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.
Filters
Can NOT be used to calculate fields. But queries can.
Filters
Can NOT be used to select a subset of fields in a datasheet. But queries can.
Filters
Can NOT calculate sums, averages, counts, and other types of summary statistics across records. But queries can.
Query Design View
Can also be used to create a new query from scratch.
AND Criteria
Criteria added to the same line on the query design grid.
AND Criteria
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).
OR Criteria
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!
query design grid (query grid)
Displays the field names, sort orders, and criteria used within the query.
Datasheet printout
Does NOT allow you to add custom headers, footers, images, or subtotals as reports do.
Deleting a field from a query
Does NOT delete it from the underlying table.
Filter by Selection
Filtering by the selected field value is a fast and easy way to filter the records for an exact match.
Is Null
Finds criterion in all records where NO ENTRY has been made.
Is Not Null
Finds criterion in all records where there is ANY ENTRY in the field, even if the entry is zero (0).
>
Greater than
>=
Greater than or equal to
Field List
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.
<
Less than
<=
Less than or equal to
AND Criteria
Means ALL criteria must be true to select the record. You narrow the number of records that are selected in your search.
<>
Not equal to
Query Design View
Presents the fields you can use for that query in small windows called field lists.
Report object
Primary Access tool to create a professional report.
Is Null/Not Null
Primary key fields cannot have a null entry.
Sorting
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.
query datasheet
Sometimes called the logical view of the data.
SQL
Structured Query Language
Wildcards
Symbols used by substitutes for unknown characters in a word. Two of the most common are: * = Represents any group of characters ? = Any single character
Queries
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.
Field Selector
Thin gray bar above each field in the query grid. This is used to move and delete fields.
Filter by Form
To filter for comparative data you must use this feature.
Filter by Form
Use to select more than one criterion or comparative data.
Filter by Selection
Use to select only one criterion.
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.
OR Criteria
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.
Filtering
When used on a table or query datasheet temporarily displays only those records that match given criteria.
Filters
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.
