Microsoft Access 2010 Training Create tables for a

® ® Microsoft Access 2010 Training Create tables for a new database

Course contents • Overview: The essential component • Lesson: Includes seven instructional sections • Suggested practice tasks • Test • Quick Reference Card Create tables for a new database

Overview: The essential component Tables are the essential component of any database. Without them, you don’t have a database. In this course, you’ll learn how to build the tables for a new database. If you’re feeling intimidated, relax. We assume you’re a beginner, and we’ll show you how to create tables step by step. Create tables for a new database

Course goals 1. Create a table in Datasheet view. 2. Set data types for the fields in the table. 3. Create a table in Design view, and set the primary key and data types for the table. 4. Create a lookup field — a field that provides a list of choices. 5. Use Design view to change the values in an existing lookup field. Create tables for a new database

Create tables You need to build an asset tracking database and move away from a spreadsheet that’s too big to use. You may be familiar with how to design the tables for your new database – the fields, data types, primary keys, and foreign keys. Now it’s time to create the tables. Ways to create tables. Create tables for a new database

Create tables As a reminder, though, in a relational database, tables store your data. Your data doesn’t “live” anywhere else, and that makes tables the central component of your database. If you don’t have tables, you don’t have a database. This course shows you how to use the most common tools for building tables: Datasheet view and Design view. Ways to create tables. Create tables for a new database

Create tables Here’s the process: In Datasheet view, you build a table by clicking a blank field header, selecting a data type, and then entering a field name. All you have to do is click and type, and we’ll show you how. For some tables, you can save time by using Quick Start Fields, predefined sets of fields that meet several common business needs, such as capturing addresses or starting and ending dates. All you have to do is select a set of fields from a menu. Ways to create tables. Create tables for a new database

Create tables Here’s the process: In contrast to Datasheet view, Design view lets you control every field and property in a table. In this course, you’ll use it to create a table and to change the values in a lookup field — a field that contains a list of choices. Finally, as you go, remember that if you want to publish your database to Share. Point, you have to use Datasheet view to create your tables. Ways to create tables. Create tables for a new database

Create a table in Datasheet view provides a visual way to create a table. Start by creating a new, blank database or by adding a new table to an existing database. Either method opens a new table in Datasheet view. Notice that the new table contains a field called ID. That’s your primary key, so you don’t need to create one. The process, in Datasheet view. Create tables for a new database

Create a table in Datasheet view provides a visual way to create a table. To add your fields, click the first blank field header – the words Click to Add. That starts a menu of data types, and you select a data type for the field. After that, the field header then becomes available for writing, so. . . The process, in Datasheet view. Create tables for a new database

Create a table in Datasheet view provides a visual way to create a table. You just type the field name and press ENTER. Doing that shifts the focus to the next field, where you repeat the process. As you work, remember that if your field names contain more than one word, don’t use spaces between the words. The process, in Datasheet view. Create tables for a new database

Create a table in Datasheet view provides a visual way to create a table. When you’ve finished, press CTRL+S, or go to the Quick Access Toolbar and click Save. That starts a Save As dialog box, where you enter a name for the table and then save it. The process, in Datasheet view. Create tables for a new database

Go faster with Quick Start fields are a faster way to build parts of a new table. The fields capture data for common business needs, and all field names and data types are set for you. Adding Quick Start fields to a table. Create tables for a new database

Go faster with Quick Start fields With a table open in Datasheet view, click the Fields tab, and in the Add & Delete group, click More Fields. A list appears. Scroll down the list until you see the Quick Start section, click the type of fields you want to use, such as Address, or Name, and. . . Access adds the fields for you, with field names data types already set. Adding Quick Start fields to a table. Create tables for a new database

Go faster with Quick Start fields You can use the new fields right away — just start entering data — or you can rename them, and remove fields you don’t need. Also, you may have noticed what seem to be spaces in the field names. Adding Quick Start fields to a table. Create tables for a new database Don’t worry, you’re not looking at the actual field names. Instead, you’re looking at captions, user-friendly text associated with each field name.

Create a table in Design view allows you to build a table from scratch and set or change every available property for each field. You can also open existing tables in Design view and add, remove, or change fields. Using Design view. Create tables for a new database

Create a table in Design view On the Create tab, in the Tables group, click Table Design. In the Field Name column of the designer, enter the names of your table fields. As a rule, the first field you create should be your primary key field. And remember that you don’t need to add any foreign key fields now. You can do that when you create your relationships. Using Design view. Create tables for a new database

Create a table in Design view In the Data Type column, use the list next to a field name to choose a data type for that field. Optionally, use the Field Properties pane to set properties for individual fields. As always, save your changes and give your new table a name that describes the data it contains. Using Design view. Create tables for a new database

Add and save data As you finish your tables, you’ll probably enter a few records. That’s a good way to test your tables, and to help make sure you’re capturing the right data. Remember a couple of rules along the way. The process of saving data. Create tables for a new database

Add and save data When you enter or change data, you never have to click Save to commit the new information to your database. All you have to do is move the focus to another record. To do that in a datasheet, or in a type of form called a multiple-items form, you can click a different row. You can also use the TAB or arrow keys to shift the focus to a new record. Any of those actions will commit new data. The process of saving data. Create tables for a new database

Add and save data The same is true forms. You enter data on the form, and when you navigate to a different record, you commit your data. The process of saving data. Create tables for a new database

Use the record navigation buttons Once you create your tables, you’ll need to know how to use the record navigation buttons. You’ll find them in the lower-left corner of your tables, and you’ll also see them in your query results, and on most of your forms. Access record navigation buttons. Create tables for a new database

Use the record navigation buttons You use the buttons to locate data. Use the First record button to go to the first record in a table or query result. Use the Previous record button to go to the previous record. The Current Record box lists the records in sequential order, and it shows you which record you have selected. Access record navigation buttons. Create tables for a new database Use the Next record button to move to the next record.

Use the record navigation buttons You use the buttons to locate data. Use the Last record button to move to the last record, and. . . If you need to add data, click the New (blank) record button. Access record navigation buttons. Create tables for a new database

Add a lookup field to a table You can sometimes use a lookup field instead of a table. For example, say you need to record the locations of your company’s assets. If you have a large number of locations, such as offices on several floors, you’d store that data in a table because it’s easier to manage. But if you only have a few, it makes sense to store those options in a lookup field. Using the Lookup Wizard. Create tables for a new database

Add a lookup field to a table A lookup field can store a list of options internally, or it can look up data from a field in another table. The following steps explain how to create a lookup field that stores options internally, in what Access calls a value list. Using the Lookup Wizard. Create tables for a new database

Add a lookup field to a table With your table open in Datasheet view, click the Fields tab, and in the Add & Delete group, click More Fields. In the menu, click Lookup & Relationship. That starts the Lookup Wizard. On the first page of the wizard, click I will type in the values that I want and click Next. Using the Lookup Wizard. Create tables for a new database

Add a lookup field to a table On the next page of the wizard, make sure the Number of columns box contains a 1 and then enter your options in the grid, one option per row. On the third page of the wizard, enter a name for the new field and click Finish. Using the Lookup Wizard. Create tables for a new database

Suggestions for practice 1. Create a Suppliers table in Datasheet view. 2. Create a Support table in Design view. 3. Create an Assets table. 4. Create lookup fields in Datasheet view. 5. Create lookup fields in Design view. Online practice (requires Access 2010) Create tables for a new database

Test question 1 When you create a new table in Datasheet view, you must define a primary key field. (Pick one answer. ) 1. True. 2. False. Create tables for a new database

Test question 1 When you create a new table in Datasheet view, you must define a primary key field. Answer: 2. False. The “ID” field in the new table acts as the primary key. You can change the field name, or replace the field with another primary key, but a new datasheet always contains a primary key. Create tables for a new database

Test question 2 You can’t use the Lookup Wizard to alter an existing value list. (Pick one answer. ) 1. True. 2. False. Create tables for a new database

Test question 2 You can’t use the Lookup Wizard to alter an existing value list. Answer: 1. True. You use Design view to alter a value list. Create tables for a new database

Test question 3 When you use Quick Start fields to help create a table, you must set data types for those fields. (Pick one answer. ) 1. True. 2. False. Create tables for a new database

Test question 3 When you use Quick Start fields to help create a table, you must set data types for those fields. Answer: 2. False. You can change the data types if you need to, but they’re set for you. Create tables for a new database

Test question 4 Which of the following is the correct syntax for a value list? (Pick one answer. ) 1. ’Option 1’, ’Option 2’, ’Option 3’ 2. “Option 1”; ”Option 2”; ”Option 3” 3. “Option 1”: ”Option 2”: ”Option 3” Create tables for a new database

Test question 4 Which of the following is the correct syntax for a value list? Answer: 2. “Option 1”; ”Option 2”; ”Option 3” Place each option between double quotes and separate each option with a semicolon. Create tables for a new database

Quick Reference Card For a summary of the tasks covered in this course, view the Quick Reference Card. Create tables for a new database