Creating relational tables within Access is fairly simple. If you havent done so already, open the Microsoft Access database you created in our last article. Youll then be presented with a menu allowing you to create new tables using one of three methods:
- Create table in Design view
- Create table by using wizard
- Create table by entering data
The most common data types that youll use are text, number, date/time and yes/no. For example, when designing the Inventory table, youll use the following fields:
- Item ID (type: AutoNumber)
- Description (type: Text)
- Quantity (type: Number)
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 youve 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 (dont forget that theres a Date/Time data type!).
After completing these tables, congratulate yourself! Youve made significant progress toward designing your Access database. Join us next time as we learn how to create relations between tables.