Microsoft ® Office Access 2003 Training

1. CGI presents: Microsoft® Office Access 2003 Training Get to know Access

2. Course contents • Overview: Database basics • Lesson 1: Benefits of using a database • Lesson 2: Table that data • Lesson 3: Analyzing, viewing, and reporting data Each lesson includes a list of suggested tasks and a set of test questions. Get to know Access

3. Overview: Database basics • Do you want to manage a large collection of information? A database helps you enter data easily, find it quickly, use it to create labels or mailings, and summarize it in printed and online reports. This course will cover the benefits and structure of an Access database and get you familiar with ways to get data into one and out of one (such as using queries and reports). Get to know Access

4. Course goals • Learn the difference between a database and a list. • Get familiar with tables, fields, records, and primary keys. • Find out how to use the four main items, called objects, that most databases contain. Get to know Access

5. Lesson 1 Benefits of using a database

6. Benefits of using a database • A database is much more than just a list or table. A database: • Gives you true command of your data, enabling you to retrieve it, sort it, analyze it, summarize it, and report results in moments. Access unlocks the full value of your data. Get to know Access

7. Benefits of using a database • A database is much more than just a list or table. A database: • Can combine data from various files, so that you never have to enter information twice. • Can even make data entry more efficient and accurate. Access unlocks the full value of your data. Get to know Access

8. Better than a collection of lists • Scenario: You're the secretary of a large hiking club with a lot of lists to manage. One club member, who appears on a number of lists, changes her e-mail for the second time this year. A key Access benefit: avoiding the complications of multiple lists Get to know Access

9. Better than a collection of lists • With only a set of lists, you'd have to change that information everywhere it occurs. But with a well-structured database, you'd have to change it only once. The database takes care of everything else. A key Access benefit: avoiding the complications of multiple lists Get to know Access

10. Making friends with relations Access creates relational databases. Data is stored in separate tables by subject or task, but the data is related. In other words, information in one set of data is associated with the applicable information in the other set of data. Relationships link data from individual tables to increase its usefulness. Get to know Access

11. Making friends with relations Database planning tips: • To make the most of your database, set up the tables of data to reflect the subjects and tasks associated with your data. • Consider the scenarios in which people will be entering data, looking up data, or reporting data. A little forethought can go a long way. Relationships link data from individual tables to increase its usefulness. Get to know Access

12. How a database is structured • Access databases consist of objects, such as the following four important ones: • Tables store data in rows and columns. • Queries retrieve and process data. Objects: the most important part of a database Get to know Access

13. How a database is structured • Access databases consist of objects, such as the following four important ones: • Forms control data entry and data views. • Reports summarize and print your data. Objects: the most important part of a database Get to know Access

14. Suggestions for practice • Remember a database. Think about recent situations in which you’ve seen a database in use. Jot down your thoughts. • Note database uses. How did people use the database: to look up your customer information? Scan price tags into the register or computer? And so on. • Imagine database activities. If you’re planning to create a database, write down two or more situations in which you’re likely to use the data. Get to know Access

15. Test 1, question 1 • Which of the following is not a database object? (Pick one answer.) • Table. • Report. • Query. • Worksheet. Get to know Access

16. Test 1, question 1: Answer • Worksheet. Although you can import worksheets from Microsoft® Excel® into your database, worksheets are not database objects. Get to know Access

17. Test 1, question 2 • Which of the following describes a relational database? (Pick one answer.) • It provides a relationship between integers. • It consists of separate tables of related data. • It retrieves data related to its queries. Get to know Access

18. Test 1, question 2: Answer • It consists of separate tables of related data. The separate tables are associated with each other through relationships. Get to know Access

19. Test 1, question 3 • What are the main building blocks of a database? (Pick one answer.) • Lists. • Queries. • Tables. Get to know Access

20. Test 1, question 3: Answer • Tables. Every Access database contains one or more tables that store your data. Get to know Access

21. Lesson 2 Table that data

22. Table that data • All databases in Access contain at least one table. In this lesson, we'll show what a table is made of, and how you can structure tables to fit your data. Tables organize your data. Get to know Access

23. Tables, the building blocks of databases • Tables store data, so they’re essential building blocks of any database. A database should have a separate table for every major subject, such as employee records or customer orders. Data should not be duplicated in multiple tables. Separate tables make up a database. Each table contains rows called records and columns called fields. Get to know Access

24. Tables, the building blocks of databases • A record is a collection of facts about a particular person, event, CD, or other item of interest. Each row constitutes a record. For example, Nancy Davolio and her employment details are a record in an Employees table. Speedy Express and its contact information are a record in a Shippers table. Get to know Access

25. Tables, the building blocks of databases • A field is a single kind of fact that may apply to each person, event, or other record. The Phone and other columns are fields. For example, Postal Code is a field in an Employees table. Phone is a field in a Shippers table. Get to know Access

26. Fields of data • The fields in your database have settings that determine the type of data they can store, how the data is displayed, and what you can do with the data. Settings include the field name, data type, primary key, and field properties. Get to know Access

27. Fields of data • Field name. If an existing field name isn’t descriptive enough, you can rename the field. • Data type. A field’s data type limits and describes the kind of information you can enter in a field. It also determines the actions you can perform on a field and how much memory the data uses. Settings include the field name, data type, primary key, and field properties. Get to know Access

28. Fields of data • Primary key. The primary key is a unique identifier for each record in your table. • Field properties. These are a set of characteristics that provide additional control over the details inside the field and that make it easier to enter and manage data. Settings include the field name, data type, primary key, and field properties. Get to know Access

29. Each record is unique • You may have heard that no two snowflakes are alike. This characteristic also applies to records in a well structured database. Employee details should form one unique record. Each record in each table should be unique—in other words, you wouldn’t have two identical records about Nancy Davolio in the same database. Get to know Access

30. Each record is unique • But what if you have two employees named Nancy Davolio? To distinguish one record from another, tables can contain a primary key field. The primary key is an identifier that’s unique to each record. Employee details should form one unique record. Get to know Access

31. Each record is unique • Access can assign a numeric primary key that increases by 1 each time you add a record to a table. This number continues to be associated with this record, even if you add and delete other records entered before this record in your database. Employee details should form one unique record. Get to know Access

32. It’s all relative Primary keys allow you to tap into the power of a relational database, instead of working with a fistful of repetitive lists that are hard to maintain and cannot cooperate. Primary and foreign keys Get to know Access

33. It’s all relative A primary key separates similar information and makes each record unique. It also brings information together. You relate one table to another using a primary key. This is how tables share data, and how you can avoid repeating information in both the tables. Primary and foreign keys Get to know Access

34. It’s all relative When tables relate, the primary key of one table becomes a foreign key of the other table. For example, in this picture: • Employee ID appears in the Employees table as a primary key… • …and in the Orders table as a foreign key. Primary and foreign keys Get to know Access

35. Suggestions for practice • Open a table. • Look inside a table. Online practice (requires Access 2003) Get to know Access

36. Test 2, question 1 • Data should be organized into tables based on: (Pick one answer.) • Field properties. • Subjects associated with the data. • Character lengths. Get to know Access

37. Test 2, question 1: Answer • Subjects associated with the data. A well structured database has a table for each subject to which data belongs, such as Employees, Students, or Products. Get to know Access

38. Test 2, question 2 • A row in a table is also known as: (Pick one answer.) • A field. • A record. • A data type. Get to know Access

39. Test 2, question 2: Answer • A record. A row is one record in a table; it contains a collection of data about something, such as a product or employee. A record includes data from several fields that might have names like Unit Price or Employee ID. Get to know Access

40. Test 2, question 3 • A primary key is: (Pick one answer.) • A unique identifier that can be used to relate tables. • The key that precedes the secondary key. • The password that validates a database. Get to know Access

41. Test 2, question 3: Answer • A unique identifier that can be used to relate tables. A primary key distinguishes one record from another and links data in one table to data in other tables. Get to know Access

42. Lesson 3 Analyzing, viewing, and reporting data

43. Analyzing, viewing, and reporting data • Tables are great for storing data. But to get the full benefit of Access, you need to understand other Access database objects. In this lesson, we'll tell you more about queries, forms, and reports, and how they can help you. A database window opens to show the list of database objects. Get to know Access

44. Queries • Do you have questions that you want to answer with your data? Queries can answer those questions by assembling stored data from your database, or by performing calculations with the data to provide further information. Results of a query Get to know Access

45. Queries To answer questions, queries retrieve, filter, sort, and assemble data on command. Another important power of queries is to combine the data from several tables into a single view. Results of a query Get to know Access

46. Queries • The picture illustrates the results of a query. This query extracted the names of employees who live in the United Kingdom from a larger Employees table. Results of a query Get to know Access

47. Queries • When a query finds data and shows it to you, it can also process that data according to your instructions and perform calculations using the data. Results of a query A query can also remove data. Note: Use caution when executing queries that change data, and consider backing up your data first. Get to know Access

48. Forms • Forms make data friendlier by enabling people to enter or view data in your database easily. Forms: • Control and simplify data input. • Make data from a table or a query easier to understand by presenting it in visually appealing designs. Elements of a form Get to know Access

49. Forms • Forms make data friendlier by enabling people to enter or view data in your database easily. Forms: • Provide drop-down lists, instructions, navigational controls, and graphics to help users work with your data. Elements of a form Get to know Access

50. Forms • Common elements of forms: • Graphic elements, such as lines and rectangles, are stored in the form's design. • Data comes from the fields in the underlying table or query. Elements of a form Get to know Access