Access Module 2

How can data be sorted?

- clicking the list arrow on a datasheet's column heading, then click a sorting option - using the Sort and Find buttons on the Home tab

means ALL criteria must be true for the record to be selected - created by entering 2 or more criteria in the same criteria row of the query design grid

AND criteria

sorts records based on the selected field in ascending order (0-9, A-Z)

Ascending button

Sorts records based on the selected field in descending order (Z-A, 9-0)

Descending button

dialog box accessed from the home tab that can be used to find or replace any series of characters in the current field or the entire datasheet

Find & Replace

finds all records where any entry has been made (even if 0)

Is Not Null

criterion that finds all records when no entry has been made in the field

Is Null

finds all records where no entry has been made

Is Null

are you required to know SQL to build or use Access queries?

No because of the query tools such as the ones in Query Design View

means any ANY ONE criterion must be true for the record to be selected - created by entering 2 or more criteria on DIFFERENT criteria rows of the query design grid - also created by entering 2 or more criteria in the same criteria cell separated by OR

OR criteria

view used to add, delete, or move fields in an existing query; to specify sort orders; or to add specific criteria - can create a new query from scratch

Query Design View

the lower pane in Query Design View

Query Grid

a query is a set of what kind of instructions?

SQL (Structured Query Language)

located on the file tab allowing you to save the entire database (and all objects it contains) or just the current object with a new name

Save As command

a tool provided by Access to create a new query - prompts you for information needed to create a query

Simple Query Wizard

A query is sometimes called a logical view of data because: a. Queries do not store data - they only display a view of data b. You can create queries with the Logical Query Wizard c. Queries contain logical criteria d. Query naming conventions are logical

a. Queries do not store data - they only display a view of data

provides advanced filter features such as filter by form, save as query, and clear all filters

advanced button

SQL stands for which of the following? a. Standard Query Language b. Special Query Listing c. Structured Query Language d. Simple Query Listing

c. Structured Query Language

which of the following describes OR criteria? a. Selecting a subset of fields and/or records to view as a datasheet from one or more tables b. Reorganizing the records in either ascending or descending order based on the contents of one or more fields c. Using two or more rows of the query grid to select only those records that meet given criteria d. Using multiple fields in the query design grid

c. Using two or more rows of the query grid to select only those records that meet given criteria

when you format a datasheet:

can: change font size, font face, colors, & gridlines cannot: add custom headers, footers, images, subtotals

to delete an unneeded table from Query Design View:

click its title bar and press [delete]

found in Query Design View and limits the number of records showing in the resulting datasheet

criteria

limiting conditions used to restrict the number of records that are selected in a query

criteria

tests, or limiting conditions, for which the record must be true to be selected for the query datasheet - set in Query Design View

criteria

rules that specify how to enter criteria •Quotation marks (") around text criteria and pound signs (#) around date criteria are automatically added by Access. Criteria in Number, Currency, and Yes/No fields are not surrounded by any characters

criteria syntax

Which of the following is NOT true about a query? a. A query can select fields from one or more tables in a relational database b. A query can be created using different tools c. An existing query can be modified in Query Design View d. A query is the same thing as a filter

d. A query is the same thing as a filter

AND criteria: a. Determine sort orders b. Determine fields selected for a query c. Help set link lines between tables in a query d. Must all be true for the record to be selected

d. Must all be true for the record to be selected

To add a new table to Query Design View:

drag it from the navigation pane or double click it under the show tables button

small windows that display field names

field lists

the thin gray bar above each field in the query grid

field selector

creates a temporary subset of records

filter

provides a list of values in the selected field that can be used to customize a filter

filter button

filters by comparative data - equal to, greater than, etc.

filter by form

a fast and easy way to filter the records for an exact match

filter by selection

found after selecting the "selection" button filtering by a selected field value fast and easy way to filter records for an exact match

filter by selection

provides a temporary way to display a subset of records that match given criteria - not used to calculate sums, averages, counts, etc. - removed when the datasheet is closed

filtering

opens the find and replace dialog box, which allows you to find data in a single field or in the entire datasheet

find button

helps you navigate to the first, previous, last, or new record

go to button

>

greater than

>=

greater than or equal to

As you add rows of OR criteria to the query design grid, you _______ the number of records selected for the resulting datasheet since the record needs to match ONLY ONE of the criteria rows to be selected for the datasheet

increase

identifies which fields are used to establish a relationship between two tables

join line

If you use the fields of two or more related tables in the query, the relationship between two tables is displayed with a ____ _____ identifying which fields are used to establish the relationship.

join line (also called a link line)

<

less than

<=

less than or equal to

Query Design View presents the fields you can use for a query in small windows called field ____.

lists

because a query doesn't physically store the data, a query datasheet is sometimes called a ____ ____ of the data

logical view

When a query is created with the Query Wizard, are the one and infinity symbols shown on the link line?

no

are both filters and queries commonly used as the source of data for a form or report?

no - only queries

are both filters and queries saved as an object in a database?

no - only queries

can both filters and queries be used to calculate sums, averages, counts, and other types of summary statistics across records?

no - only queries

can both filters and queries be used to create calculated fields?

no - only queries

can both filters and queries be used to select a subset of fields in a datasheet?

no - only queries

<>

not equal to

sorting in datasheet view:

only shows sorting at that time

queries are:

permanent

allows you to select a subset of fields and records from one or more tables & present the selected data as a single datasheet

query

answers a question about the information in the database - only select the data you need to answer the question

query

the lower pane of the window that displays the field names, sort orders, and criteria used within the query is a:

query design grid (query grid)

removes the current sort order

remove sort button

opens the find and replace dialog box, which allows you to find and replace data

replace button

you can hide/unhide and freeze/unfreeze fields (keeps field on screen at all times) by

right clicking the field name and select the option from the shortcut menu

helps you select a single record or all records in a datasheet

select button

filters records that equal, do not equal, or are otherwise compared with the current value

selection button

putting records in ascending or descending order based on the values of a field

sorting

rules that determine how criteria are entered

syntax

data is physically stored in

tables

filters are:

temporary

sorting in design view:

the data will always be sorted

when you add AND criteria, what happens to the number or records that are selected because the record must be true for ALL criteria?

the number of records is narrowed

applies or removes the current filter

toggle filter button

True or False: Any edits you make in a query are permanently stored in the underlying tables, and are automatically updated in all views of the data in other queries, forms, and reports

true

used to search for a pattern of characters

wildcard

Are used to search for a pattern - represents any character - entered as criteria ? - used to search for a single character * - used to search for any number of characters

wildcard characters

can both queries and filters be used to select a subset of records in a datasheet?

yes

can the resulting datasheet for both queries and filters be used to enter and edit data?

yes

can the resulting datasheet for filters and queries be used to sort, filter, and find records?

yes

do you enter and edit data in a query datasheet the same way you do in a table datasheet?

yes

does sorting and finding data work the same way for queries as it does for tables?

yes