Microsoft 2010 Access Unit B

47 terms by mfawaz 

Create a new folder

Advertisement Upgrade to remove ads

Queries

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.

Query Wizard

(Create tab) Prompts you for information 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.

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.

Field Selector

Thin gray bar above each field in the query grid. This is used to move and delete fields.

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.

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.

Filter by Selection

Use to select only one criterion.

Filter by Form

Use to select more than one criterion or comparative data.

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

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!

Filters

Are NOT saved as an object in the database. But queries are.

Filters

Can NOT be used to select a subset of fields in a datasheet. But queries can.

Filters

Are NOT commonly used as the source of data for a form or report. But queries are.

Filters

Can NOT calculate sums, averages, counts, and other types of summary statistics across records. But queries can.

Filters

Can NOT be used to calculate fields. But queries can.

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.

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

<>

Not equal to

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.

SQL

Structured Query Language

query datasheet

Sometimes called the logical view of the data.

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.

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

Can also be used to create a new query from scratch.

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.

Deleting a field from a query

Does NOT delete it from the underlying table.

Filtering

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.

Filter by Form

To filter for comparative data you must use this feature.

AND Criteria

Means ALL criteria must be true to select the record. You narrow the number of records that are selected in your search.

AND Criteria

Criteria added to the same line on the query design grid.

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).

Is Null/Not Null

Primary key fields cannot have a null entry.

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.

Report object

Primary Access tool to create a professional report.

Datasheet printout

Does NOT allow you to add custom headers, footers, images, or subtotals as reports do.

Datasheet printout

By default, contains the object name and current date in the header. The page number is in the footer.

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set