Universal Class: Over 500 Online Certificate Courses
 
 
Creating a New Database and the Process of Adding Table using Access 2016
 
 

Creating a New Database and the Process of Adding Table using Access 2016

Under the File tab, click New. You can choose Blank Database or use an Office.com template. We're going to select Blank Database.

vlcsnap-2016-03-23-06h28m51s139

To the right, you'll be asked to create a name for this database, then click Create.

You'll then see this Access screen:

vlcsnap-2016-03-23-06h28m57s202

This is a zoom of the screen above, left section

vlcsnap-2016-03-23-06h28m57s202

 

What you have now is a blank database. You are ready to start entering data. But remember, to add data, we must first create tables. Let's learn to do that now.

Create a New Table

To create a new table, go to the Create tab. You'll see it to the right of the File tab. Click Table to add another table.

vlcsnap-2016-03-23-06h29m27s237

Access creates one table for you by default, so when we do this, we'll have two tables, as shown below.

vlcsnap-2016-03-23-06h29m37s93

You can click on the Table1 or Table2 tab to navigate between tables.

On the left hand side of the screen, right click Table1 and select Design View. There are several different views in Access 2016. For now, open Design View. This is where you can design the table by determining what fields you want to store in the table.

vlcsnap-2016-03-23-06h29m48s204

Access will ask you to name the table. Enter a table name, then click OK.

We've named ours Books.

vlcsnap-2016-03-23-06h30m01s77


In Design View, you can go ahead and name the table columns and apply constraints over them in the Field Properties at the bottom of the above pictured window. By clicking on ID, you can also add your Primary key.

We're going to make our Primary Key ISBN.

We'll write that in the Field Name. This is the name of the field/column.

vlcsnap-2016-03-23-06h30m08s143

Next, we'll "tab over" to Data Type and specify the type of data to be entered into the ISBN field, by pressing the Tab key. Select "Number".

vlcsnap-2016-03-23-06h30m20s6

Next, we'll tab over to Description and type a description of the data entered in the field. To go back to the previous field at any stage, hold Shift and press Tab.

We can enter in information so that this table also has an Author, Title, Publication Date, Price, and Genre fields.

When we're finished entering all fields for this table, this is what we have:

vlcsnap-2016-03-23-06h30m30s107

Note the key beside ISBN. This is our primary key.

 

Once you're finished with this, change to the Datasheet View. You can do this by either clicking on the icon on the bottom right hand side of the screen:

vlcsnap-2016-03-23-06h30m43s224

Or, by clicking on the dropdown arrow under the View button on the Design tab.

vlcsnap-2016-03-23-06h31m02s165

You'll be prompted to save the table, then you'll be able to enter information into it. Click Yes.

vlcsnap-2016-03-23-06h31m30s194

Adding Data to a Table

There are several ways to add data to a table. The easiest way is to enter data into the table when you are in Datasheet view.

vlcsnap-2016-03-23-06h31m30s194

This looks and works similar to an Excel spreadsheet file. As we discussed earlier, the columns represent pieces of information and the row is a single record of all of that information.

We've entered the following information into our Books table. It's good to enter it now, as the next few lessons involve getting the data from these tables.

ISBN

Author

Title

Publication Year

Price

Genre

0393059804

Sebastian Junger

A Death in Belmont

Apr 18, 2006

9.58

Fiction

0618915478

Philip Roth

Exit Ghost

Oct 1, 2007

7.37

Fiction

0062088025

James Grippando

Lying with Strangers

Apr 24, 2012

8.99

Fiction

0316067369

Alice Sebold

The Almost Moon

Sep 8, 2008

Interested in learning more? Why not take an online class in Microsoft Access 2016?

9.01

Fiction

0440245117

John Grisham

The Confession

Jul 19, 2011

6.04

Fiction

0345531964

John Grisham

The Testament

Dec 27, 2011

8.99

Fiction

You can enter this data by typing it into the cell, and pressing Tab or clicking on the next cell. Once you enter data into the blank line, a new blank line will appear.

Your table should now look like this:

vlcsnap-2016-03-23-06h31m40s42

Creating a Second Table

Now, we're going to create a second table, and then link it to the first table. This is the main benefit of creating databases. It allows you to create separate sets of data, and link them together, to keep your information organized.

We are going to create a simple Orders table. This table will store some data about orders that have been made for books.

Traditionally, order tables can be quite complicated. Orders can have multiple items, items can belong to multiple orders, and orders can have different prices or discounts. For our example, though, we're going to make it simple.

· One book per order

· No price changes

· Only some information about destinations

Click on the Create tab and then on Table, just as you did earlier. A new table will be created.

vlcsnap-2016-03-23-06h32m25s235

Click on the View button and select Design View. This is where we define the fields.

Access will first ask you to save the table. Enter "Orders" and click OK.

vlcsnap-2016-03-23-06h32m31s40

Now, enter the following fields into the Design view:

Field Name

Data Type

Description

Order ID

Autonumber

The order's ID number

ISBN

Number

The ISBN number of the book attached to this order

Order Date

Date/Time

The date the order was made

Destination Country

Short Text

The country where the order is being sent to

An Autonumber field is a special type of field in Microsoft Access. It is a number field, and each value is unique and is automatically generated by Microsoft Access. It's good for ID numbers and for primary keys, which, as we learnt earlier, need to be unique.

Your Design View should look like this:

vlcsnap-2016-03-23-06h32m35s85

Save your database, and then click on the View button to change to Datasheet view.

Your table starts like this:

Enter in these values for the orders. You can just press Tab when you select the Order ID field, as you don't need to type anything in.

ISBN

Order Date

Destination

393059804

Jan 21, 2014

United States

62088025

Feb 10, 2014

England

618915478

Feb 25, 2014

United States

393059804

April 4, 2014

Australia

316067369

May 11, 2014

United States

Your table should now look like this:

Linking Two Tables

We have two tables: Books, and Orders. Now it's time to link them together.

To be able to link them together, we need a field in both tables that match. They don't need to be the same name, they just need to contain the same data.

Normally, you would use some kind of ID number, whether it's a real number (social security number, phone number) or a made up number (employee ID, author ID). This is good database design, as you want the field that links the tables (the ID) to be different to the label for those fields (book title, order destination, etc.), in case the labels change.

In this example, we'll be using the ISBN to link the two tables. Save the table, and then click on the Table tab.

Click Relationships.

If you have no relationships created, this form will be displayed.

This view will show each of the tables you have and how they relate to each other. First, we need to specify what tables to show.

In this window (pictured above), you see all of the tables and queries available in your database. If you want to see just Tables, click the Tables tab, if you want to see just queries, click the Queries tab, but if you want to see both, click the Both tab.

Select the tables or queries you want to create relationships for, then click Add. You may have to press the CTRL key to be able to select more than one.

In this list, we have both tables. Click Add after selecting each table to add them to the Relationship View.

vlcsnap-2016-03-23-06h33m18s255

Now, click Close.

The Relationship View is now shown.

This shows our two tables, Books and Orders. In each table are the fields. It also shows a small key icon next to the primary key, which is the unique identifier for the table.

We need to join these two tables together so that they are related. To do this, first work out what fields we want to join on. In this example, we will use the ISBN as it exists in both tables.

Because the ISBN field is being used for our relationship, it can be called a primary key and a foreign key (which we learnt earlier).

The primary key is the field that defines the record. The foreign key is the field that identifies the record in the related table. It's important that we get these around the right way.

In most cases, the primary key in one table (the Books table) is the foreign key in another table (the Orders table). This is what we will use.

If you're not sure, here's a way I use to work it out (sometimes I forget as well). I ask myself two questions to work out which is the primary table and which is the foreign table.

"Does (table1) have many (table2)s, or does a (table2) have many (table1)s?"

So, in this example, I would ask:

"Does a book have many orders, or does an order have many books?"

We've stated that an order can only have one book, for this example. So, it means that the first question is correct. This also means that the Book is the primary table and the Orders is the foreign table.

Click on the ISBN field in the Books table.

Click and drag the word ISBN in the Books table onto the word ISBN in the Orders table

vlcsnap-2016-03-23-06h33m46s9

The Edit Relationships dialog box will appear.

vlcsnap-2016-03-23-06h33m52s83

This means that the ISBN field in the Books table will be related to the ISBN field in the Orders table. If you've dragged the field onto the wrong spot, you can change the fields in this list to match what is shown.

There are a few options on the bottom of this screen:

· Enforce Referential Integrity: This means you must select a value in the primary table (Books) that exists in the related table (Authors). For example, if you enter a new book and need to specify an author, the value must exist in the Author Name field in one of the records in the Authors table. It's a good idea to check this option.

· Cascade Update Related Fields: This means that when you update the value in the Author Name field in the Authors table, any related records in the Books table get updated.

· Cascade Delete Related Records: This means that if you delete a record in the Authors table, any records that have that author in the Books table will also be deleted.

vlcsnap-2016-03-23-06h34m04s202

Click Create when you're done.

If you get this message when you click Create, it means that you can't make the changes because the table is already open. Click Cancel, close the table, and create the relationship again using these steps.

Once the relationship is created, the Relationship View will look like this:

You will have a line between the two tables, which match to the two fields you've linked. At each end of the line will be a symbol.

The "1" and the infinity symbol (∞) indicate what's called a "one-to-many relationship". It means that one record in one table relates to many records in the other table. In this example, it means that "one book record has many order records". So, a book can be in multiple orders (it can be ordered more than once), but an order can't have more than one book.

Viewing Related Data

A helpful feature of Access 2016 is the ability to easily view related data once the relationship is set up. We can go into our Books table and see what orders relate to that book.

Open the Books table.

vlcsnap-2016-03-23-06h34m19s96

You can see on the far left of each record is a plus symbol.

 

This indicates that the record can be expanded.

Click on the plus symbol for a record.

vlcsnap-2016-03-23-06h34m26s159

Access will show a smaller table underneath, which displays all of the records in the Order table that have that book. You can also enter new data in here too, which may be easier than remembering the ISBN and adding it into the other table!

Save a Database

Now that we've started to create our first database, we'll want to make sure we save it if we leave Access to work on something else – or just want to make sure we Save every so often as we work.


To save a database, click the File tab again. On the left side, you'll see your options:

vlcsnap-2016-03-23-06h34m33s235

Click Save to save the database as the current file name (example). Or, if you want to save it under a different name, click Save Database As.

You may see this message:

https://cdn.4uc.org/gmzo/468/9770431/download/images/8174093.png

If you've saved your table, click Yes.

If you haven't saved your database yet, or wish to save it as a different filename by using the Save As option, you may be asked for a location.

Choose the location where you want to save your database on your computer, enter a name for the database, then click Save.

To save a table, click Save Object As under the File tab.

vlcsnap-2016-03-23-06h34m48s127

Open an Existing Database

To open an existing database, go to the File tab and click Open. Search for the database on your computer. Select the database you want to open, then click Open.

vlcsnap-2016-03-23-06h34m58s224


 

 
 
Popular Courses
 
Learn More! Take an Online Course...