css/417 introduction to database management systems workshop 2

CSS/417

Introduction to Database Management Systems

Workshop 2

Access 2000

Session 3Relating Tables, Modifying

Table Structures, and Generating Reports

CSS/417 Workshop 2 3

Relating Tables - p. 74 Command sequence - Tools, Relationships Parent table - table used as the main table

for a relationship Child table - the related table, often has

several records for each record in the parent table

Relationships for tables in the Membership database (Table 3.1)

Parent/Child table relationships (Table 3.2)


Referential Integrity - p. 74 Orphan record - a child record

without a corresponding parent record

Referential integrity - makes certain that related records are present


Table Links - p. 75 Permanent link - established using

the Relationships command is always in effect once defined

Transient link - defined using the query-by-example feature of Access


Subdatasheets Automatically created when a

relationship between tables is created Indicated by a column of plus signs

(+) Click a plus sign to see the related

records for the record containing the plus sign

Click the minus sign to close the display of the related records


Controlling Data Entry and Display

Field Properties box of a field Format - can be used to control how dates

display Input Mask - can be used to control how you

enter data (Table 3.4) Default Value - can be used to specify the

automatic contents of a field Validation Rule/Validation Text

Validation Rule - expression that is used to evaluate data entered

Validation Text - text that appears in an alert box if the data entered does not meet the validation rule requirements

These two fields are typically used together


Replace - p. 92 Replacing Text In A Table

Accomplished using the Replace in field dialog box

Accessed using the Edit, Replace command sequence

Access does not display a record before the contents of a field are altered


Deleting Records - p. 92 Commands

Edit, Delete Del key Delete toolbar button Timely Tip on p. 92

Access displays the prompt shown in Figure 3.29 for verification

Deleted records cannot be restored to the data table


Creating Reports - p. 94 Report template - contains the report

format, headings, and fields to be included in a report

Building a Report by Using a Report Wizard Use the Report tab of the database

window Report Wizard - a series of dialog boxes

that steps you through the process of building a report


Report Design Window Menu Bar and Toolbar Rulers - vertical

and horizontal Report Layout (Figure 3.38) Toolbox (Table 3.5) Field List Box - Figure 3.40 - used for

adding fields to a report template Properties Sheet or Section Detail Sheet -

controls the appearance of a report object

Scroll Bars


Redesigning a Report Able to move field and title boxes

around the design template via a drag operation

Able to resize the report bands using selection handles

Able to add additional titles Able to align text as well as change

the font and size of type used

Access 2000

Session 4Creating Queries and Forms


Definitions - p. 120 Query - a set of instructions that

specifies how Access should organize or change your data

Query Wizards - dialog boxes that step you through the query development process


Dynasets

Dynaset - contains the results of a query in a window

Can be used like a regular table Most useful when you work with fields from

several tables Any changes that are made to a dynaset

field are also made to the original record If you save a dynaset as a table, any

changes that you make to it are not automatically reflected in the tables that were originally used as input


Creating a Query Activate the Queries tab of the

Database window Click the New button Select the type of query Interact with the dialog boxes


Query Design Window - p. 124

QBE grid - controls which fields appear in the dynaset

Parts of the grid Field - contains the name of the field Table - contains the field’s table name Sort - controls the order of displayed data Show - displays a field in the dynaset Criteria - enter a relationship condition to

display selected records - Table 4.1 and Table 4.2, 4.4


Query Calculations - p. 130 Able to embed calculations in a

query SQL aggregate functions (Table 4.5) Activated by clicking the Total

toolbar button Adds a Total line to the QBE query

grid


Joining Tables Tables can be joined for

performing a query Tables being joined must have a

common field (a field with the same values from table to table)

Linking performed via a drag operation


Forms - p. 141 Form - provides for customized

data entry instead of using the datasheet window

Found on the Forms tab of the database window

Form Wizards - a series of dialog boxes prompting you about your form requirements


Conditional Formatting Format, Conditional Formatting

commands Can be used for forms or reports

Examines the contents of a field Based upon field’s contents able to

specify: Font/Font size/Font color Background color Text attributes (boldface, underline, italics)


Calculations in a Form A text box must be created to hold

the calculated field The calculation itself is specified

using that field’s Properties box Get to Data tab Enter formula in the Control Source text

box Can be built using the Expression Builder


Building a Chart

Use a Form Wizard Specify the field to be used The chart appears as the form

css/417 introduction to database management systems workshop 2

Documents

related table

reportcss417 workshop

presentcss417 workshop

formscss417 workshop

reportscss417 workshop

alteredcss417 workshop

accesscss417 workshop

togethercss417 workshop