Access 2007

1. Access 2007 Review

2. Topics • Advantages of Access • Database Objects • Tables • Queries • Forms • Reports

3. Advantages of Access • Minimizes duplicate data, increasing accuracy and consistency • Data entry is faster and easier • Provides tools that allow sorting, grouping, analyzing and reporting data in many different ways • Data is more secure • Data can be shared and edited by several users simultaneously

4. Access vs. Excel

5. Terminology to Know…

6. Editing Data • Access automatically saves new data and changes to existing data as soon as you move to another record OR close the datasheet • The edit record symbol (looks like a pencil) appears in the record selector box when you are editing a record

7. Database • Create a new database using an Access database template or open a blank database • Database contains objects such as tables, data, queries, forms and reports. • Navigation Pane: Allows you to move between objects • A table stores all of the data

8. Database Backups • Need to use the Backup command, because the Save As only saves a copy of the selected database object. • Click on Office Button, Select:Manage > Back Up Database > (select location and enter new name).

9. Creating a Table • Start by using an Access table template or by creating a table from scratch • 3 essential tasks in creating a table: * Naming the table * Naming the fields * Selecting data type for each field (e.g., numbers, text, dates)

10. Primary Key Fields • Primary key field: Contains data that uniquely identifies each record; no 2 records can have the same entry in this field • Primary key field relates one table to another in a one-to-many relationship; one record in the 1st table is related to many records in the 2nd table

11. Table in Design View

12. Field Properties • Properties = characteristics of a field • 2 properties required for every field: Field Name and Data Type • You can add other properties, such as Field Size, Format and Caption • More properties means more restrictions which then provides more accurate data

13. One-to-Many Relationships • The primary key field is in the table on the “one” side of the relationship • A foreign key field is a field in the “many” table • The one-to-many line shows the link between the primary key field of the table on the “one” side and the foreign key field of the table on the “many” side • The infinity symbol indicates the “many” side

14. One-to-Many Relationship

15. Query • A query allows you to “ask” for only the information you want vs. navigating through all the fields and records of large tables • You can enter, edit and navigate data in a query datasheet just like a table datasheet • Query is considered a logical view of the data because the structure of the database is not change.

16. Creating a Query • One way to create a query is by using the Simple Query Wizard • The Wizard asks you questions to determine the information you want • Another way to create a query is by using Query Design View • Query Design View presents the fields you can choose from in Field Lists • You also use Query Design View to edit an existing query

17. Query in Design View

18. Criteria Syntax • Quotation marks (“) around text criteria and pound signs (#) around date criteria are automatically added by Access • Criteria in Number, Currency, and Yes/No fields are not surrounded by any characters

19. Wildcards • Used to search for a pattern; represents any character • Entered as criteria • ? Used to search for a single character • * Used to search for any number of characters

20. Comparison Operators

21. Searching for Blank Fields • 2 common criteria are Is Null and Is Not Null • Is Null – Finds all records where no entry has been made • Is Not Null – Finds all records where any entry has been made (even if zero)

22. Applying AND Criteria • AND criteria means all criteria must be true for the record to be selected • Created by entering 2 or more criteria in the same Criteria row of the query design grid

23. Example of AND Criteria

24. Applying OR Criteria • OR criteria means any one criterion must be true for the record to be selected • Created by entering 2 or more criteria on different Criteria rows of the query design grid • Also created by entering 2 or more criteria in the same Criteria cell separated by OR

25. Applying OR Criteria

26. Sorting and Finding Data • Works the same way for queries as it does for tables • Data can be sorted by clicking the list arrow on a datasheet’s column heading, then click a sorting option • Data can also be sorted by using the Sort and Find buttons on the Home tab

27. Filtering Data • Filters provide a temporary way to display a subset of records that match given criteria • Filters are not used to calculate sums, averages, counts, etc. • Filters are removed when the datasheet is closed • Filters can, however, be saved as queries

28. Form • A form enables you to arrange fields of a record in any layout you wish • You can enter, edit, delete and navigate data in forms just like query and table datasheets

29. Creating a Form • One way to create a form is by using the Form Wizard • The Wizard asks you questions to determine the information you want • Form Layout View allows some design changes to the form • You can add / delete fields • You can change formatting such as fonts and colors • Form Design View provides access to the detailed layout and structure of the form for custom design

30. Form in Design View

31. Tab Stop and Tab Order • Focus: the current/active field on form • Tab Stop: determines whether the field accepts the focus and therefore determines where the focus moves as you press [Tab] • Tab Order: the order the focus moves as you press [Tab] • All text boxes and combo boxes have a tab stop and are included in the tab order

32. Report • The report feature helps you create a professional-looking, easy-to-read document that summarizes data • Reports are read-only objects • Report sections (header, detail, footer) determine where and when controls print in the report

33. Creating a Report • Report Wizard is one way to create a report • It is a tool that asks you questions to guide development of the report • Your responses determine record source, style and layout • The Wizard helps sort, group and analyze records • Report Design View is another way to create a report • It provides access to the detailed layout and structure of the form for custom design including the header, detail, footer sections

34. Report in Design View

35. Report Sections

36. Using Report Sections

37. Using Report Sections • A section determines where and how often controls in that section print in the report • Grouping means to sort records in a specific order plus provide a header or footer section after each group

38. Adding Subtotals and Counts • Calculations are used to add subtotals and counts to a report • Expressions are needed to create a calculation • Functions are built-in Access formulas • Arguments are information needed by a function

39. Formatting a Report • Formatting can make a report look more professional and easier to read • Formatting includes: * Font style, size and color * Background colors * Line thickness and color * Alignment

40. Changing Page Layout • Altering the layout can make a report fit better on the page, making it easier to read • Page layout includes: * Orientation (portrait or landscape) * Margins • Remember to use Print Preview

41. Page Orientation • Portrait: • Landscape:

42. To Learn More… • Consider taking CIS117DM – Access