TechRepublic Tutorial: Creating a support log using Microsoft Access 2000

In this article, I’ll demonstrate how you can use Microsoft Access 2000 to build a simple telephone support log. When you need to build a database in a hurry, you can follow these instructions to create one that suits your needs.

Downloading the database
You can either create the telephone support log using the directions in this article, or you can download it and follow along. Feel free to modify this simple database for your own needs. The download is available on TechRepublic.

Designing your database
The first thing you need to do when designing a database is think about what fields you will need. Many people who decide to build a database do not take the design phase seriously enough. It may be too late before you discover that you left something—or a lot of something—out.

Designing a database can be as simple as grabbing a pen and paper and writing down fields and records you need in the table. For our example, I’ll use a form designed by Jeff Davis, a former editor and contributor to TechRepublic’s Support Republic. Perhaps one of your own paper forms would also be a good candidate for a simple Access 2000 database.

Fields used in the telephone support log
TechRepublic’s telephone support log was created to help support staff track their calls. It was originally written as a Word document. The following table shows the log’s general design:

Below, I’ve listed the fields used in this log. Although the paper version uses ten fields, the database version contains 14. Note that I’ve added an initial field, called RecordNumber, to be a primary key. (I’ll explain primary keys later.) I’ve also split Caller’s Name into first and last name fields, which will make sorting records easier. I’ve done the same for Problem/Resolution. In addition, I’ve given these two fields a memo data type, because of the limitations placed on the size of text fields. (Text field entries can’t be greater than 255 characters in Access 2000, whereas memo fields can contain up to 64,000 characters.) In addition, I’ve changed the field names slightly. Note that I’ve assigned a data type to each field, based on the types available in Access 2000. Each text and memo field is assigned a length, based on what seems reasonable for the type of data the field will contain. You may want to modify the database design to suit your own needs.

In order to build this Access 2000 database, you will need little more than what you have here. So, with that in mind, let’s get started.

Building the telephone support log
Your first step is to launch Access 2000.The resulting dialog box offers you three options:

• ·        You can create a new database using a blank Access database.
• ·        You can create a database using Access database wizards, pages, and projects.
• ·        You can open an existing file.

For this project, we’ll use the Access Database Wizard, so select the option Access Database Wizards, Pages And Projects and click OK (see Figure A). You can also skip the initial dialog box and select File | New.

Figure A

Select a new database to begin.

Naming and storing the database
When the New dialog box opens, you’ll see three tabs: General, Databases, and Office 97 Templates. Click the General tab. (Although the Databases tab offers you several predefined databases to select from, you’re creating a new database, so we won’t use any of these.) On the General tab, select Database and click OK.

When the File New Database dialog box appears, select a location to save your file. Now you need to give your database a name. You can be as creative as you want, but use a name that tells you something about your database (such as SupportLog). This is good naming convention because it draws your attention to the words, and it just plain looks better. This convention is a carryover from the old programming days. Click Create to finish this step.

Using the Table Wizard to create the database
Databases are made up of one or more tables. The telephone support log, with its simple design, will use only one. The dialog box that opens after you click Create will display the name of your table in the title bar. In this case, the dialog box is called SupportLog: Database. Please note the different choices in this window, which appear on the left: Tables, Queries, Forms, Reports, Pages, Macros, and Modules. In this article, I’ll use Tables. You should be in the Tables section now. If not, click it. Select Create Table By Using Wizard and then click the New button to open the Table Wizard.

Using sample tables to create fields
Now you are presented some sample tables from which you can select predefined fields. You are given a choice of Business or Personal tables. For our example, select the Business option, as shown in Figure B. Later, you can modify these fields. But for now, using sample tables means you don’t have to create a database from scratch.

Figure B

Using sample tables and fields to create your table saves design time.

Choosing a primary key
Every table must have a primary key that Access 2000 uses to index records. Normally, the primary key is the first field. If you don’t assign a primary key, then Access automatically does this for you later. You are given the option of defining this key or modifying it in Table Design Mode. I’ll discuss this in more detail after we design the table and are ready to modify it.

For now, let’s choose a field to be the primary key from the Sample Table list. Scroll down this list and select Contacts. On the right you will see the fields that make up the Contacts table in a list called Sample Fields. Select the first of these, ContactID. Next, click the right arrow [>] button to add it to your new table. You’ll need to rename this field. Click on ContactID where it appears on the right under the column Fields In My New Table. Now click the Rename Field button. In the resulting dialog box, rename the field RecordNumber and click OK.

Field names don’t have to be limited to one word in Access 2000, and they may contain spaces. But it’s a good idea to keep the names as simple as possible. Using one word can help you remember what your field names are later on. I recommend you be consistent in your naming conventions.

Adding support call information
The first field that we need to create is CallDate. Scroll down to the Birthdate sample field and click it to select it. Next, click the right arrow button to add it to your new table.

The Birthdate field is exactly what we want since it is formatted as a date. The only problem is that it is not named correctly. So, rename it CallDate. CallDate now appears as the second field in your new table.

Next, we need to create our CallTime field so we can log the time a support call is received. Scroll through the tables in the Sample Tables list until you see Events and select it. Then, scroll down the Sample Fields list until you find StartTime. Select it, click the right arrow button, and StartTime will be added to your new table list. Rename the field CallTime. Now you should have three fields—CallDate, CallTime, and StartTime—in your new table list.
If you haven’t already done so, take a few moments to notice the arrow buttons [>], [>>], [<], and [<<]. Using these buttons, you can transfer either one field or all the fields to your new table. You can also remove fields using the single [<] or the all [<<] arrow transfer buttons.
Adding caller fields and adding support information
Now we need fields for caller information. This step is very simple. Scroll back to the Contacts table and you will have most of the other fields you need. Select the FirstName field and add it to your table, then rename it CallerFirstName. Do the same with the LastName field, calling it CallerLastName.

To enter the CallerDept field, scroll down to the Employees sample table and select it. Then, select DepartmentName from the field list. This field will fit nicely, and all we have to do is rename it CallerDept. Click your transfer arrow to move the field to your new table and rename it.

We’ll call the next field CallerPhone. In the Employees table, click the WorkPhone field, transfer it over to your new table, and rename it CallerPhone.

Now let’s finish our caller information. The Employees table has another field we can borrow for our database. Find the EmailName field, transfer it to your new table, and rename it CallerEmail.

The next four fields contain information about the support call. To create the ReceivedBy field, stay in the Employees sample table and transfer the field called LastName to your new table. Rename it ReceivedBy.

Using a memo field
The Problem field requires a special data type that can hold more than 255 characters, which is the limit for text fields. Access 2000 has a memo format for times when you need more room—for instance, when you have to add notes to a database. The limit of a memo field is 64,000 characters. This should be enough for any description.

Again, we can borrow this field from a sample table. Scroll down to the Notes field in the Employees sample table, select it, transfer it over to your new table list, and rename it Problem. Use the same Notes field again for Resolution.

Finishing the database
At this point, return to the Events sample table and select StartTime to use as our TimeSpent field. Transfer the field to your new table and rename it.

The original log that Jeff Davis designed has two checked-off items for Outcome: _Resolved and _Will Call Back. This information lets you see at a glance the status of the support call. Within the Events table is a sample field called Confirmed, which uses a Yes/No data type. Choose that field, transfer it over, and rename it Resolved. Transfer the sample field one more time and name the new field WillCallBack.

Congratulations! You have now created your first table. You should have 14 fields, including RecordNumber. There are two buttons on the bottom right of the Table Wizard: Next and Finished. We have to set a primary key and name our table, so click Next.

Modifying the database
The Table Wizard will prompt you to enter a name for the table you just created. Don’t confuse this with the filename, which contains the entire Access database you might create, including all tables, forms, queries, and other database tools. Let’s call the table SupportLog, as we did for the database file.

Next, the wizard gives you the choice of setting a primary key or having the wizard do that for you. Accept the default option—Yes, Set A Primary Key For Me—then click Next.

You are now given three options:

• ·        Modify The Table Design
• ·        Enter Data Directly Into The Table
• ·        Enter Data Into The Table Using A Form The Wizard Creates For Me

The default is normally the second option. Select the first option, Modify The Table Design, and click the Finish button. Figure C shows what Design mode looks like.

Figure C

Design mode allows you to modify your table, and you can correct errors here, as well.

Defining the primary key
The finished table now pops up in Design mode, and you can work with it. If you made a mistake in the previous section, you can correct it here.

Design mode is divided into two parts. A list of fields appears on the top. Next to them appears a data type and an optional description, which you can add if needed. Below the list appears a more detailed properties sheet for the field selected. The information categories change depending on the type of field selected.

At the top of the dialog box, notice the key icon next to RecordNumber. This icon means that Access has selected the first field as the primary key. This is what you want, so leave that choice selected. If you wanted another field to be the primary key, you could right-click over the field and select that menu option. The key icon would then move to that field. If you want to experiment with changing the primary key, do so. But don’t forget to change it back!

Adding and moving fields
Suppose you’ve forgotten to design a database with a primary key. It’s not an uncommon oversight. What can you do now? You need to insert a new field and make it the primary key for your table. Begin by right-clicking the first field (in this case, let’s use CallerDepartment) and selecting Insert Rows. A new row appears above the selected field. Now click under the Field Name column and type RecordNumber. Next, click under the Data Type column, click the down arrow, and select AutoNumber from the drop-down list. This sets the format for the RecordNumber field to AutoNumber. There, you have modified your first table. There is one more thing to do: Right-click on the leftmost column and select Primary Key from the context menu.

This was a test field. We don’t need it, however, so right-click it and choose Delete Rows to remove it. When working with tables, remember that rows are inserted above the selected field, but the current field is the one deleted.

If you want to change the order of a field in your table, right-click it and choose Cut. You can then paste it to another location.

Field data types
Now, select the CallDate field. Note that the Date/Time data type appears in the second column. Select that column and click the down arrow. The drop-down list shows all the formats you can use:

• ·        Text
• ·        Memo
• ·        Number
• ·        Date/Time
• ·        Currency
• ·        AutoNumber
• ·        Yes/No
• ·        OLE Object
• ·        Hyperlink
• ·        Lookup Wizard

Delving into data types goes beyond the scope of this article. However, the purpose of each choice should be fairly clear. By using the sample tables and fields as you did, you were able to select fields with the correct data types. If you need to, you can change any data type by selecting from the drop-down box.

Changing field lengths and other field properties
When I designed this table, I assigned each text field a length. Scroll down the list of fields and note how the field size changes on the General tab. Most text fields in this table have a length of 50 characters, which is the default length for text fields. Refer to the design sheet and change the text field lengths to their appropriate values.

Just for the record (no pun intended), take a look at the Input Mask, Caption, and Default Value properties of each field. Some fields contain input masks, which are ways to ensure the data is entered in a certain form. For instance, CallDate has an input mask to present dates in a familiar order. Not all fields have input masks. Captions appear in data entry forms. If you leave them out, the field names are used instead. In the Default Value field, you can assign a value to be automatically entered in each new record.

For more information on the properties available in Access 2000 fields, see the help documentation. I want to keep this introductory database simple, so I’ll move on.

When you’ve finished modifying your table in Design mode, close the window. Be sure to save the table. Figure D shows the fruit of your labor.

Figure D

This is how the completed SupportLog table appears in the database window.

Looking at the completed table
You should now be looking at your SupportLog database, with the SupportLog table that you just created visible. Note that you can do the following:

• ·        Open the table for entering data.
• ·        Design the current table that you have selected.
• ·        Create a new table.

Databases often use many tables; larger corporate databases may require hundreds of such tables.

Just for fun, click the Open button and enjoy looking at the table you’ve created. You should be familiar with what your new table looks like. You might even try entering and sorting some data directly in the table, as shown in Figure E. If you’ve used Microsoft Excel, this process will be familiar to you. However, it’s much more attractive, and convenient, to use forms for data entry and retrieval. You could try to use the Form Wizard to create one now to slick up data entry in your telephone support log.

Figure E

You can begin entering data in the completed table.

Conclusion
In this article, I’ve explained how to design a simple table for a telephone support log database using the Table Wizard in Microsoft Access 2000. This wizard can help ease the design of your own simple databases used in your IT department. Access 2000 is a rich-featured, complex database program. I’ve introduced some of those features that will help you design your own databases.

Dallas G. Releford has worked in the computer field as a programmer, MIS manager, PC specialist, and in other related positions. In addition, he has written a novel, which was published on the Internet and led him to an interest in the electronic publishing field. He also writes articles, electronic books, and just about anything else that involves the written word. To learn more about Dallas’ business, visit his Web site, which is called The Editor’s Eye .

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today