For you to store data in a Microsoft Access database, you must first design one or more tables to hold the data. Once the table(s) are created, we can input data into the table(s).
What is a table?
A table is a collection of related data about a particular topic stored in rows (called records) and columns (called fields). In fact, for those familiar with Microsoft Excel, a table is nothing more than a Microsoft Excel worksheet.
Every column in a table is a field containing a category of information. The name of the field is always displayed at the top of the column. For example, in a database for storing individual contacts, you may have First Name, Last Name, Address, and Phone Number appearing at the top of each column.
And every row in a table is a record containing fields of information. The name of the record is invisible, but are represented by numbers starting from 1. Thus the first record is always row 1, the second record is row 2, and so on.
It is important to remember that all tables in a Microsoft Access database should be designed properly at the very beginning if they are to have the flexibility and power of creating almost any type of form, report or query you may want with respect to the nature of the topic or purpose behind your database.
How do I design a proper table?
The first step in creating tables is to name each table in the correct manner. Although the naming conventions we will use are not necessary, they will help greatly in maintaining consistency and simplicity when creating and modifying your database using Microsoft Access.
The currently accepted method of naming objects in Microsoft Access is as follows:
| Object | Tag |
| Table | tbl |
| Query | qry |
| Form | frm |
| Report | rpt |
| Macro | mcr |
The aim, therefore, is to precede any name you give to your tables (with the first letter of the name capitalised) with the above designated three letter tag in lowercase (small) letters. When naming your tables, or any object for that matter, make sure the names have no spaces or underscores and contains only letters or numbers.
The second step in creating tables is to get each table to focus on one, and only one, topic. For example, is the table designed to store just the names of people or their addresses (i.e. the topics are people and addresses respectively)? Or to store the names of products and their prices (i.e. the topic is products)? Once the topic of your table is clear in your mind, gather all the relevant data you need for that particular topic. By relevant data, we mean getting a list of names for all the fields you will include in your table.
In the technical jargon, simplifying your table designs to focus on one topic is called normalising data or normalisation. To help break tables down into their basic topics:
- Look for repeating groups of fields which list the same category of data in multiple fields (e.g. Project 1, Project 2 etc instead of just Project Number);
- Look for repeating or duplicate values in a field, especially if they do not directly relate to the primary key field;
- Look for blank values in non-primary key fields as these are likely to contain duplicate records of the same primary field value; and
- After checking for the above areas to help break down tables into single topics, look for redundant fields in any table and remove them for greater simplification.
As we shall see later, a primary key field is a way for Microsoft Access to quickly index the records in a table for faster sorting and retrieval. But the field can also be used as a key to understanding what your table is really about.
The reason for focussing on only one topic for each table is primarily to eliminate redundant and duplicated information in different tables, as well as to make the managing of the data easier and more effective. It can also make future changes to your database very easy if you design your database in this way.
The third step in creating tables is to design each field for storing data in a table such that it contains the smallest meaningful representation of your data. For example, you could name a field in your table as Full Name as part of a database for storing individual names of people. However, you will not be able to sort the field by last name. If you divide the field into two smaller fields called First Name and Last Name, you will find it a whole lot easier to sort your database records later.
I am ready to create a table. What do I do now?
Click on the New button in the main database window (shown above), and type the name of your new table. Press the Return key to activate the table.
In older versions of Microsoft Access, you may be asked how you want to create a table. For example, you may be asked whether you prefer the Table Wizard instead of creating an empty table from scratch.
OR
In Microsoft Access, you can work with a table in two views: (i) the Design View; and (ii) the Datasheet View.
If you want to add fields to your table, or modify the structure of a table in any way, use the Design View. If you just want to add, edit or analyse the data itself (i.e. view the records themselves), use Datasheet View.
If you are in the Database window, click on the Design button to start building your table in Design View. Later you can add and/or edit the data in your tables by selecting the name of your table and clicking on the Open button.
Start entering the names of all your fields you require for your table in the Field Name column. An example of what a table in Design View with fields added to it should look like, see below:
The Data Type column as shown here is where you specify exactly the kinds of information you want stored in each field. The information data type can be either text, numbers, dates, pictures, hyperlinks (1) and so on. By specifying the data type of each field, you will ensure that Microsoft Access can properly and efficiently store your data for quick retrieval at a later date, as well as reduce the likelihood of entering the wrong information into the fields during data-entry.
The Description column is purely for ease of designing your database. You enter comments here explaining what your field is about. It may make it easier for you later should you have to make future modifications to your table.
How do I add new fields?
If you want to add a new field of your own choosing and design:
- Click the first empty Field Name box.
- Type a name you want. Note that spaces in the name are acceptable.
What about inserting a field above an existing one?
If you want to insert a field above an existing field in the list:
- Click the field selector to the left of the existing field.
- Click the Insert Row button on the toolbar.
- Now create your field by typing its name in the empty Field Name box.
I want to delete a field. How can I do that?
To delete a field from your table design:
- Click anywhere in the row that defines the field you want to delete.
- Click the Delete Row button on the toolbar.
- A warning message will be displayed. Click OK if you want to delete the field and its data.
Now I want to move a field. Can I do that too?
Yes you can! To move a field in your table design:
- Click the field selector to the left of the field you want to move.
- Click the field selector again and, while holding down the mouse button, drag the row to where you want it.
- Release the mouse button. Microsoft Access will reorder the fields in your table to reflect the change.
Can I limit the type and range of values of the data to be accepted in my fields?
Yes. To change the data type of a field to restrict its type and range of values it will accept:
- Click the Data Type cell to the right of the field you want to change the data type in the Table Design window.
- Click the grey button with an arrow pointing down inside the cell.
- Select a data type from the list.
To limit the range of values you will accept from a user when typing data into a field, there are additional field properties you may wish to investigate. Further details are explained in the next section.
How do I sort and find records quickly in my table?
You must specify an indexed field in the field properties under "Indexed" in the bottom part of the Table Design window. You will have a choice of indexing with or without duplicate records, or no indexing at all. If you just want Microsoft Access to display unique records only, choose "Indexed (No Duplicates)".
How do I set a field to be automatically indexed by Microsoft Access?
You use what is called a Primary Key. The purpose of a primary key is (i) to help Microsoft Access automatically index a field with unique data as required for finding data more quickly; and (ii) to assist in the development of relationships between tables so that one properly indexed field in a record can pick out information in another table.
Table relationships are something we will discuss in the next section. For now, we will concentrate on making sure a field is created or chosen to make a good Primary Key so that Microsoft Access can properly index the data in the field.
For example, a field designed to store social security numbers is a good candidate for a primary key. This is because you will know the numbers have to be unique or else the social security system would collapse overnight! However, a field containing the last names of people is not a good primary field because you will get duplicate data (e.g. Smiths, Jones and Bakers).
Some fields do not make good primary key fields and others may be good. The best choice is a field that can uniquely identify each record, such as IndividualID.
You can have more than one primary key field. If, say, two fields are assigned primary keys in the same table, it simply means that Microsoft Access cannot accept duplicate combinations of values. For example, an order can have many products, but each product can be listed only once per order. So you can do the following:
To set a primary key to a field:
- Click the field selector for the field you want to assign a primary key.
- Click the Set Primary Key button on the toolbar. A symbol of a key will appear next to your field.
To create more than one primary key in a table, press and hold down the Ctrl key as you click the row selector for each field your want to designate as the primary key. Once the rows have been highlighted, click the Primary Key button.
I'm ready to save my table design. What button do I press now?
Click on the Save button on the toolbar. If you have not assigned a name for your table, Microsoft Access will display a dialog box where you can name your table just before clicking on the Save button.
NOTE: It is highly recommended that if you intend to modify a table in your database in future, you should make a backup copy of the table. Use Copy and Paste commands to do this. Why? You could inadvertently lose data in your table should you decide to change the field type in one of your fields, resulting in a truncation or undesirable conversion of the data, and so on.
