Maintaining your Database

1. Maintaining your Database Chapter Two

2. Maintaining Access Data Maintenance - the process of keeping data current Maintenance Tasks: • adding new records • removing old records • changing values in existing records

3. Tools to Assist in Maintenance • Ordering table columns • Sorting ordered data • Using the Find tool to locate specific records

4. Ordering Table Columns Improves readability of table when field the table will be sorted on is leftmost in the table • In datasheet view, click on the field selector of the column you want to move • Click and drag the column to its new position

5. Ordering Table Columns At a glance, it’s not obvious how this table has been sorted Moving the ZipCode field to the leftmost position makes the sort on ZipCode more obvious

6. Sorting Tables Sorting on one field • Click in the Sort field, then use the Sort Ascending or Sort Descending button to achieve the appropriate sort

7. Sorting Table by Primary and Secondary Fields Click the Sort Ascending button to complete sort Select LastName (primary) and FirstName (secondary) Fields Drag CstmrNmbr column to new location Select column to be moved

8. Sorting Records • How do Ascending and Descending Sorts deal with different types of data?

9. Finding Specific Data Values Click in the column to search Click the Find button

10. Finding Specific Data Values Enter Find What criteria Click Find Next to find first instance of Find criteria, click additional times as needed to find more instances of search string

11. Finding and Replacing Values • Click in column that replacement will occur in • Click the Find button on the toolbar • Click on the Replace Tab of the Find and Replace dialog box • Enter Find and Replace criteria, click Replace to replace values one at a time, Replace All to do all replacements at once Text to be replaced Replacement text Find criteria Field to be searched

12. Find and Replace Criteria

13. Wildcards in Access • Used for Pattern matching

14. Adding Table Records Click on New Record indicator on either the navigation bar, or toolbar Current record selector moves to new record Record counter increments by one

15. Adding Table Records (continued) Record indicator changes to pencil icon when you begin entering data, indicating that record is in edit mode

16. Updating Data Deletion • Removal of obsolete data Modification • Correction of errors • Changes to original data values Both modification and deletion are critical to the integrity of the database

17. Deleting Data Deleting a single record • Highlight record selector • Press Del key or • Click Delete button on toolbar or • Select Delete Record from Edit menu or • Right-click and select Delete Record To delete several contiguous records at once, click and drag to select records, then delete using one of the methods above

18. Modifying Data • Navigation Mode (default setting) • Tab moves cursor from field to field, entire field is selected • Allows quick selection and replacement of entire cell contents • Edit mode • Tab moves cursor from field to field, cursor located at either beginning or end of field, depending on Keyboard options settings • Allows for editing character by character

19. Modifying Data Moving around in an Access table

20. The Undo Command in Access Datasheet before modification to Wagoner record Firstname and Lastname fields modified, Firstname field still active - Undo button undoes modifications field by field Firstname and Lastname fields modified, CstmrNmbr field active - Undo button undoes both modifications at once Firstname and Lastname fields of Wagoner record modified, different record selected - Undo button undoes both modifications at once, or Undo Saved Record from Edit menu restores work Firstname and Lastname fields of Wagoner record modified, editing occuring in different record Modifications to Wagoner record can’t be undone

21. Updating with the Microsoft Office Clipboard

22. Office Clipboard (continued) Click an item to paste or delete it Office Clipboard Contents

23. Using the Clipboard to copy records to a new table 1. Make a Copy of the Customer table Click Paste Select Copy Click OK Select Table CustomerBackup Table Created Type table name Choose Structure Only Option Continued…..

24. Using the Clipboard to copy records to a new table 2. Open the Customer Table 3. Display the Office Clipboard 4. Select records for new table and copy to the clipboard Click Copy – record appears on clipboard Select record Continued…..

25. Using the Clipboard to copy records to a new table 5. Open the CustomerBackup table 6. Select new record row, then select Paste option for first item on clipboard. 7. Repeat for other items on clipboard

26. Organizing A Datasheet Often, tables are more than one screen wide, necessitating scrolling through columns to find information. Tools to use? • Hiding and Unhiding Columns • removes columns from display, but not from table • used when there are columns in a table not relevant to the task at hand

27. Hiding/Unhiding Columns Select Hide Columns from Format menu Select State field selector State field is hidden from view Select Unhide Columns from Format menu to display State field again

28. Freezing/Unfreezing Columns Select the columns to be frozen, then select Freeze Columns from the Format menu The last 3 fields can now be seen in their entirety with LastName and FirstName fields All fields don’t fit in the window – you need to scroll to the right to see the remaining fields Solid line shows boundary of frozen columns

29. Designing and Building a Database • Assessing Information Needs • Outline the Mission • Establish Table Subjects • Establish Table Fields • Defining Relationships • Designing a Prototype

30. Establishing Table Subjects Original Invoice First Table Design Break Out Table Design, no calculated values

31. Establishing Table Fields Tips for Defining Fields • Each field must directly describe the subject of the table • Store data in their smallest logical part • Assign a primary key field to each table Table design with fields and primary keys assigned

32. 1 places  Customers Orders  Contains  Products Defining Relationships Preliminary entity diagrams for invoicing design • Relationships depicted: • One customer places many orders • One order contains many products • One product is contained on many orders

33. 1 places  Customers Orders 1 Contains  OrderDetails  Contains 1 Products Defining Relationships Invoicing design with two one-to-many relationships replacing a many-to-many relationship • Relationships depicted: • One Customer places many Orders • One Order contains many OrderDetails (lines) • One product is contained on many OrderDetails

34. Designing a Prototype • Build tables, specify relationships and enter some test data • Some Questions to ask: • Is any data missing? • Is data repeated? • Are the primary keys working correctly? • Can tables be joined effectively?

35. Building Access Tables Using Design View Design View allows control over all table attributes Attributes: • Field Names • Up to 64 characters • Can include any combination of letters, numbers, spaces and special characters except . ! ` [ ] • Cannot start with a space • Cannot contain control characters Field Name

36. Table Attributes • Data Types • Establishes what data values a field can store and what other properties can be set for a field Data Type

37. Table Attributes • Field Sizes • Available for Text, Number and AutoNumber data types Field Size

38. Building a Table Definition Select Tables, then New Select Design View, then OK

39. Building a Table Definition – Design View

40. General Tab Attributes General Tab for text data type General Tab for Number Data type General tab for Currency data type

41. Using Undo and Redo when Defining Tables • The last 20 actions can be undone in Design View using the Undo command • Redo can reinstate up to 20 of the most recently undone actions • How does this differ from datasheet view?

42. Working with New Tables • Navigating between Views • Done through the use of the View button • Populating New Tables • Done through data entry in datasheet view, use of queries and forms, use of the clipboard, and through importing data from another application View Button

43. Access Limitations Database limitations • 2 gigabytes in size • Maximum of 32,768 objects • 64 character maximum in an object name • 14 character maximum for passwords • Support up to 255 concurrent users

44. Access Limitations Table limitations • have up to 64 characters in each field name • have up to 255 fields • be one of 2048 concurrent open tables • be up to 1 gigabyte • have 32 indexes • be sorted by up to 255 characters in one or more fields