Microsoft Access How-to: Create a Table

access logoOK, so you've opened up Access a couple of times, clicked on a couple of things, and closed it right down again. It's really hard to know where to start, and reading the help files and manuals don't really seem to help much. Well, let's fix that. We'll start by creating a really simple table, which is where the 'rubber meets the road' when it comes to databases.

The true core of any database system is the table. A table is nothing more than a software object that allows you to store data. It's actually simpler than you would think.

I'm using Access 2003 for these examples, but they should look fairly close to older versions

First, start by opening Access. You'll need to create a new, blank database to get started. Click on the 'New' icon in the upper left corner, or select 'File -> New' from the main menu:

 

access create table 01

 

Now, if you're not using Access 2003 you're going to get a series of dialog boxes here, so it's going to look a little different than what I'm showing. The options are basically the same, so you should still be able to follow along. In 2003 you'll see a navigation bar pop up on the right side of the application screen, which will look like this:

 

access create table 02

 

One of the things that's a little different about Access, compared to other Office applications, is that you need to create and name the database before you can start working in it. In Word, Excel, etc., you can immediately start working in an un-saved document, then you need to save it later. Not so in Access, you need to save it up front. So click on 'Blank database', and select what you want to call it and where you want to save it. I've called mine example.mdb, and saved it in My Documents:

 

access create table 03

 

The navigation bar will go away, and you'll see the main database object window that should look something like this:

 

access create table 04

 

The database window is how you will create and select various objects in your database. You'll notice some default wizards, that are really quite helpful with many of the things you'll want to do at some point. For the purpose of this exercise, however, we'll be overlooking the wizards and create our table manually. To do that, make sure that 'Table' is selected under 'Objects' in the database navigation bar, then simply select 'New' from the database menu:

 

access create table 05

 

You'll get another dialog box that looks like this:

 

access create table 06

 

OK, now here the default selection is create and open your new table in what's called 'Datasheet view'. This means that Access will create a blank table for you and show it to you ready to input data. What's kind of nice about this is that you can enter in some example data that you're going to be using, and Access will try to figure out the data type (more about this in a minute). Let's give it a try. Click OK, and you'll get the default table setup. Go ahead and enter some typical address information into it:

 

access create table 07

 

Now, go up the main menu and select the 'Table Design' icon:

 

access create table 08

 

Access will now show you the table in design mode. Design mode allows you to customize the data, add more fields, delete fields, add indexes, change data types (in some cases), and other typical database stuff we don't really need to go into right now. It should look something like this:

 

access create table 09

 

Let's go right down the line.

  • First of all, you'll see that Access created an 'ID' field, which is a number field that 'auto increments'. They now call this field data type 'AutoNumber'. What this means is that every time you add a new record, this field will be filled in automatically with the next number. A couple of important points about this field:
  1. You should always have an ID field. They are very useful, and sometimes necessary when you start joining tables.
  2. In addition, iin most cases this is the field you will want to use for creating a 'Primary Index'. We won't go into that right now, suffice to say that you should always have a primary index in every table you create. When you create a table this way Access will automatically create a primary index on this field.
  3. The number that is shown in this field does not represent the number of records in the table. If you delete a record the AutoNumber field does not reset, so it is possible (and likely) that the ID field will be a higher number than the total number of records in the table.
  • Click on the second column down, labeled 'Field1'. For right now all we're going to do is rename these fields so they mean something to us.
  • You will notice on my examples that I use upper and lower case letters in the field names (Fname, StreetNumber, etc). This is done just a personal preference, you don't need to use upper and lower case if you don't want to. In its default configuration Access is not case-sensitive, and for the most part ignores case in text. Most databases do not, and will treat Fname and fname as two completely different fields.

    You may have noticed that when I had you enter the data I split the first name from the last name. In general, you do not want to lump different pieces of information into a single field. The reason for this is that you may want to manipulate this data, for example you may want to sort by last name. If you have a field that has both first and last name this will be very difficult to do. So, just highlight the text 'Field1' and type in 'Fname' (no quotes):

     

    access create table 10

     

    Just a couple of quick items about naming your fields. Don't use any character that is not a number or a letter except for a dash (-) or an underscore(_), and don't use spaces. Any other character or punctuation mark is going to get you into trouble.

    Rename Field2 to Lname, and Field3 to StreetNumber. Now, on this field we're going to want to change the datatype from a number to text:

     

    access create table 11

     

    If you leave it as a number datatype you will not be able to enter any text into the field. So, if you run across a street number that has some text in it you won't be able to enter it into the field. A text field will accept numbers, but a number field won't accept text.

    Let's change the rest of the fields as listed below:

     

    access create table 12

     

    One other note, you'll notice that I changed Postal to a text field, also. Here in the states you could leave this as a number field, but as soon as you need to try to use this table for any international address you're going to find text in the postal code. Anybody from outside the US would need to modify the fields to more closely work with your address formatting. Phone had defaulted to a text field because we had entered non-numeric characters in the data.

    OK, so lets take a look at the results. Change the table to datasheet view by clicking the View icon on the main toolbar:

     

    access create table 13

     

    As soon as you do that, you're going to get the following dialog box prompting you to save your table:

     

    access create table 14

     

    Select yes, and save it as something that means something to you.

     

    access create table 15

     

    You should now see something that resembles this:

     

    access create table 16

     

    Congratulations! You've created your first database table from scratch. One final note, you don't enter any value in the ID field. To create another record just start typing in the next first name and the ID field will fill in automatically.

    Bytes: