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.