Access 2010 Level 1 Unit 1 Creating Tables and Queries

2. Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 4 Creating and Modifying Tables in Design View

3. Creating and Modifying Tables in Design View Quick Links to Presentation Contents • Create a Table in Design View • CHECKPOINT 1 • Insert a Total Row • Sort Records • Print Specific Records • Format Text • Complete a Spell Check • Find and Replace Data • Use Help • CHECKPOINT 2

4. Create a Table in Design View To create a table in Design view: • Open the desired database. • Click the Create tab. • Click the Table button in the Tables group. • Click the View button in the Views group. • Type a name for the table. • Click OK. continues on next slide… View button

5. Create a Table in Design View…continued • Type the field names, specify the data types, and include descriptions. • Click the Save button in the Quick Access toolbar. Design view

6. Create a Table in Design View…continued continues on next slide…

7. Create a Table in Design View…continued

8. Create a Table in Design View…continued To create a default value for a field in Design view: • Click in the Default Value property box. • Type the desired field value. Default Value property box

9. Create a Table in Design View…continued • For some fields, you may want to control the data entered in the field. • For example, in a ZIP code field, you may want the nine-digit ZIP code entered (rather than the five-digit ZIP code); or you may want the three-digit area code included in a telephone number. • Use the Input Mask field property to set a pattern for how data is entered in a field. • An input mask ensures that data in records conforms to a standard format.

10. Create a Table in Design View…continued To use the Input Mask Wizard: • Open the table in Design view. • Type the desired text in the Field Name column. • Press the Tab key. • Click the Save button. • Click in the Input Mask property box. • Click the Build button. continues on next slide… Build button

11. Create a Table in Design View…continued • In the Input Mask list box, choose which input mask you want your data to look like. • Click the Next button. continues on next slide… Input Mask list box

12. Create a Table in Design View…continued • Specify the appearance of the input mask and the desired placeholder character. • Click the Next button. continues on next slide… placeholder character

13. Create a Table in Design View…continued • Specify whether you want the data stored with or without the symbol in the mask. • Click the Next button. • Click the Finish button. Specify whether you want the data stored with or without the symbol in the mask.

14. Create a Table in Design View…continued • Use the Validation Rule property box in the Field Properties section in Design view to enter a statement containing a conditional test that is checked each time data is entered into a field. • Enter in the Validation Text property box the content of the error message that you want to display. Validation Rule property box Validation Text property box

15. Create a Table in Design View…continued • Like the Input Mask Wizard, you can use the Lookup Wizard to control the data entered in a field. • Use the Lookup Wizard to confine the data entered into a field to a specific list of items.

16. Create a Table in Design View…continued To use the Lookup Wizard: • Open the desired table in Design view. • Type the desired text in the Field Name column. • Press the Tab key. • Click the down-pointing arrow. • Click the Lookup Wizard option. continues on the next slide… Lookup Wizard option

17. Create a Table in Design View…continued • Click the I will type in the values that I want option. • Click the Next button. continues on the next slide… I will type in the values that I want option

18. Create a Table in Design View…continued • Click in the blank text box below Col1 and then type the first choice. • Press the Tab key and then type the second choice. • Continue in this manner until all desired choices are entered. • Click the Next button. • Click the Finish button. Col1

19. Create a Table in Design View…continued To insert a field in Design view: • Open the desired table in Design view. • Click in the row that will follow the new field. • Click the Insert Rows button in the Tools group in the Table Tools Design tab. Insert Rows button

20. Create a Table in Design View…continued To move a field in Design view: • Open the desired table in Design view. • Click in the field selector bar at the left side of the row that you want to move. • Point to the field selector bar of the selected row. • Hold down the left mouse button and drag the arrow pointer to the desired position. • Release the mouse button. field selector bar

21. Create a Table in Design View…continued To delete a field in Design view: • Open the desired table in Design view. • Click in the row to be deleted. • Click the Delete Rows button in the Tools group in the Table Tools Design tab. • Click Yes. Delete Rows button

22. CHECKPOINT 1 • In Design view, each one of these in the top section represents one field in the table. • column • row • section • group • Enter the content of the error message that you want to display in this property box. • Validation Text • Validation Rule • Input Mask • Default Value Answer Answer Next Question Next Question • Use this field property to set a pattern for how data is entered in a field. • Validation Text • Validation Rule • Input Mask • Default Value • Use this feature to confine the data entered into a field to a specific list of items. • Lookup Wizard • Input Mask Wizard • Field Wizard • Confine Wizard Answer Answer Next Question Next Slide

23. Insert a Total Row To insert a total row: • Open the desired table in Datasheet view. • Click the Totals button in the Records group in the Home tab. • Click in the Total row. • Click the down-pointing arrow. • Click the desired function. Total row

24. Sort Records To sort records: • Open the desired table in Datasheet view. • Click in the field in the desired column. • Click the Ascending button or Descending button in the Sort & Filter group in the Home tab. Ascending button

25. Print Specific Records To print specific records in a table: • Open the desired table in Datasheet view. • Select the records. • Click the File tab. • Click the Print tab. • Click the Print option. • At the Print dialog box, click the Selected Record(s) option in the Print Range section. • Click OK. Selected Record(s) option

26. Format Text • Formatting options are available in the Text Formatting group in the Home tab. Text Formatting group

27. Format Text…continued continues on next slide…

28. Format Text…continued

29. Format Text…continued To change to rich text formatting: • Click in the Text Format property box in the Field Properties section. • Click the down-pointing arrow that displays at the right side of the property box. • Click the Rich Text option at the drop-down list. Rich Text option

30. Complete a Spell Check • The spell checking feature in Access finds misspelled words and offers replacement words. • It also finds duplicate words and irregular capitalizations.

31. Complete a Spell Check…continued To complete a spelling check: • Open the desired table in Datasheet view. • Click the Spelling button in the Records group in the Home tab. • At the Spelling dialog box, change or ignore the spelling as needed. • Click OK. Spelling dialog box

32. Find and Replace Data To find data: • Open the desired table in Datasheet view. • Click the Find button in the Find group in the Home tab. continues on next slide… Find button

33. Find and Replace Data…continued • Type the desired data in the Find What text box. • Click the Find Next button. Find What text box

34. Find and Replace Data…continued To find and replace data: • Open the desired table in Datasheet view. • Click the Replace button in the Find group in the Home tab. • Type the desired data in the Find What text box. • Type the desired data in the Replace With text box. • Click the Find Next button. • Click the Replace button or the Find Next button. Replace With text box

35. Use Help To use the Help feature: • Click the Microsoft Access Help button. continues on next slide… Microsoft Access Help button

36. Use Help…continued • At the Access Help window, type a topic or feature. • Press Enter. • Click the desired topic. Access Help window

37. Use Help…continued To display the Help tab Backstage view: • Click the File tab. • Click the Help button. Help tab Backstage view

38. Use Help…continued • When you position the mouse pointer on a button, a ScreenTip displays with information about the button. • Some button ScreenTips display with the message “Press F1 for more help” that is preceded by an image of the Help button.

39. Use Help…continued • Some dialog boxes, as well as the Backstage view, contain a Help button you can click to display a help window with specific information about the dialog box or Backstage view. Help button

40. CHECKPOINT 2 • To insert a Totals row, click the Totals button in this tab. • Create • External Data • Database Tools • Home • Formatting options are available in the Text Formatting group in this tab. • Create • External Data • Database Tools • Home Answer Answer Next Question Next Question • Click this button to sort data in the active field in alphabetic order from A to Z or numbers from lowest to highest. • Ascending • Descending • Chronological • Historical • Press this keyboard shortcut to display the Access Help window . • F1 • F2 • F3 • F4 Answer Answer Next Question Next Slide

41. Creating and Modifying Tables in Design View Summary of Presentation Concepts • Create a table in Design view • Assign a default value • Use the Input Mask Wizard and the Lookup Wizard • Validate field entries • Insert a total row • Sort records and print specific records in a table • Complete a spelling check • Find specific records in a table and find data and replace with other data • Apply text formatting • Use the Help feature