The Tables of a Database

Introduction to Data Fields

A Database From Scratch

In the previous lesson, we saw that you could create a database using one of the available templates. This approach provides you with readily made objects you can directly start using. Another technique consists of creating an empty database that has no built-in object. Starting a database from scratch allows you to create and add its different objects when necessary. The main advantage of this approach is that you will exercise as much control as possible on your database because you will be creating all of your objects. The disadvantage is that you will miss that primary layout that the templates offer. Creating a database from scratch simply means starting from a blank database and adding the different components. Of course, after learning how to perform some changes, you will be able to modify some aspects of a database, whether created from a template or started from scratch.

To create a database from scratch, if you are just launching Microsoft Access, in the left section, you can click Featuring. Then, in the main section in the middle, click Blank Database, give a name in the File Name text box, and click Create.

1. Start Microsoft Access
2. To start a new database, in the main section and under New Blank Database, click Blank Database
3. In the right section, set the File Name to Clarksville Ice Cream
4. Click Create to create the new database file

Imagine you create a list of people with whom you have some type of relationship. The list can be started with names as follows: Bill, James, Hermine, and Khan. This type of list is considered one-dimensional because all of its items fit in one category. In order to create a more significant list, you may want to include these people’s email addresses and probably other related information. If you include these additional pieces of information in the same category, the list may become confusing. To arrange the list, you would divide it in categories. Here is an example:

This type of display allows you to refer to a piece of information by its category. This is the basis of a table. A table is a two-dimensional list of items so that the items are arranged by categories. A complete or incomplete series of items that represent each category is called a record. Therefore, a table can be represented as follows:

In database development, a category is represented as a column. Sometimes it is also called a field. A record is represented as a row.

A field is an object used to host, hold, or store a piece of information of a database. Before such an object can perform its function, it must first be created. In the strict sense, the most important or the most regularly used fields of a database are created on a table, but tables may not be the friendliest windows to present to a user for data entry. For this reason, fields can and should also be created on other windows.

The process of making a field available depends on the type of object it will reside on and probably how the field will be used. This leads to two categories of fields: those that can receive or store information and those that can only present or display it. All fields of a table, especially in Microsoft Access, are created to store data. On the other hand, fields on almost all of the other objects (queries, forms, reports) can be made of combinations of already existing fields of a table, or they can be created independently of any existing or non-existing data.

A table is presented as a rectangular window represented in the middle of the screen with a tab. As an option, you can remove the tabs and let the tables display without them. To do this, you can click the Office Button and click Access Options. In the left frame, click Current Database and, in the right frame, in the Document Window Options section, click the Tabbed Documents radio button and remove the check mark on the Display Document Tabs check box:

After making the selection, you can click OK. You will be asked to close and reopen the database:

Which you should do.

On the left side of its top section, a table presents an icon . The table icon provides a menu you can access when you right-click. The menu allows you to save or change the view of the table.

When a table displays with a tab, the extreme right section of the tab(s) shows a close button you can click to close the table.

In Microsoft Access 2007, by default, a table in Datasheet View is represented with a tab, which prevents the user from moving it around. If you want, you can make the tables of a database appear as single documents. To do this, open the Access Options dialog box from the Office Button and click. In the left frame, click Current Database. Under Document Window Options in the right frame, you can click the Overlapping Windows radio button before clicking OK:

You will be asked to close and reopen the database, which you should do.

Although a table is primarily recognized as an arranged list of columns and rows, each column and each row intersect to create a rectangular box called a cell:

The cell is actually the object that holds data of a table. A cell holds only one piece of information. The database developer decides what type of information would go into a cell but the user types that information. Based on its arrangement of cells, a table is said to display in a datasheet layout or simply a datasheet (because its view is made of cells).

If you start entering data into a table and there are more records than the height of the table can display, the table would be equipped with a vertical scroll bar. The vertical scroll bar would allow you to move up and down on the datasheet. This is useful if/when a table has more records than can be displayed all at once, and this will happen regularly. In the same way, if a table contains one or more records than the width of the table can display, the table would be equipped with a horizontal scroll bar:

The presence or absence of one or both scroll bars is automatically managed by the operating system and you should not be concerned with the management of the scroll bars. At the same time, if your users need to use a table, they know how to use a scroll bar.

The lower left side of the table is made of four buttons used to navigate the table, one button used to create a new record, and a text box. Each button plays a specific role:

Button	Name	Role
	First Record	Allows moving to the first record of the table
	Previous Record	Allows to move one record back (if there is one) from the current record
	Current Record	Displays the number representing the current record out of the total number of records
	Next Record	Allows moving you one record ahead
	Last Record	Allows moving you to the last record of the table
	New (Blank) Record	Used to enter a new record on the table

To have a table as part of your database, you must create it and you have various options. If you create a database using one of the local templates, it would provide you with a few tables that you can start using. To use a sample table, after starting a database, on the ribbon, you can click Create. Then, in the Tables section, click Table Templates to see a list of sample tables:

From the menu that appears, you can click one. This would open a new table for you. You can then save it.

1. Open the Rockville Techno database created in the previous lesson
2. On the Ribbon, click Create and click Table Templates
3. Click Assets

Like everything that is part of a database, a table must have a name that can be used to refer to that object throughout the database and in other files that can be linked to the table. There are two main ways you can specify the name of a table, either when you save it for the first time or if you decide to rename it.

To save a table you have just created:

• On the ribbon, you can click the Office Button and click Save
• You can right-click the Table_X (such as Table1) tab and click Save
• You can press Ctrl + S
• You can start closing the table. You would be prompted to save the change (if its structure has been changed from the structure it had before it was opened)

If a table has not been saved before, you will be asked to provide a name for the table. A table can have almost any name: Employees, 2&&4DG, Pestes, verTT#@tg, etc. Because you and your users should be able to easily identify a table, there are suggestions you should follow when naming it:

• The name of a table should reflect the kind of data it is holding
• You can name a table with a few words, with spaces
• You use a prefix that identifies the table as such, a table. The name of a table would be preceded with tbl. If the name includes one word, such as Musicians, you can give the table a name like tblMusicians, another name would be tblStudents
• If the name of the table reflects a combination of words, such as Bank Accounts or Students Academic Numbers, you should start each new word with an uppercase. Here are examples: tblBankAccounts, tblStudentAcademicNumbers, tblMemberRegistrations.

In our lessons, we will not use the convention of prefixing the name of a database object with a three letters. For example, the names of tables will not start with tbl. We came to this decision because most other database environments, including Microsoft SQL Server, do not suggest this rule. Although the lack of this rule may create some confusion, because you will end up having a table and a form with the same name, we will strive to explicitly state what object type we are referring to. For example, we will avoid writing, "Open Employees" or "Open the Employees object". Instead, we will usually state, "Open the Employees table". This should clearly indicate that you must first click the Tables button in the Database Window, and then open the indicated table. In the same way, if we write, "Open the Employees form", you must first click the Forms button in Database Window, then open the indicated form.

1. To save the table, right-click Table1 and click Save
2. Set the name of the table to Corporate Items and click OK
3. To close the table, click its Close button

Because the table is the primary object of a database, when you create a table and save it by giving it a name, Microsoft Access creates a section in the Navigation Pane and name that section after the table. In the same way, you can have as many sections as the number of tables on the database. Here is an example:

As we will see in the next lesson, some objects created that are based on the table would be listed under the table name in its category. Here is an example of two objects added to the section of a table named Customers:

Each section is expandable and collapsible. To expand a section, click the name of its section. to collapse a section, click the blue header of its section.

Once a table has been created and saved, it is represented in the Navigation Pane with an icon and a name.

The most regular operations you will perform on a table require that you open it first. To open a table, first locate it in the Projects Navigation window then:

• You can double-click the table
• You can right-click the table and click Open

Any of these actions causes the table to display in Datasheet View in the central area of the screen. In the same way, you can open as many tables as necessary. When a table is opened, it is represented by a tab that displays its current name if it was previously saved, or its temporary name if it has never been saved.

After, or when not, using a table, you can close it. Before closing a table, first select its tab. Then, to close a table:

• You can click the close button on the right side of the tabs
• You can press Ctrl + Shift + F4

In order to use a table, some operations require that you (or rather the user) first select(s) it:

• To select a table in the Projects Navigation window, simply click it
• If you had opened many tables and they are displaying in the main area of the screen, to select one, click its tab
• If you have many tables displaying in the main area of the screen, you can press Ctrl + F6 continuously to switch from one table to the next until the desired one displays

We saw that, when or after creating a table, you must save it to make it part of your database. When saving it for the first time, you must give it a name. If the name of a table is not appropriate, you can change it. To rename a table, in the Projects Navigation window, you can right-click the name of the table and click Rename.

1. In the Projects Navigation window, right-click the Corporate Items table and click Rename
2. Type Employees Resources as the new name of the table and press Enter

If you have a table you don't need anymore, you can remove it from your database. Because you cannot delete a table if it is opened, you must first close it.

To remove a table from your database:

• In the Navigation Pane, right-click the table and click Delete
• In the Navigation Pane, click the table to select it. Then, on the ribbon, click Home. In the Record section, click Delete
• In the Navigation Pane, click the table to select it and press Delete

In each case, you will receive a warning to confirm what you want to do. It is important to know that if you delete a table, because it is not a file, it doesn't go into the Recycle Bin: it is lost, including its records. Therefore, before deleting a table, make sure you really want to get rid of it. When in doubt, don't delete it.

1. In the Projects Navigation, right-click the Employees Resources table and click Delete
2. Read the warning of the message box and click Yes

If you are aware of a good table that is part of another database, you can add it to your database. This is referred to as importing a table. There are various techniques you can use and various types of tables you can import, including tables created from other types of applications. To import a table:

• You can right-click any table in the Projects Navigation window, position the mouse on Import and select from the menu
• In the ribbon, you can click External Data and select from the Import section

Although we didn't mention it so far, a table doesn't exist without a column. Put it another way, a table must have a least one column in order to have meaning. This also means that, in order to create a table, it must actually have at least one column. If you start a table in Datasheet View, Microsoft Access creates and gives it one default column. Once a table displays in Datasheet View, you can start entering data into its cells. If you do this, whenever you enter data under a column, that column receives an incremental name: the first column on the right side of ID would be named Field1, the second would be Field2, and so on. These would be insignificant and can even be confusing. Fortunately, you can give meaningful names to the columns of your table.

A column can have almost any name: "Book Title", "DG$5@H", "This Is The Book Title", "R8%%DW$4", etc. Some columns will have only tinny data, like a person's age, the number of books in an office, a person’s middle initial, etc. It may not be a good idea to give them a name that would take too much space. In the future, when you start programming, it would be easier to handle field names that are composed of only one word, without space.

To name a field, first figure out what the data in the category will be made of. If you are planning to enter employees' salaries in a field, you can just call it Salary. If you want to specify employees’ names by first name, middle name, and last name, you can use such column names and make them distinct. In this case, you could name the first column as firstname (in one word), the last name would be called lastname. Although this good technique allows you to use one word name, some people might be confused. The suggestion is to distinguish wherever a new English (or the language you are using to design your database) name starts in the field name, by using a starting uppercase. Instead of firstname, you can use FirstName. Instead of fullname, you can use FullName. Instead of firstdayofmonth, you can use FirstDayOfMonth.

Before changing the name of a column:

• You can double-click its header
• You can right-click a column and click Rename Column
• When any cell under a column has focus, on the ribbon, you can first click Datasheet. Then, in the Fields & Columns section, click Rename Column. 

Any of these actions would put the name of the column into edit mode. You can then type the new desired name.

1. Re-open the Clarksville Ice Cream database created earlier
2. To create a new table, on the ribbon, click Create and click Table
3. Double-click Add New Field
4. When it is highlighted, type Order Date and press Enter
    
   
5. As the caret in blinking in the next column header, type Order Time and press Enter
6. As the caret in blinking in the next column header, type Container and press Tab
    
   
7. Close the table
8. When asked whether you want to save it, click Yes
9. Type Customers Orders as the name of the table and click OK

If you create a table using one of the templates, it would have ready-made fields. If you create a table from scratch, it would not have as many fields. Fortunately, Microsoft Access provides many pre-configured fields you can add to your table, whether you create the table based on a template or from scratch. These ready-made templates are referred to as a Field Templates.

Before using a field template, from the ribbon, click Create and click New Table. This would start a new table and the ribbon would switch to the Datasheet tab. In the Fields & Columns section, click New Field. This would display the Field Templates window:

By default, the Field Templates window is positioned on the right side of the screen. This is a dockable window: you can move it to the left or place it somewhere else on the screen.

The items in the Field Templates window are organized in categories identified with a labeled node, such as Basic Fields, Assets, or Contacts. Each node displays a - button. This - button indicates that the node is expanded. To collapse a node, you can click its - button, which becomes a + button. To expand a node, you can click its + button. Here is an example where the Assets, the Contacts, and the Issues nodes are collapsed while the Events and the Projects nodes are expanded:

To use a field from the Field Templates window:

• You can double-click the field
• You can click and drag the field from the Field Templates and drop it on the table to the left of Add New Field:
   
  

1. To start a new table, on the ribbon, click Create and click Table
2. In the Fields & Columns section of the Datasheet tab, click New Field
3. Under the Field Templates, if necessary, expand the Assets node.
   In the Assets section, double-click Manufacturer, Model, Acquired Date, and PurchasePrice
4. Expand the Contacts node if necessary.
   Under Contacts, drag Company and drop it between ID and Manufacturer
5. Right-click Table1 and click Save
6. Set the name to Company Assets
7. Click its Close button

Although a column completely depends on a table, both objects are closely related when it comes to their maintenance. In other words, the maintenance performed on one object can directly affect the other. For example, in the previous sections, we mentioned that you could import a table from a text file or from another application. We also mentioned earlier that a table could not exist without at least one column. When you import a table, you are in fact also importing its column.

1. Re-open the Rockville Techno database
2. On the ribbon, click External Data
3. Click the Access button
4. In the first page of the wizard, click the Browse button and locate the Clarksville Ice Cream.accdb file to select
5. Click Open
6. Accept the first radio button to import and click OK
7. In the Tables tab property page of the Import Objects dialog box, click Company Assets and click OK
8. In the next page of the wizard that announces success, click Close

Some operations will require that you select a column but it depends on the operation. In some cases, when any cell under a column has focus (for example if the caret is blinking in a cell of a column), the column is considered to be selected. Otherwise:

• To select one column, position the mouse on its name until the cursor points down and then click
   
  
• To select columns in a range, click and hold your mouse on one of them, then drag to the left or to the right to cover the other desired column or columns. When all desired columns are highlighted, release the mouse
• To select a range of columns, click one column that will be at one end, press and hold Shift, then click the column that will be at the other end, and release Shift

If you create a table using one of the Table Templates or if you get a table from a Database Template, the columns of the tables are wide enough to show their names. If  you create a table from scratch in the Datasheet View, Microsoft Access assigns it a default width. That width can appear to be too narrow or too wide. For example, when data exceeds the regular width of a column, part of the information would be hidden. If a particular column contains data that you want to display at all times, you can enlarge the column. On the other hand, if a column displays tinny pieces of information, you can narrow the column to let it just fit the data as desired. There are various techniques you can follow to widen or narrow a column.

To change change the width of a column:

• you can position the mouse on the right border of a column header. The mouse pointer would change into a horizontal double arrow crossed by a vertical line:
   
  
   
  If you double-click, the column would be re sized to the widest value of the column, provided the widest value is wider than the column header. If the widest value is narrower than the column header, the column width would be widened enough to display the name of the column.
• You can click the column's right border and drag in the desired direction, left or right until you get the desired width
   
  
• You can right-click a column's name and click Column Width... This would open the Column Width dialog box where you can type the desired value and click OK
   
  

If you find out that a particular column is misplaced in a table, you can change its position.

To move a column in Datasheet View, first select it, click and hold your mouse on it. Then, start dragging left or right in the desired direction. While your mouse is moving, a thick vertical line will guide you. Once the vertical line is positioned to the desired location, release the mouse:

To move a group of columns, first select them as we reviewed earlier, click and hold the mouse on one of the selected columns, start dragging left or right in the desired direction until the thick vertical guiding line is positioned in the desired location, then release the mouse:

Inserting a column consists of adding one between two existing fields. There are various ways you can do this:

• To insert a column using the ribbon, first click a cell under the column that will succeed it. Then, in the Fields & Columns section of the Datasheet tab, click the Insert button
• To insert a column from the table, right-click the column that will succeed it and click Insert Column
• To insert a field from the Field Templates, click and drag it from the Field Templates and drop it to the left of the column that will succeed it
   
  

1. Open the Clarksville Ice Cream database
2. In the Project Navigation window, double-click the Customers Orders table to open it
3. To add a new field, right-click the OrderDate column header and click Insert Column
4. Click the empty cell under Container
5. On the ribbon, click Datasheet
6. In the Fields & Columns section, click the Insert button

We saw various techniques of naming a column. You can use the same approach to change the name of a column. To put the name of a column into edit mode and then change it:

• You can double-click its name on the header
• You can right-click the column's name and click Rename Column
• When any cell under the column has focus, on the ribbon, click Datasheet, in the Fields & Columns section, click the Rename button

1. Right-click the Field1 column header and click Rename Column
2. Type Clerk and press Enter
3. Click the empty cell under Field2
4. In the Fields & Columns section of the ribbon, click the  Rename button
5. Type Flavor and press Enter
6. Close the table
7. Re-open the Rockville Techno database
8. Right-click Company and click Rename Column
9. Type Asset Type and press Enter
10. Close the table

If a table contains many columns and at one time you think you don't need all of them, you can hide some. To hide a column, you can drag the right border of its column header completely to its left border as we saw earlier to change the width of a column; when the vertical guiding line reaches the left border, release the mouse: the column would be hidden from the table:
 

To hide one or a group of columns, you can right-click and click Hide Column. You can also right-click any column and click Unhide Column. This would open the Unhide Columns dialog box. Here is an example:

To hide a column, clear its check box.

To reveal a column or a group of columns previously hidden, right-click any column header on the table and click Unhide Column. In the Unhide Columns dialog box, put a check mark on each column you want to show.

If you have an undesirable column in a table, you can delete it. It is important to keep in mind that if you delete a column, it also goes with any information under it.

To remove a column from a table:

• You can right-click the column's name and click Delete Column
• You can select the column (or a group of columns), right-click anywhere in the table and click Delete Column
• You can select a column (or a group of columns). Then, in the ribbon, click Datasheet. In the Fields & Columns section, click the Delete button

Any of these actions would present you a warning to confirm whether you still want to delete the column(s) or you want to change your mind. If you still want to remove the column(s), click Yes.