MIS Exam 2

Expression Builder (Calculated Fields)

A field that can be computed from other fields. Users will not be able to update this field instead Access will automatically perform the necessary calculation whenever you display or use this field in any way.

Foreign Key

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

Parameter Query

A parameter query is a query that prompts for input whenever is it used. You enter a parameter rather than a specific value as the criterion. You create one by enclosing a value in a criterion in square brackets. The value in the brackets must not match the name of any field. If you enter a field name in any of the brackets Access assumes you want that particular field and does not prompt the user for input.

Subforms

A subform is a form that is inserted in another form. The primary form is called the main form, and the form that is enclosed in form is called the subform. A form/subform combination is sometimes referred to as a hierarchical form, a master/detail form, or a parent/child form. Subforms are especially effective when you want to show data from tables or queries that have a one-to-many relationship. A one-to-many relationship is an association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.

Operator (<, >, <>)

An operator is a sign or symbol that specifies the type of calculation to perform within an expression. < Returns True if the first value is less than the second value. > Returns True if the first value is greater than the second value. <> Returns True if the first value is not equal to the second value.

Different Controls (Logo, Label, Dashed Line etc, Bound controls, unbound controls).

Bound controls are used to display data that comes from the database such as client number and name. Unbound controls are not associated with data from the database and are used to display such things as the report's title. Calculated controls are used to display data that is calculated from other data such as totals.

Data Validity (Default Values, Validation Rules, Lookup Lists, Input Mask

Default values are values that Access will display on the screen in a particular field before the user begins adding a record. Validation rules are rules that a user must follow when entering the data, Access will prevent users from entering data that does not follow the rules. An input mask specifies how data is to be entered and how it will appear. You can enter an input mask directly or you can use the Input Mask Wizard which allows you to select from a list of the most frequently used input masks.

Filtering data in a table

Filtering with one column includes options to Sort by Color and Filter by color. If you need to restrict the records you can filter by one or more of the other columns. Additional filters are applied to the already currently filtered data and reduces the records that are displayed. Clearing filters allows for you to redisplay all of the data in a filtered table. A filter that selects more than one item from the list of items in a single column uses the OR condition which requires that only one of the selected criteria be true for a record to be displayed. Criteria filters enable you to specify various conditions in addition to those that are based on an "equals" criterion.

Key words (and, or, not, Null, )

In an AND criterion, each individual criterion must be true in order for the compound criterion to be true. An OR criterion is true provided either individual criterion is true.

Query Design Grid

Located in the lower pane of the window. You add the fields you want included in the query to the Field row in the grid. Only the fields that appear in the design grid will be included in the results of the query.

Referential Integrity

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

Different Sections in a Report (Headers, footers, Detail section)

Report Header: At the beginning of the report.Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header. Report Footer: At the end of the report. In Design view, the report footer appears below the page footer. However, in all other views (Layout view, for example, or when the report is printed or previewed), the report footer appears above the page footer, just after the last group footer or detail line on the final page.Use the report footer to print report totals or other summary information for the entire report. Detail Section: Appears once for every row in the record source. This is where you place the controls that make up the main body of the report.

Different Views of Table

Report view shows the report on the screen. Print preview shows the report as it will appear when printed. Layout view is similar to Report view in that it shows the report on the screen, but it also allows you to make changes to the report. Design view also allows you to make changes but it does not show you the actual report, it is most useful when the changes you need to make are especially complex.

Data Aggregate

Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values. Includes: average, count, max, etc..

Aggregate Data

SQL has built-in functions also called aggregate functions to perform various calculations including COUNT, SUM, AVG, MAX and MIN.

Cascade Update

Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.

Group and Sort Tools

The fastest way to add grouping, sorting, or totals to a desktop database report is to right-click the field to which you want to apply the group, sort, or total, and then click the desired command on the shortcut menu.

Primary Key

The unique identifier. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

Different Views of a form

When working with a form in Design view, you can see the effect of the changes you have made by viewing the form in Form view.

Wildcard * vs. ?

Wildcards are symbols that represent any character or combination of characters. One common wildcard, the asterisk (*) represents any collection of characters. The question mark is another wildcard symbol that represents any individual character.

Importing and Exporting Data

You can export or copy data from an Access database so that another application can use the data. The application that will receive the data determines the export process to be used. Importing data means to make a copy of the data in a table in the Access database. When importing you can either create a new table or you can add the records to an existing table, this is appropriate if you have already created the table provided the column headings in the worksheet match the field names in the table.

Adding Fields to a Report

You can quickly add fields to a form or report by using the Field List pane. When you double-click a field in the Field List pane or if you drag a field from the list to your form or report, Microsoft Office Access 2007 automatically creates the appropriate control to display the field — for example, a text box or check box — and then binds the control to that field.

Adding Fields to a form

You can quickly add fields to a form or report by using the Field List pane. When you double-click a field in the Field List pane or if you drag a field from the list to your form or report, Microsoft Office Access 2007 automatically creates the appropriate control to display the field — for example, a text box or check box — and then binds the control to that field.

Sorting data in a table

You can sort data in ascending and or descending order. Ascending arranges text alphabetically from A to Z, and descending order arranges text from Z to A. In both ascending and descending order, blank fields are placed at the end of tables. The first sort field is known as the primary sort field and the secondary sort field is the second sort field, 64 sort fields can be applied. A custom list indicates the sequence in which you want data ordered.

Properties of a Report

You can use the Report property to refer to a report or to refer to the report associated with a subreport control. Applies to: CONTROL OBJECT, Report Object, SubForm Object, SubReport Object.