CSS/417

1. CSS/417 Introduction to Database Management Systems Workshop 2

2. Access 2000 Session 3 Relating Tables, Modifying Table Structures, and Generating Reports

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) CSS/417 Workshop 2

4. Referential Integrity - p. 74 • Orphan record - a child record without a corresponding parent record • Referential integrity - makes certain that related records are present CSS/417 Workshop 2

5. 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 CSS/417 Workshop 2

6. 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 CSS/417 Workshop 2

7. 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 CSS/417 Workshop 2

8. 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 CSS/417 Workshop 2

9. 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 CSS/417 Workshop 2

10. 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 CSS/417 Workshop 2

11. 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 CSS/417 Workshop 2

12. 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 CSS/417 Workshop 2

13. Access 2000 Session 4 Creating Queries and Forms

14. 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 CSS/417 Workshop 2

15. 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 CSS/417 Workshop 2

16. 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 CSS/417 Workshop 2

17. 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 CSS/417 Workshop 2

18. 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 CSS/417 Workshop 2

19. 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 CSS/417 Workshop 2

20. 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 CSS/417 Workshop 2

21. 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) CSS/417 Workshop 2

22. 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 CSS/417 Workshop 2

23. Building a Chart • Use a Form Wizard • Specify the field to be used • The chart appears as the form CSS/417 Workshop 2