Work With Tables/ Database Records

1. Work With Tables/ Database Records Lesson 3

2. Skills Matrix

3. Skills Matrix

4. Skills Matrix

5. Skills Matrix

6. Skills Matrix

7. Navigate Among Records • Use the Keyboard • Use the Record Navigation buttons at the bottom of the screen in Datasheet view Navigating Among Records

8. Navigate Using the Keyboard Navigating Among Records

9. Navigate Using the Keyboard (cont.) Navigating Among Records

10. Navigate Using the Keyboard (cont.) Navigating Among Records

11. Use the Navigation Buttons • Click the First, Previous, Next, Last, and New (blank) Record buttons to go to those records • Key a record number into the Current Record box and press Enter to go to that record Search box Previous record Next record Filter indicator First record Current record Last record New (blank) record Navigate Among Records

12. Use the Navigation Buttons (cont.) • Key data into the Search box to find a match in the table • The Filter Indicator shows whether a filter has been applied to the table Previous record Next record Filter indicator Search box First record Current record Last record New (blank) record Navigate Among Records

13. Enter, Edit, and Delete Records Enter Records • Position the insertion point in the cell where you want to add data and begin keying • In Datasheet view, enter data field by field and row by row • After you enter data and move to a new field, Access automatically saves the data in the table

14. Enter, Edit, and Delete Records Enter Records (cont.) • Each field in a table is formatted with a specific data type. You must enter that kind of data in the field. • Sometimes fields may contain an input mask,which is a set of placeholder characters that force you to enter data in a specific format. For example, an input mask for data might look like this: #/##/####.

15. Enter, Edit, and Delete Records Enter Records (cont.) • To insert a new record: • Select any record in the table and click the New button on the Home tab in the Records group • Right-click a selected record and select New Record from the shortcut menu

16. Enter, Edit, and Delete Records Edit Records • Select existing data to edit it Record selector Pencil icon indicates that the record is being edited New record

17. Enter, Edit, and Delete Records Delete Records • Click the Select All button to select the entire record and then click the Delete button Delete button and menu Select All button

18. Enter, Edit, and Delete Records Delete Records (cont.) • A dialog box appears asking whether you are sure you want to delete a record • You cannot undo a deletion

19. Creating and Modifying Primary Keys Define and Modify a Primary Key To define a primary key, select the row and click the Primary Key button Key icon indicates that the field is the primary key • A primary key is a column that uniquely identifies each row

20. Creating and Modifying Primary Keys Define and Modify a Primary Key (cont.) • When you create a new database, Access creates a primary key field named “ID” by default and sets the data type for the field to AutoNumber • If you don’t have another field that would make a good primary key, you can use this field as the primary key

21. Creating and Modifying Primary Keys Define and Modify a Multi-field Primary Key • Two or more fields may be used together to provide the primary key of a table • Two or more primary keys in a table are called composite keys • In Design view, select the rows you want to designate as primary keys and click the Primary Key button

22. Finding and Replacing Data Find and Replace Data • Use the Find command to search for specific text in a table or to move quickly to a particular word or number in the table • Use the Replace command to automatically replace a word or number with another • Clicking either button launches the Find and Replace dialog box

23. Finding and Replacing Data Find and Replace Data (cont.) • Key the text or numbers that you want to search for into the Find What box and click Find Next to locate the record containing the data

24. Finding and Replacing Data Find and Replace Data (cont.) • If you want to replace the data, key the new data into the Replace With box and click Replace or Replace All

25. Finding and Replacing Data Find and Replace Data (cont.) • In the Match menu, you can specify where you want Access to look in a field. Select Any Part of Field for the broadest search. • Click the Match Case box to search for text with the same uppercase and/or lowercase capitalization of text

26. Finding and Replacing Data Find and Replace Data (cont.) • Use wildcard characters to find words or phrases that contain specific letters or combinations of letters • Key a question mark (?) to represent a single character—for example, keying b?t will find bat, bet, and but • Key an asterisk (*) to represent a string of characters—for example, m*t will find mat, moment, or even medium format

27. Attaching and Detaching Documents Attach and Detach Documents • Access 2007 allows you to attach documents, such as Word documents or photo files, to records in a database • You can only attach documents to a field that is formatted with the Attachment data type, which Access indicates with a paper clip icon Attachments field Number of attachments

28. Attaching and Detaching Documents Attach and Detach Documents (cont.) • Double-click the record in the Attachments field to display the Attachments dialog box where you can add, remove, open, or save multiple attachments for a single record

29. Attaching and Detaching Documents Attach and Detach Documents (cont.) • If the program that was used to create the attached file is installed on your computer, you can open and edit the file using that program • If you don’t have the program that was used to create the file, Access prompts you to choose a program you do have to view the file

30. Software Orientation Sort & Filter Group Filter button Ascending button Selection menu Descending button Advanced Filter button Clear All Sorts button Toggle Filter button

31. Sort and Filter Data Within a Table Sort Data Within a Table • To sort data means to arrange it alphabetically, numerically, or chronologically • Ascending order sorts data from beginning to end, such as from A to Z or 1 to 10 • Descending order sorts data from end to beginning, such as from Z to A or 10 to 1

32. Sort and Filter Data Within a Table Sort Data Within a Table (cont.) • To sort a column • Select it and click the Ascending or Descending button in the Sort & Filter group • Select it, right-click the column, and choose a Sort command from the shortcut menu. The available sort commands vary depending on the column’s data type.

33. Sort and Filter Data Within a Table Sort Data Within a Table (cont.)

34. Sort and Filter Data Within a Table Sort Data Within a Table (cont.) • You can sort records on multiple fields—simply decide on the order • The primary sort field is called the outermost field, such as Last Name. Sort this field second. • The secondary sort field is called an innermostfield, such as First Name. Sort this field first.

35. Sort and Filter Data Within a Table Sort Data Within a Table (cont.) • Sort commands remain with the table until you remove them with the Clear All Sorts button A sort arrow in the header row indicates that the field has been sorted

36. Sort and Filter Data Within a Table Filter Data • A filter is a set of rules for determining which records will be displayed Filter icon indicates that a filter has been applied

37. Sort and Filter Data Within a Table Filter Data: Custom Text Filter • Select the field, click the Filter button, and choose a filter from the menu • Key text into the Custom Filter box

38. Sort and Filter Data Within a Table Filter Data: Filter by Selection • Select a word in the field, right-click it to display the shortcut menu, and select a filter option from the shortcut menu

39. Sort and Filter Data Within a Table Remove a Filter from a Field • Select the field that contains the filter and click the Filter button. Select Clear Filter from [field name].

40. Sort and Filter Data Within a Table Remove all Filters • Click the Advanced button. Select Clear All Filters from the menu to permanently remove all filters.

41. Software Orientation Relationship Tools on the Ribbon Removes tables and relationships from the display; it does not delete Hides table from the display Launches the Edit Relationships dialog box Displays all relationships and tables in the database Displays all the relationships and related tables for the selected table Creates a report you can print Launches the Show Table dialog box

42. Understanding Table Relationships Define Table Relationships Select a table to display in the window

43. Understanding Table Relationships Define Table Relationships (cont.) Related tables Click Create to define the relationship Type of relationship

44. Understanding Table Relationships Define Table Relationships (cont.) Relationship line

45. Understanding Table Relationships Referential Integrity Enforced Relationship line is thicker Number 1 on the “one” side of the relationship Infinity symbol on the “many” side of the relationship

46. Understanding Table Relationships Print Relationship Report • Click the Relationship Report button to display the report. Click the Print button to print it.

47. You Learned How to • Navigate among records • Enter, edit, and delete records • Create and modify a primary key • Define and modify a primary key Summary

48. Summary Define and modify a multi-field primary key Find and replace data Attach and detach documents Sort data within a table You Learned How to (cont.)

49. Summary Filter data within a table Remove a filter Understand table relationships You Learned How to (cont.)

50. Summary Define table relationships Modify table relationships Print table relationships You Learned How to (cont.)