When you create a table with Table Design, you can be more deliberate about designing your database, set field types, create a lookup list, and create a foreign key to pair with your primary key.
Create a table with Table Design
Select Create > Table Design.
In the new table, for the first field, enter a field name and a data type.
To set the primary key for the table, select the Field Name cell for the appropriate field and then select Primary Key.
The Primary Key indicator appears to the left of the field name. You can change the primary key at any time, but each table can have only one primary key.
Select File > Save, and name the table.
There are many more options when you build and modify tables with Table Design.
Switch between Design View and Datasheet View for any saved table.
Designate any field as the table’s primary key.
Use Builder to create an expression or input mask.
Test validation rules.
Insert and delete rows.
Create lookup lists.
Create, rename, and delete data macros.
Define relationships and object dependencies.
You can create a table in Datasheet View or in Table Design view. Working in Datasheet View is simple—a lot like entering data in a spreadsheet. On the other hand, when you work in Table Design view, you can be more deliberate about designing your database and setting your field types. You can also create a lookup list, and also create a foreign key to pair with your primary key.
On the ribbon, we select Create, then Table Design. If you like using hot keys, hold down the Alt key and press the letter next to the command you want to use. In this case, that's “C” and then TD.
The Table Designer starts and this new tab appears. This is a CONTEXTUAL TAB. Access has several of them and they only appear when you need them. This one has commands for inserting or deleting fields, and one for setting a primary key.
Here in the Field Name column, we enter Phone ID. From the Data Type list, we select AutoNumber and then select Primary Key. We can also right-click the row and see many of the same commands. Again, press Alt plus the letter next to the command you want to use.
For the next field, let’s enter Phone number and select the Short Text data type.
Below that, let’s enter Type and start to create a lookup list. Select Lookup Wizard, type the values we want, and then select Next. One column is fine. In the first row, we enter the phone number types for the list: office, then mobile, fax, emergency, and home. Select Next. The field already has the name we entered, so now we just select Finish.
We could have made this field into its own separate table, but we didn’t because it's a small amount of data. What we've done instead is create a lookup list. So now when we enter a name, we just pick the right value from the list that we set up.
Finally, we need to create our foreign key. Let’s call it Customer ID FK. Customer ID is the name of the primary key in the Customers table and FK stands for foreign key. In other words, this is a shorthand way of knowing that this is a foreign key and its primary key is the customer ID field in the customer's table.
Next, we set the data type to Number. This brings up a database design rule. The data types for your primary and foreign keys have to match or be compatible. In this case, we’re using a compatible data type, as the primary key in the Customers table is an AutoNumber data type field.
Remember, Access controls the values in AutoNumber fields. It keeps them in sequence as you add customer names, and you can never duplicate any of those sequential values. Because you need to duplicate values in the foreign key field anytime a customer has more than one phone number, we wouldn’t want to use the AutoNumber data type here.
We need a compatible data type, though, and in this case it’s Number. Other data types like Text are also compatible with your key fields.
Save your table, calling it phone numbers.
Now you know how to create a table in Table Design View, and in the process, create a value list and a foreign key. That’s it, we’re done!