First we create one of the basic tables that will hold our data. The database dialog is on screen with notebook tabs labeled Tables, Queries, Forms Reports, Macros, and Modules. There are three buttons labeled Open, Design and New. The window layout varies slightly with the version of Microsoft Access, but the functions are consistent among versions.

Because you have an empty file, only the New button will be available to use. Click it with the left mouse button, or to use the keyboard be sure the database window is active and press Alt-N.

The first table we will create is the Manufacturers table. This will keep the information on the company or individual that manufactured the item that you are cataloging. In this case it is software.

In version 7.0 and 97 the New Table dialog pops up. We're going to create this table manually (sans wizards), so click Design View. Older versions will give you the table design window automatically.

The design window displays a grid with columns labeled Field Name, Data Type, and Description.

Field Creation Basics:

Click the first row under Field Name and type MfgID. You can capitalize and use spaces in field names in any manner that suits you. Keep in mind that these field names will appear in a lot of places, so make it look good to you and your users. Long field names require a lot of typing and screen space.

With your cursor in the Field Name field click the button with the key icon on the tool bar above, close to the Help item on the menu bar. This identifies the MfgID field as a key field. Important: Key fields require a UNIQUE entry for each record. This is necessary for records in this table to be related to records in another table.

As soon as you type the first field name Text is entered in the Data Type field and the Field Properties window appears below. The default data type is Text 50 characters long. The MfgID field will be 8 characters long. Change the field size to 8.

In the Description field type: "M- plus 1^st 4 characters of company name plus 3 numeric characters."

When your cursor is in the Format field an arrow appears on the right side. This suggests a drop down list with data from which to choose. Wrong! Too bad they didn't follow through and provide a pick list of formats as they did in Microsoft Excel. We'll have to get into the help file and get some help with the formatting here.

We want 5 alpha characters forced to upper case and 3 numeric characters. Put your cursor in the format field and press F1. Up pops the help at the Format Property page. You are given some jumps along with other format information. Click the jump labeled Text and Memo Data Types. The symbol for upper case characters is >. Click on Example near the top of the window. Note that @ is used as a placeholder for text characters. This is the information we need for our format field. Close or minimize help.

Type >@-@@@@@@@ in the format field. The "-" is displayed but not stored in the table.

Move to the Input Mask field below the format field. Click the ellipsis on the right to get some samples of input masks. None of the samples help in this case, so close it and enter AAAAA999 (5 alpha characters and 3 numeric characters).If you want a different caption than your actual field name when you see the table in data sheet format type your preferred caption in The Caption field. I will leave it blank.

Type MXXXX000 in the Default Value field. This will give each new record an ID allowing you leave the field to enter other information with which you will construct the actual MfgID. Microsoft Access will enclose it in quotes when you leave the field.

We will leave the Validation fields blank. Later we'll use Microsoft Access Visual BASIC For Applications to write a sub routine to create the MfgID number semi-automatically.

Double click on the Required field to change it to Yes. Leave the No on the Allow Zero Length field. When you made MfgID a key field the Indexed field changed to Yes (No Duplicates). In some cases its OK to have duplicates in an indexed field, but not in a key field.

We have finished creating our first field. The others will go much faster. Use the instructions above to create the fields as listed below:

For the remainder of the fields we'll list only the properties that require entry or change of value.

Because the post office reads addresses from bottom to top, we want to be sure the mailing address appears below the physical address if both are included on a mailing label. To make things simpler later we will move the mailing address so that it appears after the physical address in the table.

In the table design window click the left margin beside MfgMailAddress. Notice the cursor changes to an arrow with a shadow of a rectangle attached. Now drag it into position below MfgLocation. That's all there is to moving a field in the table structure!

Note: Indexing zip codes is useful if the application will be used for mailing purposes. The post office grants discounts to business mailers when mail is sorted by 5-digit zip and meets some other requirements.

Note: This field can be important if you are to use it for mailing. Otherwise it is unnecessary. We'll use it to demonstrate some things when we make a report using mailing addresses.

Note: Update helps you track when data was entered for this manufacturer. This can be a helpful reminder to change data if it becomes out dated. Use the ellipsis then choose functions, built-in functions, Date/Time, Date and Microsoft Access will enter Date() for you. This will make the Update field default to today's date when a new record is created. We can write a macro to replace the date when you change any important data in this record. This would be done in the data entry form. You do not need to change the Format and Input Mask fields.

Note: The purpose of this field is so the user can type any code they choose up to 5 characters to aid in the query filter process in the event there is no other field value available that will work for this purpose.

Table number one is created! If you want to take a look at your creation, click the icon on the tool bar that looks like a miniature spreadsheet (under the File menu). Wallah! Your new table opens with default values in the first record! Notice that the icon has changed to one that looks like a designer's rule, triangle, and pencil. This allows you to toggle between the open table and the table design window. This feature is available with all Microsoft Access objects.

When a form has been created, you will also have the option of seeing your data in the form view via these tool bar icons in the left corner of the tool bar.

Next we'll create a data entry form for the Manufacturers table.