Access 2007 ® Use Databases

1. Access 2007® Use Databases How can Microsoft Access 2007 help you structure your database?

2. Lesson 2: Structure a Database Access features such as templates, tables, and queries can help you to structure your database, making it easier to find and interpret data.

3. Lesson 2: Structure a Database View This Presentation to Answer the Following Questions: ________________________________________________________________ • How can I create a database? • How can I create a table? • How can Access help my database run more efficiently? • How is Access a relational database? • How can the Query Wizard help me create a query? • What vocabulary words should I review?

4. Lesson 2: Structure a Database To create a database, you can search both Access and online to find the appropriate template. Database templates include tables and fields that for organizing and tracking data, such as inventory, customer orders, personal contact information, and to-do lists.

5. Lesson 2: Structure a Database If you cannot find a template to meet your needs, you can create one from blank database. You can then design your own tables, forms, and reports.

6. Lesson 2: Structure a Database You can add new tables to any database. To create a table, select a template from the dozens Access provides for business and personal use.

7. Lesson 2: Structure a Database Use Design View to design your own tables. In a table, you can change a field’s property, such as the number of characters, or change a field data type, such as whether it represents a date/time. Every table must have a primary key. A primary key ensures that each record is unique.

8. Lesson 2: Structure a Database Access has several tools to help your database run more efficiently. The Table Analyzer Wizard can analyze the information in a table and then make changes to streamline the data. The Table Analyzer Wizard can ensure addresses are listed consistently and get rid of repeated listings.

9. Lesson 2: Structure a Database With a little patience, you can change anything in your tables. You can add a new field, change the order of the fields, change the field type, and change the field’s caption, or column heading.

10. Lesson 2: Structure a Database The Totals row function offers a quick and simple way to summarize columns of data in a table to get totals, averages, maximums, minimums, and other calculations.

11. Lesson 2: Structure a Database Academic Skills Check ________________________________________________________________ For what sort of everyday tasks can you use Access templates? Answer: You can use Access templates for everyday tasks such as maintaining personal contact information and creating to-do lists.

12. Lesson 2: Structure a Database Tech Check ______________________________________________________________ What Access tools can you use to design and analyze tables? Answer: You can use Design View to design tables and Table Analyzer Wizard to analyze tables.

13. Lesson 2: Structure a Database Tech Check ______________________________________________________________ What types of changes can you make to fields in a table? Answer: The types of changes you can make to fields in a table include changing the order of the fields, the field type, and the field’s caption, and adding a new field.

14. Lesson 2: Structure a Database Access allows you to set properties for entire tables of data and for individual fields. The field properties are shown as a list in a Property Sheet.

15. Lesson 2: Structure a Database Use multivalued fields to store more than one answer in a single field. A sporting goods store might categorize knee pads in both the skateboarding and baseball categories. You can create a Lookup field, which will contain all the possible data choices for the field.

16. Lesson 2: Structure a Database Use an attachment field to link to database files and other objects that might not fit into one field. An attachment field helps you present information in its original format, such as an image, Word document, or spreadsheet.

17. Lesson 2: Structure a Database Use an input maskto specify how information will be formatted. An input mask can specify that phone numbers be keyed with the area code first. An input masks keeps users from entering the wrong types of data into certain fields.

18. Lesson 2: Structure a Database Access is a relational database because it lets you define relationships as part of the database structure. A relationship is a link between two tables based on a common field.

19. Lesson 2: Structure a Database A one-to-one relationship means that both fields in their respective tables are not repeated. Table 2 Table 1

20. Lesson 2: Structure a Database A one-to-many relationship means that a record in one table can have many matching rows in another table.

21. Lesson 2: Structure a Database Referential integrity makes sure that the related data in two tables always exists and is complete.

22. Lesson 2: Structure a Database There are a few other ways to modify relationships to ensure that data is not lost, duplicated, or garbled. To modify the way information in tables is linked, use the Join option.

23. Lesson 2: Structure a Database Tech Check ______________________________________________________________ What is an attachment field useful for? Answer: An attachment field is useful for linking to a database files such as graphics and spreadsheets that might not fit into one field.

24. Lesson 2: Structure a Database Tech Check ______________________________________________________________ What does a relational database allow you to do? Answer: A relational database lets you define relationships as part of the database structure.

25. Lesson 2: Structure a Database The Query Wizard creates queries that show the fields you choose in the order you want. You can create a query by choosing fields from more than one table or from other queries.

26. Lesson 2: Structure a Database The Query Wizard includes several preset queries to help you analyze and manage your data. A crosstab query shows summary data (such as count, average, or sum) grouped by two fields. An unmatched query finds records without a matching related date. A duplicate query finds multiple records that contain the same data in a field.

27. Lesson 2: Structure a Database Action queries take action on the data in databases by adding, changing, or deleting data. A Make Table query retrieves information and assembles it into a new table.

28. Lesson 2: Structure a Database Other types of action queries include the Update Query, which updates information in a table, the Delete Query, used to delete records, and the Append Query, which inserts new records.

29. Lesson 2: Structure a Database Tech Check ______________________________________________________________ How can you create a query by choosing fields from more than one table ? Answer: Use the Query Wizard to create a query that shows the fields you choose in the order you want.

30. Lesson 2: Structure a Database Tech Check ______________________________________________________________ How does an action query take action on data in a database? Answer: Action queries take action on data in databases by adding, changing, or deleting data.

31. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ database template A sample database used by the Database Wizard to create a new database.

32. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ Design View The view that allows you to change the structure of a database object.

33. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ input mask Allows users to apply a specific type of format to data entered into a database.

34. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ referential integrity Prevents conflicting data between two tables by enforcing a relationship.

35. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ relationship A link between two tables based on a common field.

36. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ crosstab query A query that shows summary data (such as count, average, or sum) grouped by two fields–one across the top of the datasheet, the other in each row of the datasheet.

37. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ duplicate query A database query that finds records with the same data in a field.

38. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ unmatched query A query that finds records without matching related data.

39. Lesson 2: Structure a Database Vocabulary Review ______________________________________________________________ attachment field A database field that allows you to link to database files and other objects that might not fit into one field, such as an image, Word document, or spreadsheet.