Introduction to Tables

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 Office 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.

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 you 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.

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:

• 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 can 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 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".

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 Navigation Pane 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.

After 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 Navigation Pane, 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 or its title bar
• 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 Navigation Pane, you can right-click the name of the table and click Rename.

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 does not 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, do not delete it.