MSOffice Access Microsoft Office 2010: Illustrated Introductory 1 Part 1 Database & Table Objectives Learn basic database concepts and terms Explore the Microsoft Access window and Backstage view Create a blank database Create and save a table Enter field names and records in a table Open a table using the Navigation Pane Microsoft Office 2010: Illustrated Introductory 2 Objectives Open an Access database Navigate a table datasheet Table Relationships Microsoft Office 2010: Illustrated Introductory 3 Databases and Relationships A collection of related tables is called a database, or a relational database You connect the records in the separate tables through a common field A primary key is a field, or a collection of fields, whose values uniquely identify each record in a table When you include the primary key from one table as a field in a second table to form a relationship between the two tables, it is called a foreign key in the second table 4 Microsoft Office 2010: Illustrated Introductory Organizing Data Your first step in organizing data is to identify the individual fields The specific value, or content, of a field is called the field value A set of field values is called a record Next, you group related fields together into tables 5 Microsoft Office 2010: Illustrated Introductory Databases and Relationships 6 Microsoft Office 2010: Illustrated Introductory Relational Database Management Systems A database management system (DBMS) is a software program that lets you create databases and then manipulate data in them In a relational database management system, data is organized as a collection of tables 7 Microsoft Office 2010: Illustrated Introductory The Access Window Microsoft Office 2010: Illustrated Introductory 8 Creating a Database Microsoft Office 2010: Illustrated Introductory 9 Creating a Database Microsoft Office 2010: Illustrated Introductory 10 Creating a Table Click the Create tab on the Ribbon In the Tables group, click the Table button. Accept the default ID primary key field with the AutoNumber data type, or rename the field and change its data type, if necessary. Click the Design button (directly under the File Tab) to change to the design table view In the Fields Type cell, click the button for the type of field you want to add to the table (for example, click the Text button), and then type the field name. Repeat this step to add all the necessary fields to the table 11 Microsoft Office 2010: Illustrated Introductory Creating a Table In the first row below the field names, enter the value for each field in the first record, pressing the tab or Enter key to move to the next field After entering the value for the last field in the first record, press the Tab or Enter key to move to the next row, and then enter the values for the next record. Continue this process until you have entered all the records for the table Click the Save button on the Quick Access Toolbar, enter a name for the table, and then click the OK button 12 Microsoft Office 2010: Illustrated Introductory Defining a Field in Design View In the Field Name box, type the name for the field, and then press the Tab key Accept the default Text data type, or click the arrow and select a different data type for the field. Press the Tab key Enter an optional description for the field, if necessary Use the Field Properties pane to type or select other field properties, as appropriate 13 Microsoft Office 2010: Illustrated Introductory Defining a Field in Design View 14 Microsoft Office 2010: Illustrated Introductory 15 Defining a Field in Design View Microsoft Office 2010: Illustrated Introductory Data types in a Table 16 Microsoft Office 2010: Illustrated Introductory Moving a Field To move a field, you use the mouse to drag it to a new location in the Table window in the Table Design grid 17 Microsoft Office 2010: Illustrated Introductory Adding a Field Between Two Existing Fields In the Table window in Design view, select the row below where you want the new field to be inserted In the Tools group on the Design tab, click the Insert Rows button Define the new field by entering the field name, data type, optional description, and any property specifications 18 Microsoft Office 2010: Illustrated Introductory Adding a Field Between Two Existing Fields 19 Microsoft Office 2010: Illustrated Introductory Deleting a Field from a Table Structure In Datasheet view, click the column heading for the field you want to delete In the Add & Delete group on the Fields tab, click the Delete button or In Design view, click the Field Name box for the field you want to delete In the Tools group on the Design tab, click the Delete Rows button 20 Microsoft Office 2010: Illustrated Introductory Deleting a Field from a Table Structure 21 Microsoft Office 2010: Illustrated Introductory Creating Primary Key The primary key field of a table has an important purposes. it contains data that uniquely identifies each record. No two records can have the exact same entry in the field designated as the primary key field. 22 Microsoft Office 2010: Illustrated Introductory Creating Primary Key 23 Microsoft Office 2010: Illustrated Introductory Creating Primary Key 24 Microsoft Office 2010: Illustrated Introductory Entering Records & Saving a Table Click the Datasheet View button (under the file tab) to switch back to the Datasheet View. Add, edit, or delete records as desired (all changes within a table are automatically saved) Click the Save button on the Quick Access Toolbar. The Save As dialog box opens In the Table Name text box, type the name for the table Click the OK button 25 Microsoft Office 2010: Illustrated Introductory Entering Records 26 Microsoft Office 2010: Illustrated Introductory Opening a Database Start Access. If necessary, click the File tab to display Backstage view Click the Open command in the navigation bar to display the Open dialog box Navigate to the database file you want to open, and then click the file Click the Open button 27 Microsoft Office 2010: Illustrated Introductory Opening a Database 28 Microsoft Office 2010: Illustrated Introductory Navigating a Datasheet The navigation buttons provide another way to move vertically through the records 29 Microsoft Office 2010: Illustrated Introductory Viewing Objects in the Navigation Pane 30 Microsoft Office 2010: Illustrated Introductory Viewing Objects in the Navigation Pane 31 Microsoft Office 2010: Illustrated Introductory Table Relationship 32 Microsoft Office 2010: Illustrated Introductory Defining Table Relationships One of the most powerful features of a relational database management system is its ability to define relationships between tables You use a common field to relate one table to another 33 Microsoft Office 2010: Illustrated Introductory Defining Table Relationships 34 Microsoft Office 2010: Illustrated Introductory Defining Table Relationships A one-to-many relationship exists between two tables when one record in the first table matches zero, one, or many records in the second table, and when one record in the second table matches at most one record in the first table The Primary table is the one in a one-to- many relationship The Related table is the many table 35 Microsoft Office 2010: Illustrated Introductory Defining Table Relationships Referential integrity is a set of rules that Access enforces to maintain consistency between related tables when you update data in a database The Relationships window illustrates the relationships among a databases tables Click the Database Tools tab on the Ribbon In the Relationships group on the Database Tools tab, click the Relationships button 36 Microsoft Office 2010: Illustrated Introductory Defining Table Relationships Microsoft Office 2010: Illustrated Introductory 37 Defining Table Relationships Microsoft Office 2010: Illustrated Introductory 38 Defining Table Relationships Microsoft Office 2010: Illustrated Introductory 39 Defining Table Relationships Microsoft Office 2010: Illustrated Introductory 40