Previous page Previous     TOC Contents     Next pageNext

Lesson 3 - How to create the database

The new "MikeVideo" database

In this lesson we're going to discuss how to build an Access database from scratch.

When you load the Access software, you have to specify whether you are using an existing application or creating a new one. If this is your first session, you want to create a new database.

To your keyboards. START ACCESS!

And, because you want to learn something from the ground up, you won't use the wizards, pages and projects which are the database-creation templates.

Once you've mastered the concept, then you can use the shortcuts and let Access guide you through the process.

And again, to make it interesting, just choose General Database.

Choose a name for the database and store it in a folder.

In Access, a database, no matter how many tables or forms or reports it contains, is stored in a single file. That file has the extension: .mdb (for Microsoft database). When you want to move or copy or delete the database, all you have to do is work with the one file.

So far, you have an empty database.

The first object you create to define the database is the table.

A table is the description of an entity. It is used to store the data. When you create the table you first describe its structure.

A table in Access is sort of like a spreadsheet in Excel: it consists of rows and columns. Only, it is more organized than a spreadsheet and that organization will later allow you to retrieve the data in all kinds of ways.

The design view of the table is meant to define the structure.

You name all the columns (in technical jargon they would be called attributes of the entity). Remember to use the prefix for the table, in this case c_ because it's the Customers table.

Then, choose a Data Type for each column.

The Data Type defines what kind of data will be contained: text, numbers, URL, dates, etc. This lets the system know how much memory to reserve and also, what you are allowed to do to the data. For example, if you specify that a column is a "Number" type, you won't be allowed to put "XYZ" in it.

To see the data types available, click on the Data Type column and do F1 (the Help function). Here's what you should get:

And here are the basic rules on how to use them:

  • When you define a primary key field and it doesn't matter what the format is, use AutoNumber. If the identifier is formatted, like A9-1234, use Text.

  • When the data is a date of any kind, Date of birth, Date hired, Invoice date, etc., you have to use the Date/Time data type.

  • Most fields will be Text. Even a phone number (it contains a dash), or department number that happens to be 101.

  • Use Number only for fields that will be used in calculations (+ - * / ) like quantities or salaries. If it does not have to be calculated, use Text. Even if the Department_number is 101, define it as text: it's not the number one hundred and one, it's the characters "1""0""1". Believe me, it will make your life easier.

  • Use Memo when the Text field may be too big. If you are doing a Patients table for a Doctor, the Diagnosis field should be Memo.

  • Whenever a field has only a yes/no answer, use the Yes/No type. For example: Paid?, Member?, Active?

The Properties at the bottom will usually be acceptable for the data type you selected.

But, to simplify things later there is one property you should indicate: Caption.

The Caption is the name that will show for the column on forms and reports and so on. If there is no caption, the Field Name is used. So, if you want forms to show "Customer ID" instead of c_custID, define the caption.

Before starting to enter data, you should define the data formats in the Windows Control Panel.

Access gets its formatting information from Windows.

If you want dates to automatically display as YYYY-MM-DD, as you should, you set that property in Windows.

The same for currency and number formats. Different countries have different ways of displaying those types so, you set them for your country in the Windows environment.

Now, complete the Table, as defined in the model from Lesson 2, adding all the fields required.

Before you save the Table, make sure the Primary key is identified. To do that, click on the grey button to the left of c_CustID and that should select the whole row. Then click on the key in the toolbar. That should put a little key symbol on the grey button, identifying that field as the Primary key.

Save the table and, if this is the first Save, it will ask you to name it (don't keep Table1 as a name!).

Finally, repeat the whole process with the Movies table and you will be well on your way.

Next week: Linking Tables together through Relationships. See you then!