Databases can be used to keep track of a variety of things such as customer orders, your favorite recipes, or your Christmas card list. This first in a series of three articles will walk you through the basics of creating a simple database with Microsoft’s Access 2000. If you would like to follow along with the examples, download our sample database (a simple address book) or create your own as we go along.
Start with a blank database
When starting Microsoft Access 2000, you are given the option to create a blank database, use a database wizard, or open an existing database (see Figure A). By not using the wizard, you will learn more and can customize the database to better suit your needs. Select Blank Access database and click OK. You will then be prompted to create and save your database. You can name your database whatever you like, but for this article, our database will be named “database_sample_1.mdb.” Once you’ve entered the file name, click Create.
|Select Blank Access database.|
You should now see a screen that looks like Figure B.
|From here, you can see the various parts of an Access 2000 database.|
Create your table
The next thing you have to do is create your table. Think of a table as a spreadsheet of information. You are going to have columns and rows, just like in Microsoft Excel. Each row will only contain data pertaining to one entity (in the case of this sample—a single person). After deciding to create a new table, you will be presented with more options including creating the table with a wizard. Again, we’re going to stay away from the wizards. Double-click Create table in Design view, and the table window, shown in Figure C, will appear. Here you will be able to name the column headings and decide what type of information the table will hold.
|The Microsoft Access 2000 table window|
Labeling your fields
Type your desired field names in the Field Name column. Create a field for everything you think you will need to complete a person’s address. Next to each field name you enter, you will be required to select a data type. There are several from which to choose. Use “text” for the fields titled “first name,” “last name,” “street,” “city,” “state,” and “zip code.” You could make the zip code a number data type, but that is usually reserved for numbers you plan to perform calculations upon.
Changing the field length
Each field automatically defaults to a length of fifty characters. That means you have fifty spaces allotted for each first name, last name, street, city, state, and zip code. However, you really only need two spaces for the state. To change the field length, click on the word “Text” next to the state field. You will now see some information at the bottom of the screen. Change the field size from 50 to 2 (see Figure D). Now repeat the process to change the number of spaces allotted for the zip code to 5 (unless you plan to use a zip plus four format, in which case you can set the zip code field length to 10).
|The field size for “state” has been shortened to two characters.|
Setting a field’s default value
To save yourself some typing when entering data, you can set a default value for each field. Enter the name of your city in the Default Value field at the bottom of the screen. Now, when you enter a person’s city, the default value will automatically appear. You can, of course, enter a different city during data entry, but if most of your people are from one city, this step can save you time.
Set the table’s primary key
Every table must have what is known as a primary key—something that will uniquely identify each row of the table. In this address book example, the primary key could be the person’s first name. However, you may have more than one Jason in the table, and this will cause problems. It’s often customary to have an ID number associated with each row (or person) that serves as the primary key. For this example, we will use what is known as a combination key (i.e., the primary key will be made up of the person’s first and last name).
Click on the small empty box to the left of the first name field (it’s usually gray in color) and hold the Ctrl key while clicking the empty box next to the last name field. Both fields will be highlighted. Now, click on Edit | Primary Key. A little key should appear in those empty boxes next to both the first and last name fields.
Saving your table
Now that the fields are created, you need to save your table. Click on the X in the corner of the window. You will be prompted to save your new table. Click Yes, enter a name for the table (see Figure E), and click OK. If you forgot to choose the primary key, Access will let you know.
|Name your table here, then save it.|
Entering your data
You should now see your table listed in the main database screen shown in Figure F.
|Your new table is now listed in the main database screen.|
Double-click on the table you just created, and the Table window will appear on your screen (see Figure G). All you have to do is enter the data you want stored in the table.
|You can enter your data directly from the Table window.|
When you are finished with one row, press Enter, and a new row will appear (notice that the name of the city is already typed for you). You must have some data in both the first name and last name fields or you will not be able to continue entering data into the table. This is because the primary key fields that you selected cannot contain null, or empty, values. When you are finished entering your data, just click the X in the corner of the Table window, and your work will automatically be saved.
Until next time…
That’s all for this lesson. I hope this article has shown you how easy it is to get started with Microsoft Access 2000. In my next article, we’ll add some other tables to the database and discuss creating relationships between them.
Do you have a great Microsoft Access tip that would benefit your fellow TechRepublic members? What do you think of Jason’s brief introduction to Access 2000? Post a comment or write to Jason and let him know.