Home and Learn: Microsoft Access Course

Access Design View

We'll now create a table to handle the books that each author has written.

To create new tables, click on the Create ribbon at the top of Access:

The create ribbon at the top of Microsoft Access

The Create ribbon is where you create new tables, queries, forms, and reports.

Click the Table item on the Tables panel. You'll see a new table appear in the main window, along with a new item in the Access Objects panel on the left with the default name Table1. Rename this table like you did before: either click the Save icon in the top left, just above File, or right click the Table1 tab in the main window and select Save from the menu. Call your new table tblBooks. Your All Access Objects panel on the left should look like this:

The All Access Objects panel showing two tables created

Design View

Make sure you are on the Fields ribbon at the top of Access. On the left of the Fields ribbon, you should see an item called View:

The Fields ribbon in Access showing the View option highlighted

Click on View to see two options, Datasheet View and Design View. Select Design View.

The View menu in Access showing Design View selected

The tblBooks tab in the main area will change to this:

An Access table in Design View

When you were in Datasheet View, you could set up your table and enter data. In Design View, you can only set up a table. You can't enter data in Design View.

The first column is called Field Name. This is, obviously, where you enter the names for your fields. The second column is where you select the Data Type for your fields.The third column is so that you can enter an optional description for the field.

Underneath the three columns is an area with two tabs, General and Lookup. We'll use Lookup soon. But the General tab gives you options for each field in your table. The options will change, depending on the data type you select for a field.

But let's set up three fields in this table.

Click into the box where it says ID, under Field Name. Type BooksID instead. The Data Type is fine on AutoNumber, so leave that.

Now click into the second cell under Field Name, just below your BooksID field. Enter BookTitle as the field name, all one word. Click into the Data Type and you'll see a dropdown list of data types you can choose from. We can have Short Text again, as this would give us a maximum of 255 characters to play with for our book titles. Some books have really long titles. But not that long!

With Short Text selected for the Data Type, notice that the General tab below it has changed:

The Short Text Data Type selected in the Access Design View

These are all properties you can add or change. For example, note the Field Size item at the top. If you want to change how many characters can go into your field, click inside the box and type a new number. We'll leave it on 255, though.

We'll meet the other properties in the General boxes as we go along in this course. For now, let's add one more field.

Just under BookTitle, click inside the cell and type Genre as the name of the third field. What we'll do here is allow a user to select from a dropdown list of Genres, like Thriller, Science Fiction, History, etc.

For the Data Type, select Number. Your Design View will then look like this:

Access Design View showing a third field added, set to Number

If you click the dropdown box for Field Size, where it says Long Integer, you can see a list of number types you can choose from:

Access Number types in a dropdown list

These are all the different number types you can choose from, as described in the previous lesson. If you get the type wrong here, it can cause serious problems further down the line. For example, if you select Integer as the Number type and the number of rows in your table goes above 32,767 then you'll start getting errors.

What we can do now is to create a new table for the different types of book genre. We can then use this table as a dropdown list for our Genre field.We'll do that in the next lesson below.

<--Back to the Microsoft ACCESS Contents Page

Email us: enquiry at homeandlearn.co.uk