Microsoft ® Access ® 2010 Training

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 • Create a table in Datasheet view. • Set data types for the fields in the table. • Create a table in Design view, and set the primary key and data types for the table. • Create a lookup field — a field that provides a list of choices. • Use Design view to change the values in an existing lookup field. Create tables for a new database

Create tables 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. You need to build an asset tracking database and move away from a spreadsheet that’s too big to use. Create tables for a new database

Create tables 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. 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. Create tables for a new database

Create tables Ways to 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. Create tables for a new database

Create tables Finally, as you go, remember that if you want to publish your database to SharePoint, you have to use Datasheet view to create your tables. Ways to 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. Create tables for a new database

Create a table in Datasheet view The process, in Datasheet view. 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. Create tables for a new database

Create a table in Datasheet view The process, in Datasheet view. 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... Create tables for a new database

Create a table in Datasheet view The process, in Datasheet view. 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. Create tables for a new database

Create a table in Datasheet view The process, in Datasheet view. 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. Create tables for a new database

Go faster with Quick Start fields Adding Quick Start fields to a table. 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. Create tables for a new database

Go faster with Quick Start fields Adding Quick Start fields to a table. 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. Create tables for a new database

Go faster with Quick Start fields Adding Quick Start fields to a table. 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. 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 tables for a new database

Create a table in Design view Using Design view. 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. Create tables for a new database

Create a table in Design view Using 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. Create tables for a new database

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

Add and save data The process of saving 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. Create tables for a new database

Add and save data The process of saving 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. Create tables for a new database

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

Use the record navigation buttons Access 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. Create tables for a new database

Use the record navigation buttons Access 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. Use the Next record button to move to the next record. Create tables for a new database

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

Add a lookup field to a table Using the Lookup Wizard. 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. Create tables for a new database

Add a lookup field to a table Using the Lookup Wizard. 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. Create tables for a new database

Add a lookup field to a table Using the Lookup Wizard. 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. Create tables for a new database

Add a lookup field to a table Using the Lookup Wizard. 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. Create tables for a new database

Suggestions for practice • Create a Suppliers table in Datasheet view. • Create a Support table in Design view. • Create an Assets table. • Create lookup fields in Datasheet view. • 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.) Create tables for a new database True. False.

Test question 1 When you create a new table in Datasheet view, you must define a primary key field. Answer: False. Create tables for a new database 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.

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

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

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.) Create tables for a new database True. False.

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

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

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

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

Microsoft ® Access ® 2010 Training