| What is a table? | | | | Relational databases are constituted by one or more tables than contain the information in an organized form. Databases usually contain more than one table and each one will have a fixed number of fields. | | | | The tables are made up by two structures: | | |
 | Field: It corresponds to the name of the column. It must be unique and besides to have a data type associate. |
| | Record: It corresponds to each row that composes the table. There the data and the registries are composed. |
| Each table in your database should hold the information on one subject. You might think of a subject as a collection of related information with common characteristics. For example, a Book table will hold information about books like: Title, Author, pages quantity and so on. |
 | The name of each table must be unique in the database. |
| | Each field name must be unique within a table. |
| | Table names should normally be plural. |
| | Table and field names should be as brief as possible but also should clearly identify the subject of the table or the data in the field. |
| | Avoid abbreviations and acronyms if you can as they can be cryptic to another user of the database. |
| | Some designers prefer to follow a naming convention that includes a tag that identifies each object. |
| When setting up table´s fields, you will find the follows available data types: |
| Text | Alphanumeric characters. | Up to 255 characters. |
| Use for text, or text and numbers that are not used in calculations (for example, a product ID). | |
| Memo | Alphanumeric characters (longer than 255 characters in length) or text with rich text formatting. | Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control. |
| Use for text greater than 255 characters in length, or for text that uses rich text formatting. Notes, lengthy descriptions, and paragraphs with formatting such as bold or italics are good examples of where you would use a Memo field. | |
| Number | Numeric values (integers or fractional values). | 1, 2, 4, or 8 bytes, or 16 bytes when used for replication ID. |
| Use for storing numbers to be used in calculations, except for monetary values (use the Currency for data type for monetary values). | |
| Date/Time | Dates and times. | 8 bytes. |
| Use for storing date/time values. Note that each value stored includes both a date component and a time component. | |
| Currency | Monetary values. | 8 bytes. |
| Use for storing monetary values (currency). | |
| AutoNumber | A unique numeric value that Office Access 2007 automatically inserts when a record is added. | 4 bytes or 16 bytes when used for replication ID. |
| Use for generating unique values that can be used as a primary key. Note that AutoNumber fields can be incremented. | |
| sequentially, by a specified increment, or chosen randomly. | |
| Yes/No | Boolean values. | 1 bit (8 bits = 1 byte). |
| Use for True/False fields that can hold one of two possible values: Yes/No or True/False, for example. | |
| OLE Object | OLE objects or other binary data. | Up to 1 gigabyte. |
| Use for storing OLE objects from other Microsoft Windows applications. | |
| Attachment | Pictures, Images, Binary files, Office files. | For compressed attachments, 2 gigabytes. For uncompressed attachments, approximately 700k, depending on the degree to which the attachment can be compressed. |
| This is the preferred data type for storing digital images and any type of binary file. | |
| Hyperlink | Hyperlinks. | Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control. |
| Use for storing hyperlinks to provide single-click access to Web pages through a URL (Uniform Resource Locator) or files through a name in UNC (universal naming convention) format. You can also link to Access objects stored in a database. | |
| Lookup Wizard | Not actually a data type; instead, this invokes the Lookup Wizard. | Table or query based: The size of the bound column. |
| Use to start the Lookup Wizard so you can create a field that uses a combo box to look up a value in another table, query or list of values. | Value based: The size of the Text field used to store the value. |
|
| |
| Steps to create a table from scratch |
| |
| 1) Start Access from the Start menu or from a shortcut. |
| |
| 2) The Getting Started screen will appear. |
| |
| 3) Create a database TutorialDB as showed in below image. |
| |
  |
| Illustration 1: Office Create blank database named TutorialDB. Click here to view larger image. |
| |
| 4) Select the Create tab from the upper tab strip. |
| |
 |
| Illustration 2: Click on the Create tab. |
| |
| 5) Select the Table Design icon. |
| |
| This option will display the following work area: |
| |
 |
| Illustration 3: Working area for defining tables. Click here to view larger image. |
| |
| • At the right panel, all existing tables will appears. |
| |
| • At the left panel, the table field¡¯s datasheet appears, with the pointer positioned at the first row, ready to create a new field. |
| |
| • At bottom panel, Fields Properties panel allows the field¡¯s properties set up. |
| |
| 6) Fill all required field name´s for the new table, as follows. |
| |
 |
| Illustration 4: Create a field. Click here to view larger image. |
| |
| 7) Select the Data Type list and select the right data type for the field. |
| |
 |
| Illustration 5: Select the data type. Click here to view larger image. |
| |
| 8) Complete all required fields at the sheet bellow the field definition row. |
| |
 |
| Illustration 6: Fill the description fields with the appropriate and more descriptive information. Click here to view larger image. |
| |
| 9) Fill the Fields properties sheets. |
| |
 |
| Illustration 7: Fill the field¡¯s properties with the most adequate definition. Click here to view larger image. |
| |
 |
| Illustration 8: BooId field properties. Click here to view larger image. |
| |
| 10) Create all required fields for the table. Select the format by opening the Format list. |
| |
 |
| Illustration 9: Create all the fields. Click here to view larger image. |
| |
| 11) If the data will be unique for each field (usually the primary key candidate), set the Indexed option to yes (no duplicates). |
| |
 |
| Illustration 10: The Yes (No duplicates) option permit the primary key definition. Click here to view larger image. |
| |
| 12) Also, if the field cannot be null, set the Required option to Yes. |
| |
| 13) Once you completed all required fields, save the table by clicking the table name tab. |
| |
| 14) Right click to display the context menu and Click on the Save menu item. |
| |
 |
| Illustration 11: Save the table. Click here to view larger image. |
| |
| 15) Assign the table name and press on the OK button. |
| |
 |
| Illustration 12: Book table will be saved. Click here to view larger image. |
| |
| 16) Once saved, select the field that will act as the table primary key and press the Primary Key button on the Options Tape. |
| |
 |
| Illustration 13: Create the Primary Key over the field declared as auto number and for indexing without duplicates. Click here to view larger image. |
| |
| 17) Save the table. |
| |
| 18) Now, we are ready to fill create indexes. Press the Indexes button at the toolbar. |
| |
 |
| Illustration 14: Open the Indexes Window. |
| |
| 19) The Indexes window will be displayed. |
| |
 |
| Illustration 15: Index creation and / or modification. |
| |
| 20) As you see, when you create the primary key, an index was created for the table with the name PrimaryKey. Note that this index is primary and unique. |
| |
| 21) Now, click on the Books table name at the left pane. Right click to display the context menu and select the Open menu item. |
| |
 |
| Illustration 16: Open table for Data visualization and / or input. |
| |
| 22) This option will open the table in the Datasheet view and will allow you to fill the table with data. |
| |
 |
| Illustration 17: New rows insertion. Click here to view larger image. |
| |
| 23) Fill the table with some test data. |
| |
 |
| Illustration 18: Table with test data. Click here to view larger image. |
| |
| Note that the bookId field that we define as autonumer data type is auto filled when typing the book name. |
| |
| 24) Now, click on the Books table name at the left pane. Right click to display the context menu and select the Table Properties menu item. |
| |
 |
| Illustration 19: Table properties modification. |
| |
| 25) The table properties window appears. Complete the Description field with some text and press the OK button. |
| |
 |
| Illustration 20: Table properties modification. |
discuss this topic to forum