Access Lesson 2: Getting Started with Microsoft Access 2013

In this lesson, you will become more familiar with creating tables and importing data into Microsoft Access 2013. You will create basic tables and learn about different field data types. You will also import data into Access from Microsoft Excel.

What you’ll learn in this lesson:

• • Creating a new blank database and a template
• • Creating a table and fields
• • Using Design view to edit a table table and create a primary key
• • Importing Microsoft Excel data into Access

Starting up

You will work with files from the Access02lessons folder. Make sure that you have loaded the OfficeLessons folder onto your hard drive from www.digitalclassroombooks.com/Office2013. If you need further instructions, see “Loading lesson files” in the Starting up section of this book.

Access 2013 database types

Access 2013 has two types of databases:

1 Desktop (standard) database, which is stored on your computer, a network, or another computer storage location and run directly from the location on your computer. The exercises in this lesson involve the desktop database type.

2 An Access web app, which was introduced with Access 2013. This new type of database can be built from Access, and then used and shared with others as a SharePoint app in a web browser. You create the app by selecting the type of data you want to track (contacts, tasks, projects, and so on), and Access makes the database structure, complete with views that let you add and edit data.

Creating a simple database

In Lesson 1, you explored a database. Now you will create one. Access 2013 has two methods for creating a database.

The first method is to use the templates provided with Access 2013. A template is a database created for a specific type of task that contains several pre-made database objects. When using a template to create a database, all you need to do is add your own data to the tables, and customize the template as needed.

When you use templates to create databases, you’re not limited to using the objects as they come with the templates; you can customize the objects and adapt them to fit your needs. For example, if you have a better design for a form, you need a different field name for a table, or you need a new table, you can customize these objects. You can also add your own database objects to the ones that already exist in the template.

The second option for creating a database is to create a new and empty database from scratch. Many people use this option when the database they need to create is too complicated and they cannot locate a suitable template.

It’s also very common for Access users to work with existing databases. You will probably enter information into the database, create new database objects, and edit existing data.

Creating a database using a template

1 Open Access if it is not already opened and open the Backstage view. Select the New command.

The New area displays several categories of templates. In the Search for Online Templates box, you can search for online templates from the Office.com site. There are also suggested search topics listed under the search box, such as Assets, Business Contacts, Inventory, Project, Sales, and more.

The next area includes different templates that appear as thumbnails. These templates are available for you to select directly from the New area of the Backstage view, and they are either Access apps or Desktop Access databases. Below is a list of these templates:

• • Custom web app: Access app made for you to customize from scratch
• • Blank desktop database: creates a new blank database
• • Desktop contacts
• • Asset Tracking
• • Contacts
• • Issue tracking
• • Project management:
• • Desktop asset tracking
• • Desktop issue Tracking
• • Desktop project management
• • Desktop task management

The Backstage view appears with the New tab selected.

2 From the second row, select the Desktop asset tracking template. The Desktop asset tracking information box opens.

Desktop asset tracking information dialog box.

3 In the File Name box, type the File Name NorthWind Traders Assets. Click browse, navigate to the Access02lessons folder, and click OK. Now click Create.

When the database opens, you may see a warning about disabled content. Click Enable Content. You may then see a message box labeled Getting Started with Assets. You can close it by clicking the Close button in the top-right corner.

Look at the Navigation bar. Notice the different objects that are automatically created because your database is based on a template.

4 In the Navigation pane, under the Tables Object category, double-click the table called Assets.

When the table opens, notice that there are no records in it. However, all the fields (columns) are set up and prepared for you to enter information into the table.

5 Click the File Ribbon tab and select Close. This closes the database, but leaves Access running.

You can have multiple databases open simultaneously, but closing unused databases frees up your computer’s resources and speeds up processes in the database that you are working with.

Creating a database from scratch:

1 Click File to enter the Backstage view, and then select New.

2 From the first row, select Blank desktop database. The Blank desktop database box opens.

3 In the File Name text box, type SCRATCH, browse to the Access02lessons folder, and click OK. Click Create and notice that only one object is created for this database: Table1.

The SCRATCH database opens with the Table1 object open and ready for data entry. It is the only object that is created when you make a new blank database.

Saving a new table

Now that your new database is open, and you have one new table created, you need to give your table a name.

1 In the Quick Access Toolbar, click the Save icon () or choose File > Save. The Save As dialog box appears.

2 In the Table Name text box, type Assets. Press Enter or click OK.

Tables, views, and data types

Tables are crucial to Access databases and form the framework of relational databases. Next, you will investigate various ways to view tables, and how to prepare them for the data they will hold.

You can view tables in Access 2013 in one of two ways: Datasheet view and Design view.

• • Datasheet view is designed for data entry and viewing records. You can add, delete, edit, sort, filter, and print records from this view. You can also insert, delete, categorize new fields, and change their order.
• • Design view is where the table’s design is the focus. It lists all the fields with their data types and descriptions. In this view, you do not see the actual records.

Creating a table in Datasheet view

Datasheet view allows you to add tables and fields to your database. It resembles a spreadsheet, such as you would find in Excel.

1 Using the SCRATCH database that you created in the previous section, click the Create tab.

2 From the Tables group (second group of commands from the left), select the Table command. Table1 appears in Datasheet view.

3 To rename Table1, click the Save button in the Quick Access Toolbar. When the Save As dialog box appears, type Products in the text box, and then press Enter.

In Datasheet view, you can examine the table more closely and notice the following:

• • The words Datasheet View are displayed in the lower-left corner of the Status Bar. (The status bar is located at the bottom of the Access window.)
• • The Datasheet view icon () is active in the lower-right corner of the Status Bar; the Design View icon () is inactive.

In the Views group of Home tab, the Design View command appears in color. When you click the down-arrow, you can see in the menu that the Datasheet view option is active, but the Design view option is inactive. The Design View icon is the one that appears in the View group because it’s the option that you can toggle to when you are in the Datasheet view.

Datasheet View button active on Home tab after clicking the Design View command.

Although the Design View command is in color in the Views group of the Home tab, Access is currently in Datasheet view.

Next, you’ll spend some time examining the table’s Datasheet view. When you create a new table, Access automatically creates a field (first column) in the new table. This field is called ID and Access assigns it to be a Primary Key. (A Primary Key is a unique record identifier for a table.)

The second column is ready for you to assign it a field data type.

The highlight on the first cell under the ID field heading indicates that it is ready for data entry.

Adding fields to an Access table

There are multiple ways to add fields to a datasheet. One way is to assign the data type and then enter the name of the field. A field’s data type determines the type of information that can be stored in that column of your table, and the type of information that cannot be stored in the column. For example, if you have a Funds field of data type Currency, you couldn’t enter letters or characters such as A, B, C; you also could enter letters into a Birthday field with a Date/Time data type.

There are ten basic data types available in an Access table:

• • Short Text (formerly called Text): alphanumeric data (names, titles, etc.). The maximum length allowed is 255 characters.
• • Long Text (formerly called Memo): large amounts of alphanumeric data, for example, sentences and paragraphs. The maximum number of characters allowed is up to about 1 gigabyte (GB), but controls to display long text are limited to the first 64,000 characters.
• • Number: numeric data.
• • Date/Time: dates and times.
• • Currency: monetary data, stored with four decimal places of precision.
• • AutoNumber: unique value generated by Access for each new record; often a primary key field that Access automatically generates in new Access tables.
• • Yes/No: Boolean (true/false) data. Access stores the numeric value zero (0) for false, and -1 for true.
• • OLE Object: pictures, graphs, or other ActiveX objects from another Windows-based application.
• • Hyperlink: a link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer.
• • Attachment: files such as pictures, documents, spreadsheets, or charts that you can attach to a field. Each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file.
• • Calculated: an expression that uses data from one or more fields. You can designate different result data types from the expression. Dependent on the data type of the Result Type property. Short Text data type results can be up to 243 characters long. Long Text, Number, Yes/No, and Date/Time should match their respective data types.
• • Lookup Wizard: the Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. Dependent on the data type of the lookup field.

The most common data type is Short Text.

Data types can have some unexpected properties. For example, the Text field can contain numbers, and numbers can have a data type such as Text, Number, and Currency.

For this next exercise, you will add these three fields to the Products table you created in the previous exercise: Product Name, Product Number, and Order Date.

To add the new fields to the Products Table in Datasheet view:

1 In the second column heading, click the Click to Add down-arrow, and then select Short Text. The words Click to Add are replaced with Field1.

2 Rename the heading Field1 by typing Product Name, and then press Enter.

3 Repeat steps 1 and 2 for the third column heading. Choose Number for the Data Type field and rename the heading to Product Number.

4 Repeat steps 1 and 2 for the fourth column heading. Choose Date/Time for the Data Type field and rename the heading to Order Date.

Creating a Date/Time field called Order Date in the Products Table.

5 Save the Product table, but don’t close it. You will use it in the next section.

Creating a table in Design view

You can use Design view to add tables and fields to your database. Design view offers more control over field structure and properties than Datasheet view. Design view is also well-suited for controlling field data types and descriptions.

When creating a table using Design view, you begin by creating the table structure in Design view; you then switch to Datasheet view to enter or import data.

To add a table to a database using Design view:

1 Using the SCRATCH database you used in the previous exercises, click the Create tab, and then select the Table Design command from the Tables group.

The new table appears as a separate tab and is open in Design view. Look at the status bar at the bottom of the window. The words Design View appear on the left side of the status bar, and the Design View icon () is active on the right side.

The Status bar shows the Design view icon. The new Table opens in Design View.

Adding fields to a table

Many database tables require a unique field (column) or set of fields that uniquely identify each record stored in the table. This field is called the primary key field. The selection of a primary key is one of the most critical decisions you’ll make in the design of a new table.

Primary key fields cannot be memo fields, OLE fields, or Memo fields. A primary key field is often a unique number or combination or letters and numbers that make each record unique. You will now add fields to the table you just created in Design view:

1 Add the following information in each of the columns:

Field Name	Data Type	Description
Vendor Number	Short Text	Vendor Identification Number
Vendor Address	Long Text	Vendor Address
Vendor Contact Name	Short Text	Contact Person’s Phone Number
Current	Yes/No	Active Vendor

Creating a Yes/No field in Design View.

For many fields, a description might seem unnecessary, but you should enter a description for any field that might have an unclear name. The description appears in that table’s status bar when the field is selected using Datasheet view; this is helpful to others when entering data.

2 From the Field Name column, select the Vendor Number cell.

3 From the Table Tools > Design context Ribbon tab, click the Primary Key button (). A small gold key appears to the left of the Vendor Number cell representing that this field has now become the primary key for this table. It will allow each row to be uniquely identified and assist in sorting, filtering, and table relationships.

Selecting the Primary Key.

4 Right-click the Table1 tab and choose Save from the context menu. The Save As dialog box appears.

5 In the Table Name text box, type Vendor Info. Then click OK.

6 Now, the table is ready for data-entry from the Datasheet view. To change views, right-click the tab of the Vendor Info table and select Datasheet view from the context menu.

Importing data from a Microsoft Excel spreadsheet

Spreadsheets can store data, but databases are more powerful, expandable, and often easier when managing large amounts of data. You can import data from external sources, such as text files, XML files, other database types, and spreadsheets into your Access 2013 databases. In this next exercise, you will use the Import Spreadsheet Wizard to import data from an Excel spreadsheet into your table.

To import data from Excel:

1 Open the program Microsoft Excel and choose File > Open. Navigate to the Access02lessons folder and double-click Orders to open the spreadsheet.

When the Orders spreadsheet opens in Microsoft Excel, notice that each column has a column heading (name) and that each row underneath is continual with no blank rows between.

This spreadsheets looks very much like an Access table. When the spreadsheet is formatted like this, it makes it easy to import it into Access as table.

2 Switch back to Access where your SCRATCH database is still active by clicking the Access icon found on the Task bar along the bottom of your screen.

3 In Access, select the External Data tab. From the Import & Link group, click Excel.

4 In the Get External Data window, click Browse, navigate to the Orders Excel file in the Access02lessons folder, and select it. Click Open. The path and file name of the Orders Excel file appears in the File name text field.

5 The option Import the source data into a new table in the current database is selected by default. Don’t change this selection.

6 Click OK. The Import Spreadsheet Wizard opens. Click Next; the first row of your Excel spreadsheet already contains column headings.

7 Click Finish, and then click Close.

8 In the Navigation pane, double-click the new table called Sheet1 to open it and see the imported data.

The data has been imported from the Excel spreadsheet into your Access table.

To rename the new table Sheet1

1 Right-click the Sheet1 table Tab, and select Close from the context menu.

2 In the Navigation pane, right-click the Sheet1 Table. A context menu appears.

3 From the context menu, choose Rename. The Sheet1 name appears highlighted.

4 Type Orders to replace the highlighted text, and then press Enter.

Self study

1 Try linking to the data in the Orders Excel spreadsheet. The procedure for linking to data in a spreadsheet is almost the same as the procedure for importing it that you completed in the previous section. Start at Step 2 of the importing procedure. When you get to Step 5, select the option Link to the data source by creating a linked table. Continue with the remaining steps. When you finish, you will notice that the default Linked Table name is also Sheet1.

2 Create another table in Design View. Practice creating fields and data types in the Design View, and then return to the Datasheet View to display the table.

3 Create another table in the Datasheet view. Practice creating new fields and assign them data types. Enter new records into the table and notice how the different data types determine what types of data will be accepted in the table.

Review

Questions

1 Name the two ways of creating a database in Access 2013.

2 True or False: When creating a table, the Status bar indicates which view you’re using.

3 True or False: Of the two views in this lesson, Datasheet view offers the most control over field structure and properties.

Answers

1 The two ways of creating a table in Access 2013 are: 1) by creating a blank database; and 2) by using a template.

2 True. When creating a table, both ends of the Status bar indicate the current view.

3 False. The Design view offers much more control over field structure and properties than the Datasheet view.