Lesson 1: Exploring Access 2007

1. Lesson 1: Exploring Access 2007

2. Lesson Objectives After studying this lesson, you will be able to: Define what a database is Identify the types of objects modern databases contain Launch Access 2007 and identify elements of the application window Open a database and use the Navigation Pane Open database objects, navigate within database objects, and close the objects Close a database properly and exit Access 2007

3. Defining Access Databases What Is a Database? A collection of related data stored together in one electronic file

4. Flat Files Object-Oriented Files Database Structures Repetitive data

5. Launching Access 2007 Same basic procedures used to launch other computer programs

6. Exploring the Access Environment Create a new blank database Lists of templates Recently used database list

7. Opening a Database Same basic procedures used to open files in other computer programs Enabling Content to control security

8. Identifying Elements of the Access Window Ribbon tabs Shutter bar Open/Close button Navigation Pane Status bar

9. Object Types • Access 2007 databases contain four basic types of objects: • Tables – hold the data • Queries – search the table data • Forms – input the table data • Reports – report the table data as meaningful information • Each object type is represented by a different icon: • Tables – • Queries – • Forms – • Reports –

10. Using the Navigation Pane • Shutter button opens and closes the Pane • Organization appears in the Pane banner • Pane banner menu button ▼ displays the Navigation Pane menu • Choose a different organization from the Pane menu • Expand and collapse buttons to show/hide each object list

11. Opening, Navigating, and Closing Database Objects Each database object is listed on the Navigation Pane To open any object, double-click the object name Default setting for Access 2007 displays each object as a separate tabbed page in the Access window work area

12. Working with Tabbed Objects • To switch from one object to another, click the tabs

13. Navigating Datasheets • Tables and queries display in a row and column layout called a datasheet • Datasheet resembles an Excel spreadsheet • To move from column to column, press Tab or Enter or click the cell

14. Using Navigation Tools

15. Lesson 2: Using an Access Database

16. Lesson Objectives After studying this lesson, you will be able to: Add records to a database using both tables and forms Change the layout of a table by adjusting column width, hiding columns, and rearranging column layout Locate and update records by sorting, filtering, and using Find and Replace Preview and print database data Save a database as a new file

17. Adding Records Using a Table The primacy of tables Tables hold raw data All reports, queries, and forms use table data Two basic input tools for adding data to database tables: Typing data into the Datasheet view of the appropriate table Typing data into a form associated with the appropriate table

18. Fields, Records, and Files • Fields • Basic unit of data represented by a column in a table datasheet • Record • Group of related fields or all fields related to a specific item: person, place, or thing • Each row of a datasheet represent a record • File • Group of related records represented by a table in a database

19. Saving Records Access automatically saves each record as you complete it Edits made to records are also automatically saved

20. AutoNumber Fields Fields that automatically number records as you add them to a table It is unnecessary to type a number in these fields Numbers are un-editable The number is assigned to the specific record If you delete a record from a table, Access never assigns the number to any new record

21. Entering Data into a Table Datasheet The new row is identified with an asterisk Press (Tab) or (Enter) to move to the next column Click the New Record button

22. Changing the Width of Columns Drag a column border to make the column on the left of the border wider or narrower Double-click a column heading border to change the width of the column on the left to fit the longest data entry in the column Before After

23. Moving and Hiding Data Columns Hiding and moving fields in a datasheet has no impact on the actual structure of the table Data remains available but is just hidden from view when columns are hidden Moving columns enables you to print datasheets in different arrangements The E-mail Address column is empty The column is hidden

24. Saving a Table Layout Each time you make a change to the layout of a table you must save it The Save button appears on the Quick Access toolbar

25. Adding Records Using a Form Same basic procedures are used to add records using a form Data entered into a form is added to the associated table Press (Tab) or (Enter) in a form to move from field to field

26. Retrieving Data Sorting features Filtering tools Find and Replace

27. Sorting Records Sort Ascending Alphabetical order from A to Z Numeric order from lowest to highest Chronological order from first to last Sort Descending Reverse alphabetical order from Z to A Numeric order from highest to lowest Reverse chronological order from last to first

28. Sorting Records Using Multiple Fields Access considers second fields when values in the first field are equal Example: Personal Names When the last name is the same, you can tell Access to consider the first name Last name is the primary sort field First name is the secondary sort field

29. How Multiple Column Sorts Work Access sorts data on multiple fields from left to right Columns in a table must appear side by side in the datasheet The column on the left must be the one you want sorted first (primary sort field)

30. Using Find and Replace Techniques similar to those in other applications Use Find and Replace to locate records to remove or edit records

31. Filtering Records Filter by Selection Selects records on the basis of the value contained in the active field for the active record Filter by Form Selects records on the basis of values or conditions (criteria) that you type in one or more form fields Again, Access searches only the fields you specify to find the match.

32. Using the Toggle Filter Tool After you apply a filter, clicking the Toggle Filter button removes the filter and displays all records After removing a filter, clicking the Toggle Filter button re-applies the last filter applied A ToolTip displays to let you know what action you are performing: Remove Filter or Apply Filter.

33. Identifying Logical Operators Identifies the comparison Access should perform Operators include: = > < <> >= <=

34. Using Wildcards Used in place of specific characters Primary wildcards used: * Represents any number of characters ? Represents an individual character Examples: *Graham* locates all records with graham within the text Gra?am locates all records with gra at the beginning of the field value and am at the end of the field value with only one letter between

35. Using the Save As Command The Office  Save As command is used to save the active object rather than the file Display the Save As menu to save the entire database as a new file

36. Lesson 3: Building a Database

37. Lesson Objectives After studying this lesson, you will be able to: Create a new blank database Create a database table using Datasheet View Create a form Add records to a table Create a report Create a simple query Create a table from an Excel worksheet Create a new database using a template

38. Planning a Database What information do you want to be able to obtain? What data elements (fields) must you include to be able to obtain the information? What types of data will you enter into each field? Dates, numeric values, amounts of money, text, etc. What fields of data relate to the same basic items and could be grouped together? How do the groups of data relate to each other? What is the most efficient way to get data into the database tables? What questions will you need the database to be able to answer?

39. Creating a New Database Database – Shell that holds all data items Formats: Blank Template Sample Must save a new database before adding items to it Access creates a blank, unnamed table after you save the new database Generic Field# names appear as column headings in the default table Access creates

40. Guidelines for Creating Tables Each table should contain fields that focus on data specific to one type of item Such as customers, employees, inventory, etc. Each table should have a primary key that cannot be empty Each generic field# heading is replaced with the official field name

41. Creating Additional Database Objects Create tab of the Ribbon contains tools for creating all database objects Tools on the Ribbon are grouped by object type Multiple tools are available for each object

42. Creating Simple Forms—Input • Simple forms contain all table fields • Access arranges fields appropriately on the form and uses a default form design

43. Creating Simple Reports—Output • Processes data into meaningful information • Formats data for printing • Creates a report containing all fields from the associated table

44. Creating Queries Using a Wizard Steps you through the process of creating a query Enables you to select specific fields to display in a datasheet Enables you to add criteria for selecting specific records

45. Importing Data • Creates new tables from data stored in other programs • External Data tab on the Ribbon contains Import tools • Most import procedures use Wizards

46. Using Templates to Create Databases In Access, database templates are ready-to-use databases Each template contains a set of objects designed for a specific purpose Storing contacts, tracking projects, etc. Each object in the database created with a template can be customized to meet specific needs

47. Lesson 4: Managing a Database

48. Lesson Objectives After studying this lesson, you will be able to: Create tables using Table Design View Append records to a database table Create and run queries using Query Design View Create forms and reports using wizards Copy and save database objects as new objects Back up a database Compact and repair a database

49. Using Table Design View • Presents a blank palette on which to enter fields • Provides tools for setting data types, descriptions, and properties • Enables you to change the structure and organization of fields in the table

50. Switching Views • Button on the Home tab, Views group of the Ribbon • Button displays an icon that identifies the view you will display if you click the button • The button menu enables you to switch to different views