Microsoft Access Table Description

Table Description:
A table is made of various parts to help your creativity.

On top of the table, you have columns. These are fields of data. They are originally named Field1, Field2, Field3, etc. Each column represents one category of information. On the left side of the table, you have rows that represent records for each item of our database. Each row has a Row Selector, the gray square; it allows you to select the whole row.
On the main area of the datasheet, the white rectangles separated by gray borders are the cells. Each cell is the intersection of a column and a row. Each cell is considered a field of data.

On the right side of the datasheet, there is a vertical scroll bar. It allows you to move up and down on your datasheet, this happens if/when your table has more items than can be displayed all at once, and this will happen (almost) all the time. There is usually another scroll bar on the bottom right side of the table. It allows you to move left and right between unseen areas of the table. Finally, the lower left side of the table is made of five buttons used to navigate the table, and an edit box.

The most left button allows you to move to the first record of the table. The second button moves you one record back from the current record. The edit box shows the number representing the current record. The third button moves you one record ahead. The fourth button moves you to the last record of the table. And the last button is used to enter a new record on the table.

Naming The Fields:
Double-click the header of the first field (Field1). When it is highlighted, type BookTitle (in one word), and press Enter. A field can have almost any name: "Book Title", "This Is The Book Title", "My Book Titles", etc. Some of your fields will have only tinny data, like a person's age, the number of books in her office, her MI (middle initial name), etc. So, it might 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. Therefore, it is a good idea to stick to good habits. Also, if you are planning to develop SQL applications or front-end applications that will communicate with a SQL server in the back (if you are planning to get into this business, that's what you will certainly be doing), you will have to stick to one word names, it would make your life simpler. So, 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 divide employees names by first name, middle name, and last name, these are the field names you will use to make them distinct. In this case, you could name the first name field, firstname (in one word), the last name would be called lastname. Although this good technique allows you to use one word name, some people including yourself 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, use FirstName. Instead of fullname, use FullName. Instead of firstdayofmonth, use FirstDayOfMonth. Now, this looks more explicit, doesn't it?

Double-click the second field and name it CopyrightYear. Change the third field to Author. The fourth field will be ISBNNumber. The last field will be Publisher. The table as we have it now is in Datasheet view.

Saving A Table:
On the Standard toolbar, click the Save button to save the current table. A table can have any name. This time also, there are suggestions made to you. First, the name of a table should reflect the kind of data it is holding. Second, you can name a table with a few words, with spaces; but for the same reasons mentioned earlier, stick to a name of one word, without spaces. The last suggestion recommends that you use a prefix that identifies the table as such, a table, to reduce confusion. So, instead of naming your table Books, or BookTitles, save it as tblBooks. Then click OK.

The Primary Key:

1. Microsoft Access needs one more information, actually, this is a suggestion. We have already seen that a database in Microsoft Access is made of different inter-related parts. To identify these parts, mainly tables, Microsoft Access needs to have a Primary Key on each table. When you create relationships between tables in the future, Microsoft Access will need to refer to each table from its Primary Key. There are cases when you don't want or don't need a Primary Key, this time, we will use one. So, click Yes and let Microsoft Access create a Primary Key for you.
2. Next, click the (Design) View button on the Standard toolbar (it should be the most left button). Now, we have our table on a different view, namely the design view. Microsoft Access created a Primary Key and called it ID.

Renaming A Field:

1. If you create other tables and always let Microsoft Access create or set a Primary Key for you, it will call all of them, ID. Not only this name is not very explicit, but it creates confusion of its own. There are also times when you change your mind about the name given to a field. That's why you should rename that field. Double-click the name ID and type BookID. Then, press Tab. The data type of the Primary Key has been identified as an AutoNumber, which means it will hold a value set automatically by Microsoft Access. In the Description, you can write a comment that will display on the status bar when somebody, a user, is accessing a field. For this one, type Automatic number set by Microsoft Access.
2. Move to the lower part of the design table. The data type of the Primary Key will be a Long Integer with incrementing values. For its caption, type Book ID.
3. Click the BookTitle row. Its data type is text, this is the default data type that Microsoft Access sets for all newly created fields. Since a book title is really a text format, leave it that way. In its Description section, you can type Title of the book.
4. Click the lower part of the view and type Book Title for the caption

A Field's Data Type:

1. Click the CopyrightYear row on the upper part of the view. Click the combo box of its Data Type, to display its values. There are different kinds of data we can display on a table. A Text represents a group of characters of any kind, but it is limited to 255 characters. A Memo is used to write a larger text with a maximum of 64000 characters. A Number is for numeric data. You use the Date/Time to show date or time. The Currency is used to represent a currency (money) value. The AutoNumber sets numeric values automatically. The OLE Object allows you to include other kinds of files in your database, for example pictures, graphics, other applications files (Microsoft Word documents Microsoft Excel spreadsheets, etc). With the Hyperlink, you can provide a link from your document to a file in your computer or to a web page on the Internet. The Lookup Wizard specifies some predefined data on a particular column. These data are usually prepared by you. For the copyright year, choose Number; that will represent our years.
2. Click the lower part of the view. The field size has been set to Long Integer. Click the field size combo box to display its values. A Byte is used for small numbers (integers) that range from 0 to 255. The Integer (2-byte) is larger than a Byte, it goes from -32768 to -32767. The Long Integer (4-byte), the Single (4-byte), and the Double (8-byte) represent larger numbers. The Replication ID is a 16-byte Globally Unique Identifier (GUID). You use a Decimal (16-byte) when your number has a decimal value. For the year, choose Integer. Change the caption to © Year. In the Default Value, delete the 0.
3. If you don't supply a caption for a field, Microsoft Access will use the name that you gave to the field. In this case, the fields Category and Author are fine; nevertheless, describe the Author's field as: Author or Authors, Sometimes a Publisher, Like Microsoft Press.
4. Click the ISBNNumber field. Although this is a number, it includes - and numbers, but it usually doesn't cover more that 14 characters, set its field size to 16. Change its caption to ISBN #. Although it is a good idea to supply an ISBN number or just a number for each book in our record, we will not make it required. But each book is supposed to have a unique ISBN number. So, click the Indexed combo box and choose Yes (No Duplicates).

Adding A Field In Design View:

1. You can add a field in any part of the table in Datasheet View, as well as in Design View. Click the last row of the upper part of the Design View and type Comments. Set its data type to Memo.
2. To add a field inside of a table, we will add a shelf number to the table as if we were writing a program for a library. We would like the column identifying the shelf number to be between the Comments and the ISBN Number fields. To insert a column, click the column that will be ahead of it, and then click the Insert Rows button on the toolbar. In this case, click the Comments field; then on Microsoft Access main menu, click Insert ->Rows. Then type ShelfNumber. Set its data type as Text, and its Description as Number set by Management. Press F6 to move to the lower part of the design view. Set its caption to Shelf #.

Moving A Field:

1. To move a field, you select it entirely from the Row Selector, release the mouse, then click again and drag it. We will move the ShelfNumber field from where it is, to place it between the BookID and the BookTitle rows. Place the mouse on the left of the ShelfNumber field, on the Row Selector, until it becomes an arrow (pointing right), then press it once and release the mouse; this selects the whole row. Press the same Row Selector again, hold your mouse down, and drag the whole row; while you are moving, it becomes a (horizontal) line. When the line reaches and corresponds to the line between BookID and BookTitle, release the mouse.
2. On the main menu, click View -> Datasheet View to move to the datasheet view. Microsoft Access asks you to save the last changes. Click Yes to accept.

Inserting A Field In Datasheet View:
You insert and move fields in Datasheet View the same way you would in the Design View. This time, you select the column from its header. To move the Author column, click the Author header once to select it. Click it again and drag it to be between the ShelfNumber and the BookTitle.
To insert a new field, click the © Year header column once to select it. From the Microsoft Access main menu, click Insert -> Column. Now, you have a new field, change its header to Category.

Table In Design View:

1. We should still have the Books database open. Make sure that the Tables button is clicked in the Objects of the Database Window, otherwise, click it. From the Database Window toolbar, click the New button to create a new table. This time, double-click Design View. (In Microsoft Access 97, click the Tables tab in the Database Window, click the New button, and double-click Design View from the dialog).
2. When the Design View opens, in the first row, type BookCategoryID. On Microsoft Access main toolbar, click the Primary Key button to make sure the current field will be the Primary Key of this table. Set its Data Type to AutoNumber. In the lower part of the view, set its caption as Bk Cat ID. Press F6 to go to the upper part of the view.
3. Name the second row BookCategory. Set its data type to Text. Set its caption to Book Category. Index its selection as Yes (No Duplicates).
4. Save the table as Categories , then close it.

Renaming A Table:
You can delete or rename a table easily. If you right-click on it, you make your choice from the pop-up menu.
We have two tables now: Categories and tblBooks. We would like our tables (and all other components of our database) to have some harmony. Right-click on the table Categories and choose Rename. The name Categories becomes highlighted. Name it tblBookCategories, and press Enter.

Entering And Editing Data On A Table:
A table can be used to enter data, as long as you have the necessary information as specified during your design. Of course, you can change data even if it has already been typed.

Data is entered on a table by typing it in the Datasheet View. The datasheet view looks like a spreadsheet made of columns and rows. The intersection of a column and a row is where you enter data, it is called a cell. You can access a cell by clicking in it. To move from one cell to another, you can use your mouse to click in the desired cell. You can also press the Tab key on your keyboard. Your keyboard's Enter key has also been configured to move from cell to cell (in some cases). The arrow keys on your keyboard can also be used to move between cells.

When entering data in your table, each row represents a record. When you already have data in a table, you can view it back and forth by navigating in your table. For that purpose, you will use the necessary buttons at the bottom of the table.

1. Open the Books1 database. From the Database Window, click the tblBooks table and click the Open button.
2. For the Shelf #, type CS-201-CP and press Enter to move to the next column. The author is Alan R. Feuer. For the book title, type MFC Programming and press Tab on the keyboard. For the book category, type Computer - Programming. Set the © Year to 1997 and move to the next field. The ISBN # is 0-201-63358-2. The publisher is Addison Wesley. For the Comments, type Extensive overview of the Microsoft Foundation Classes libraries.. Press Enter twice.
3. Enter one more record. America A Narrative History is the title of a History book written by George Brown Tindall And David E. Shi, published in 1996 by Norton, it has the ISBN # 0-393-96874-X, and the shelf # is HC-001-AA; comment it as Fourth Edition - Volume One.