From Mike Chapple,
Your Guide to Databases.
FREE Newsletter. Sign Up Now!

Welcome to Part 6 of our “Building an Access Database From the Ground Up” series. In this installment, we’ll build the tables necessary to implement the relational structure developed in Part 4 that builds upon the Patrick’s Widgets scenario from Part 3.

Creating relational tables within Access is fairly simple. If you haven’t done so already, open the Microsoft Access database you created in our last article. You’ll then be presented with a menu allowing you to create new tables using one of three methods:

1. Create table in Design view
2. Create table by using wizard
3. Create table by entering data

We’ll go ahead and use Design view. You’ll then be presented with a grid that allows you to specify the elements of your table. You must enter at least two elements: the “Field Name” and the “Data Type.” For the field name, simply enter the names that you assigned to the variables when you created the relational model. Select an appropriate data type for each of those variables and you’re in business!

The most common data types that you’ll use are text, number, date/time and yes/no. For example, when designing the Inventory table, you’ll use the following fields:

1. Item ID (type: AutoNumber)
2. Description (type: Text)
3. Quantity (type: Number)

Note that the Design view also allows you to enter an optional description for each field, at your discretion. You'll also notice that we used an unusal data type for the item ID -- AutoNumber. The use of this data type instructs Access to fill in the value automatically with a unique number. Using the default Access settings, the first record we create will have an ItemID of 1, the second will have an ItemID of 2 and so on. We chose this data type because we intend to use the ItemID as the primary key for this table. Remember that a primary key is a field used to uniquely identify records in the table.

Before saving the table, right click on the ItemID field and choose the "Primary Key" option. This will instruct access to use the ItemID field as the table's primary key.

Once you’ve successfully created the Inventory table, go ahead and repeat the process for the Customers, Orders, and OrderedItems tables. To give you a hint on data types, make all of the ID fields (e.g. ItemID, OrderID, CustomerID, etc.) AutoNumber fields (and the primary key). The OrderedItems table doesn't have a straightforward primary key, so don't specify one. Access will prompt you to automatically create a new key when you attempt to save the table. Tracking codes should be text, as they are alphanumeric in nature. The remaining fields should be self-explanatory (don’t forget that there’s a Date/Time data type!).

After completing these tables, congratulate yourself! You’ve made significant progress toward designing your Access database. Join us next time as we learn how to create relations between tables.