In our previous lesson we focused on designing our database.
We said that we wanted a customers table, an orders table, and books table.
So let's start by creating our books table.
I could have started with customers, orders or books, but in this case I think it makes
sense to start with books since we need to have our inventory of books before our customers
can place an order.
I've already opened Access from my start menu so now we need to decide whether we want to
use a built in template or start from scratch using a blank database.
Let's start from scratch!
Click on the blank database icon to open a new database.
Now we need to name the database.
By default Access assigns a name and also adds the file extension even if you delete
it while you're entering your name.
Just delete this to name your database.
In this case I'll name it Ready2Read and click Create.
The database opens in Datasheet view and you'll see Table 1 listed in the Navigation Pane
and on the table tab to the right.
Table 1 is the default name Access assigned to your first table.
To give the table a unique name click the Miscrosoft Office button and select Save from
the menu.
A dialog box will open asking you to name your table.
I'll name it Books and click Ok.
The new name changes in the Navigation Pane and on the table tab.
It looks like I misspelled books.
To rename a table go to the Navigation Pane, right-click on the table and Select rename.
If your table to the right is open you'll receive a message telling you to close it
before you can rename.
Just Ok.
To close the data table to the right, right-click on the table tab and select Close.
Now, right-click on the table in the Navigation Pane and select Rename.
A cursor appears which means we can start typing.
Left-click anywhere outside of the field to make the change.
Double click on the table to reopen.
By default Access started us out with one table.
To create additional tables click on the Create tab, and then Table, in the Tables group.
A new table tab appears.
From the MS Office button select Save to name your table.
We'll name this one Customers.
We have once more table to add, the Orders table.
You can always tell which table you're in by seeing which tab is highlighted.
So currently we're in the Orders table.
I'm going to go back to the Books table by clicking on the Books tab.
Now let's start adding some fields.
By default Access starts us off with an ID field.
The ID field is already set to AutoNumber which will give each record a unique number.
Remember, each record is one row of information.
Each table you create in Access will have this ID field so it's a good idea to give
this field a unique name in each table because it will be easier to understand later.
We'll name this Book ID by clicking on the ID header and then click on Rename in the
Fields and Columns group on the Ribbon.
Let's add our other fields.
To do this double click on Add New Field.
The text will disappear so you can enter your field name.
We'll start with Title.
Once you've added this new field you can press Tab on your keyboard to add other fields.
Repeat these steps until all of your field headers have been entered.
I'm going to quickly add all of my field headers here.
In Access we can work in Datasheet view which is our current view, or Design view.
Datasheet view is kinda like working will cells in Excel where Design view gives you
more control over the information you enter into cells which in this case are actually
fields.
Let's take a look at the Design view.
The Views option appears under the Home Tab and the Datasheet Tab which is the tab that
is currently open.
Click the drop down arrow under the Views option and select Design View.
The benefit of setting up our fields here is that we can tell Access to allow only certain
types of data to be entered into a field.
For example, I need to add a price field.
I can do that here under Field Name.
If I press Tab on my keyboard, I'm now in the Data Type field.
This field is used to define the type of data that can be entered.
We'll talk about data integrity and validation later.
By default the data type is text.
If I click the drop down arrow I can see additional options.
Since price deals with money, I'll change my data type to currency.
You won't see any immediate changes by changing the media type but you'll notice it later
when we start entering data.
The information here at the bottom will be discussed later so there's no need to worry
about it right now.
I'm going to switch back to Datasheet view to finish setting up the fields in my tables.
Oops looks like I have to save my table before I move on.
Now we can switch back to the Datasheet view.
Now let's add fields to the Customers table.
As I said before, the first field is the ID field by default.
We'll rename this field by clicking on the Datasheet tab under Table Tools and then Rename
in the Fields & Columns group.
Now we're gonna call this Customer ID.
I'll go ahead and add my other fields.
Once I've added all of my fields I can reorder these fields by dragging and dropping.
Here I'll show you.
Click on a field header.
Move your mouse in the area of the header and when you see a cross with arrows appear,
hold down your left click button and drag the column to another location.
I can also delete a field by clicking on the field I want to get rid of and from the Fields
and Columns group on the ribbon click Delete.
If you inherited this database meaning someone else set it up for you, you want to be careful
if you decide to delete anything in a database because it could impact other parts of the
database.
We're almost finished!
Now we need to add fields to the Orders table.
I'll start by changing the ID field.
Earlier we talked about the ID fields and how they assign a unique number to each record.
For our Orders table we want to figure out who is ordering our books.
So the first field I'm going to create is the Customer ID field.
This is where a customer's unique ID number will reside.
The next field will be the Book ID.
This will tell me which book my customer ordered.
I'll explain more about the Customer ID field and the Book ID field in our next lesson.
Remember the point of our Orders table is to see who ordered what and when so we need
to add a date field.
We just learned how to use Design view to change the data type but we can also do it
from the Ribbon in the Data Type & Formatting group.
To do this click on a field header and then click on the drop down menu beside Data Type.
It automatically defaults to Text.
But since we want to know the order dates we're going to change it to Date/Time.
Immediately you don't see any changes at all but once we start entering our data later
you'll see this calendar icon come up.
Again, we'll talk about this a little later.
Setting up tables and fields in Access is just the beginning.
Now we need to link them all together - to build the relationships in our relational
database - because that is where the real power is in Access.