�ݺ�ߣ

Access Chapter 3Maintaining a Database

ObjectivesAdd, change, and delete recordsSearch for recordsFilter recordsUpdate a table designFormat a datasheetUse action queries to update records2Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition

ObjectivesSpecify validation rules, default values, and formatsCreate and use single-valued and multivalued Lookup fieldsSpecify referential integrityUse a subdatasheetSort recordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition3

Plan AheadDetermine when it is necessary to add, change, or delete records in a databaseDetermine whether you should filter recordsDetermine whether additional fields are necessary or whether existing fields should be deletedDetermine whether validation rules, default values, and formats are necessaryDetermine whether changes to the format of a datasheet are desirableIdentify related tables in order to implement relationships between the tablesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition4

Starting AccessClick the Start button on the Windows Vista taskbar to display the Start menuClick All Programs at the bottom of the left Pane on the Start menu to display the All Programs list and then click Microsoft Office in the All Programs list to display the Microsoft Office listClick Microsoft Office Access 2007 on the Microsoft Office list to start Access and display the Getting Started with Microsoft Office Access windowIf the Access window is not maximized, click the Maximize button on its title bar to maximize the windowMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition5

Opening a DatabaseWith your USB flash drive connected to one of the computer’s USB ports, click the More button to display the Open dialog boxIf the Folders list is displayed below the Folders button, click the Folders button to remove the Folders listIf necessary, click Computer in the Favorite Links section and then double-click UDISK 2.0 (E:) to select the USB flash drive, as the new open location. (Your drive letter might be different.)Click JSP Recruiters to select the file nameMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition6

Opening a DatabaseClick the Open button to open the databaseIf a Security Warning appears, click the Options button to display the Microsoft Office Security Options dialog boxClick the Enable this content option buttonClick the OK button to enable the contentMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition7

Creating a Simple FormShow the Navigation pane if it is currently hiddenIf necessary, click the Client table in the Navigation pane to select itClick Create on the Ribbon to display the Create tabClick the Form button on the Create tab to create a simple formClick the Form View button to display the form in Form viewMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition8

Creating a Simple FormMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition9

Using a Form to Add RecordsClick the New (blank) record button on the Navigation bar to enter a new record, and then type the data for the new record as shown on the following slide. Press the TAB key after typing the data in each field, except after typing the data for the final field (Recruiter Number)Press the TAB key to complete the entry of the recordClick the Close ‘Client’ button to close the Client formClick the No button when asked if you want to save your changesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition10

Using a Form to Add RecordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition11

Searching for a RecordRight-click Client Form in the Navigation pane and click Open on the shortcut menu to open the form in Form viewHide the Navigation paneClick the Find button on the Home tab to display the Find and Replace dialog boxType MH56 in the Find What text boxClick the Find Next button in the Find and Replace dialog box to find client MH56Click the Cancel button in the Find and Replace dialog box to remove the dialog box from the screenMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition12

Searching for a RecordMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition13

Updating the Contents of a RecordClick in the Client Name field in the datasheet for client MH56 after the letter M to select the fieldPress the DELETE key twice to delete the letters auType the letters un after the letter MPress the TAB key to complete the change and move to the next fieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition14

Updating the Contents of a RecordMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition15

Deleting a RecordWith the Client Form open, click the record selector in the datasheet (the small box that appears to the left of the first field) of the record on which the client number is EA45Press the DELETE key to delete the recordClick the Yes button to complete the deletionClose the Client Form by clicking the Close ‘Client Form’ buttonMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition16

Deleting a RecordMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition17

Using Filter By SelectionOpen the Client form and hide the Navigation paneClick the City field on the first record in the datasheet portion of the form to select Berridge as the cityClick the Selection button on the Home tab to display the Selection menuClick Equals “Berridge” to select only those clients whose city is BerridgeMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition18

Using Filter By SelectionMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition19

Toggling a FilterClick the Toggle Filter button on the Home tab to toggle the filter and redisplay all recordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition20

Using a Common FilterBe sure the Home tab is selectedClick the City arrow to display the common filter menuPoint to the Text Filters command to display the custom text filtersClick Begins With to display the Custom Filter dialog boxMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition21

Using a Common FilterType Ber as the City begins with valueClick the OK button to filter the recordsClick the Toggle Filter button on the Home tab to toggle the filter and redisplay all recordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition22

Using a Common FilterMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition23

Using Filter By FormClick the Advanced button on the Home tab to display the Advanced menuClick Clear All Filters on the Advanced menu to clear the existing filterClick the Advanced button on the Home tab to display the Advanced menu a second timeClick Filter By Form on the Advanced menuMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition24

Using Filter By FormClick the Postal Code field, click the arrow that appears, and then click 80330Click the Amount Paid field, click the arrow that appears, and then click 0Click the Toggle Filter button on the Home tab to apply the filterMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition25

Using Filter By FormMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition26

Using Advanced Filter/SortClick the Advanced button on the Home tab to display the Advanced menu, and then click Clear All Filters on the Advanced menu to clear the existing filterClick the Advanced button on the Home tab to display the Advanced menu a second timeClick Advanced Filter/Sort on the Advanced menuExpand the size of the field list so all the fields in the Client Table appearInclude the Client Number field and select Ascending as the sort orderMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition27

Using Advanced Filter/SortInclude the Postal Code field and enter 80330 as the criterionInclude the Amount Paid field and enter 0 as the criterion in the Criteria row and >20000 as the criterion in the Or rowClick the Toggle Filter button on the Home tab to toggle the filter and view the results. Click the Client Form tab to view the Client tableClick Clear All Filters on the Advanced menuClose the Client FormMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition28

Using Advanced Filter/SortMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition29

Adding a New FieldShow the Navigation pane, and then right-click the Client table to display a shortcut menuClick Design View on the shortcut menu to open the Client table in Design viewClick the row selector for the Amount Paid field, and then press the INSERT key to insert a blank row above the Amount Paid rowClick the Field Name column for the new field. If necessary, erase any text that appearsType Client Type as the field name and then press the TAB keyMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition30

Adding a New FieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition31

Creating a Lookup FieldIf necessary, click the Data Type column for the Client Type field, and then click the arrow to display the menu of available data typesClick Lookup Wizard, and then click the ‘I will type in the values that I want.’ option button to indicate that you will type in the valuesClick the Next button to display the next Lookup Wizard screenClick the first row of the table (below Col1), and then type MED as the value in the first rowPress the DOWN ARROW key, and then type DNT as the value in the second rowMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition32

Creating a Lookup FieldPress the DOWN ARROW key, and then type LAB as the value in the third rowClick the Next button to display the next Lookup Wizard screenEnsure Client Type is entered as the label for the lookup column and that the Allow Multiple Values check box is NOT checkedClick the Finish button to complete the definition of the Lookup Wizard fieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition33

Creating a Lookup FieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition34

Adding a Multivalued FieldClick the row selector for the Amount Paid field, and then press the INSERT key to insert a blank rowClick the Field Name column for the new field, type Specialties Needed as the field name, and then press the DOWN ARROW keyClick the Data Type column for the Specialties Needed field, and then click Lookup Wizard in the menu of available data types to start the Lookup WizardClick the ‘I will type in the values that I want.’ option button to indicate that you will type in the valuesClick the Next button to display the next Lookup Wizard screenClick the first row of the table (below Col1), and then type CLS as the value in the first rowMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition35

Adding a Multivalued FieldEnter the remaining values from the first column in Table 3–1 on page AC 160. Before typing each value, press the TAB key to move to a new rowClick the Next button to display the next Lookup Wizard screenEnsure Specialties Needed is entered as the label for the lookup columnClick the Allow Multiple Values check box to allow multiple valuesClick the Finish button to complete the definition of the Lookup Wizard fieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition36

Saving the Changes and Closing the TableClick the Save button on the Quick Access Toolbar to save the changesClick the Close ‘Client’ buttonMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition37

Using an Updated QueryCreate a new query for the Client tableClick the Update button on the Design tab, double-click the Client Type field to select the field, click the Update To row in the first column of the design grid, and then type MED as the new valueClick the Run button on the Design tab to run the query and update the recordsClick the Yes button to make the changesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition38

Using an Updated QueryMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition39

Using a Delete QueryClear the gridClick the Delete button on the Design tab to make the query a Delete queryDouble-click the Postal Code field to select the fieldClick the Criteria row for the Postal Code field and type 80412 as the criterionMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition40

Using a Delete QueryRun the query by clicking the Run buttonClick the Yes button to complete the deletionClose the Query window. Do not save the queryMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition41

Specifying a Required FieldShow the Navigation pane, and then open the Client table in Design viewSelect the Client Name field by clicking its row selectorClick the Required property box in the Field Properties pane, and then click the down arrow that appearsClick Yes in the listMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition42

Specifying a Required FieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition43

Specifying a RangeSelect the Amount Paid field by clicking its row selector, click the Validation Rule property box to produce an insertion point, and then type >=0 and <=100000 as the ruleClick the Validation Text property box to produce an insertion point, and then type Must be at least $0.00 and at most $100,000 as the textMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition44

Specifying a RangeMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition45

Specifying a Default ValueSelect the Client Type field. Click the Default Value property box to produce an insertion point, and then type =MED as the valueMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition46

Specifying a Collection of Allowable ValuesMake sure the Client Type field is selectedClick the Validation Rule property box to produce an insertion point and then type =MED or =DNT or =LAB as the validation ruleClick the Validation Text property box and then type Must be MED, DNT, or LAB as the validation textMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition47

Specifying a Collection of Allowable ValuesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition48

Specifying a FormatSelect the Client Number fieldClick the Format property box and then type >Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition49

Saving the Validation Rules, Default Values, and FormatsClick the Save button on the Quick Access Toolbar to save the changesClick the No button to save the changes without testing current dataClose the Client tableMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition50

Saving the Validation Rules, Default Values, and FormatsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition51

Using a Lookup FieldOpen the Client table in Datasheet view and ensure the Navigation pane is hiddenClick in the Client Type field on the third record to display the arrowClick the down arrow to display the drop-down list of available choices for the Client Type fieldClick DNT to change the valueIn a similar fashion, change MED on the ninth record to LABMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition52

Using a Lookup FieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition53

Using a Multivalued Lookup FieldClick the Specialties Needed field on the first record to display the arrowClick the arrow to display the list of available specialtiesClick the CNA, PA, Phy, and RN check boxes to select the specialties for the first clientClick the OK button to complete the selectionUsing the same technique, enter the specialties given in Figure 3–47 on page AC 173 for the remaining clientsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition54

Using a Multivalued Lookup FieldMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition55

Resizing a Column in a DatasheetPoint to the right boundary of the field selector for the Specialties Needed field so that the mouse pointer becomes a doubled-ended arrowDouble-click the right boundary of the field selector for the Specialties Needed field to resize the field so that it best fits the dataUse the same technique to resize all the other fields to best fit the data. To resize the Amount Paid, Current Due, and Recruiter Number fields, you will need to scroll the fields by clicking the right scroll arrow shown in Figure 3–52 on page AC 175Save the changes to the layout by clicking the Save button on the Quick Access ToolbarClose the Client tableMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition56

Resizing a Column in a DatasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition57

Including Totals in a DatasheetOpen the Recruiter table in Datasheet view and hide the Navigation paneClick the Totals button on the Home tab to include the Total row in the datasheetClick the Total row in the Commission column to display an arrowClick the arrow to display a menu of available computationsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition58

Including Totals in a DatasheetClick Sum to calculate the sum of the commissionsClick the Total row in the Rate column to display an arrowClick the arrow to display a menu of available computationsClick Average to calculate the average of the ratesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition59

Including Totals in a DatasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition60

Removing Totals from a DatasheetClick the Totals button on the Home tab to remove the Total row from the datasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition61

Changing Gridlines in a DatasheetOpen the Recruiter table in Datasheet view, if it is not already openClick the box in the upper-left corner of the Datasheet selector to select the entire datasheetClick the Gridlines button on the Home tab to display the Gridlines galleryClick the Gridlines: Horizontal command in the Gridlines gallery to include only horizontal gridlinesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition62

Changing Gridlines in a DatasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition63

Changing the Colors and Font in a DatasheetWith the datasheet for the Recruiter table selected, click the Alternate Fill/Back Color button arrow to display the color paletteClick Aqua Blue (the second from the right color in the standard colors) to select aqua blue as the alternate colorClick the Font Color arrow, and then click Red (the second color in the bottom row) in the lower-left corner of standard colors to select Red as the font colorClick the Font box arrow, and then select Bodoni MT as the font. (If it is not available, select any font of your choice.)Click the Font Size box arrow and select 10 as the font sizeMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition64

Changing the Colors and Font in a DatasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition65

Closing the Datasheet Without Saving the Format ChangesClick the Close ‘Recruiter’ button to close the Recruiter tableClick the No button in the Microsoft Office Access dialog box when asked if you want to save your changesMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition66

Querying a Multivalued Field Showing Multiple Values on a Single RowCreate a query for the Client table and hide the Navigation paneInclude the Client Number, Client Name, Client Type, and Specialties Needed fieldsView the resultsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition67

Querying a Multivalued Field Showing Multiple Values on a Single RowMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition68

Querying a Multivalued Field Showing Multiple Values on Multiple RowsReturn to Design view and ensure the Client Number, Client Name, Client Type, and Specialties Needed fields are selectedClick the Specialties Needed field to produce an insertion point, and then type a period and the word Value after the word, Needed, to use the Value propertyView the resultsClose the query by clicking the Close ‘Query1’ buttonWhen asked if you want to save the query, click the No buttonMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition69

Querying a Multivalued Field Showing Multiple Values on Multiple RowsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition70

Specifying Referential IntegrityClick Database Tools on the Ribbon to display the Database Tools tabClick the Relationships button on the Database Tools tab to open the Relationships window and display the Show Table dialog boxClick the Recruiter table and then click the Add button to add the Recruiter tableClick the Client table and then click the Add button to add the Client tableMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition71

Specifying Referential IntegrityClick the Close button in the Show Table dialog box to close the dialog boxResize the field lists that appear so all fields are visibleDrag the Recruiter Number field in the Recruiter table field list to the Recruiter Number field in the Client table field list to open the Edit Relationships dialog box to create a relationshipClick the Enforce Referential Integrity check boxMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition72

Specifying Referential IntegrityClick the Cascade Update Related Fields check boxClick the Create button to complete the creation of the relationshipClick the Save button on the Quick Access Toolbar to save the relationship you createdClose the Relationships window by clicking the Close ‘Relationships’ buttonMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition73

Specifying Referential IntegrityMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition74

Using a SubdatasheetOpen the Recruiter table and hide the Navigation paneClick the plus sign in front of the row for Recruiter 24 to display the subdatasheetClose the datasheet for the Recruiter tableMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition75

Using a SubdatasheetMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition76

Using the Ascending Button to Order RecordsOpen the Client table in Datasheet view and hide the Navigation paneClick the City field on the first record to select the fieldClick the Ascending button on the Home tab to sort the records by CityClose the table. When asked if you want to save your changes, click the No buttonMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition77

Using the Ascending Button to Order RecordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition78

Quitting AccessClick the Close button on the right side of the Access title bar to quit AccessMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition79

SummaryAdd, change, and delete recordsSearch for recordsFilter recordsUpdate a table designFormat a datasheetUse action queries to update records80Microsoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition

SummarySpecify validation rules, default values, and formatsCreate and use single-valued and multivalued Lookup fieldsSpecify referential integrityUse a subdatasheetSort recordsMicrosoft Office 2007: Introductory Concepts and Techniques - Windows Vista Edition81

�ݺ�ߣ

Access chapter 3

More Related Content

Access chapter 3