Introduction to Tables and Data
Introduction to Data Fields
A Database From Scratch
Creating a database from scratch consists of adding the necessary objects one at a time. To create a database from scratch, start Microsoft Access and click Blank Desktop Database.
Practical Learning: Creating a Blank Database
Introduction to Tables
A table is a technique to organize a list of values in categories. Here is an example:
|Name||Email Address||Phone Number||Relationship|
The categories are organized vertically, as columns. They are also called fields. A field is an object used to host, hold, or store a piece of information of a database. The fields of a table are used to store data.
The values in each set display horizontally, each value in a column. A set of values in a horizontal arrangement is called a record:
The group of records of a table is called a record set.
The Table's Tab
A table is a rectangular:
As an option, you can remove the tabs. To do this, click File and click Options. In the left frame, click Current Database. In the right frame, 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. 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.
The Table System Buttons
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 File category. In the left frame, click Current Database. Under Document Window Options in the right frame, click the Overlapping Windows radio button:
When you click OK, you will be asked to close and reopen the database (which you should do).
Practical Learning: Applying Overlapped Objects
A Table as a Datasheet
A table is primarily 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.
Introduction to Table Creation
To create a table, on the Ribbon, click Create. In the Tables section, click Table . This would cause the Ribbon to switch to a section labeled Fields:
From the Fields tab, you can make selections.
Practical Learning: Creating a Table
The Name of a Table
A table must have a name. 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:
A table can have almost any name: Employees, 2&&4DG, Pestes, verTT#@tg, etc. There are suggestions you should follow:
Practical Learning: Saving a Table
Creating a Table in SQL
As a computer language, the SQL is equipped to perform all basic and necessary operations of a database. As such, it can be used to create a table. In the SQL, to create a table, you start your statement with the CREATE TABLE expression followed by the desired name of the table as follows:
CREATE TABLE table-name
The Tables in the Navigation Pane
When you create a table and save it by giving it a name, Microsoft Access creates a section labeled Tables in the Navigation Pane and displays the name of thew new table in that section. In the same way, you can have as many tables as possible in the Tables section. Here is an example:
You can create other object and they will have their sections. Each section is expandable and collapsible. To expand a section, click the header of that section. To the same to collapse a section.
The Properties of a Table
To let you get some information about a table, Microsoft Access provides a dialog box that allows you to know the name of the table you are accessing as well as the date and time when it was created or modified.
To display the Table Properties dialog box, right-click the table in the Navigation Pane and click Table Properties. Here is an example:
Opening a Table
To open a table in Datasheet View, first locate it in the Navigation Pane then:
Any of these actions causes the table to display in Datasheet View in the central area of the screen. When a table is displayinig in Datasheet View, in the Views section of the Ribbon, the View button displays the Design View button .
Closing a Table
After using a table, you can close it. To close a table:
Practical Learning: Closing a Table
Selecting a Table
In order to use a table, some operations require that you (or rather the user) first select(s) it:
Renaming a Table
To rename a table, in the Navigation Pane, right-click the name of the table and click Rename.
Practical Learning: Renaming a Table
Deleting a Table
To remove a table from your database, in the Navigation Pane:
In each case, you will receive a warning to confirm. Therefore, before deleting a table, make sure you really want to get rid of it. When in doubt, do not delete it.
Practical Learning: Deleting a Table
Introduction to the Columns of a Table
Creating a Column in the Datasheet View
A table does not exist without a 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. You can give meaningful names to the columns of your table.
Naming a Column
A column can have almost any name. To name a column:
Any of these actions would put the name of the column into edit mode. You can then type the new desired name or change the existing name.
Using a Sample Field
When you start a table in the Datasheet View, the Ribbon becomes equipped with a new tab labeled Fields. The sample fields and their configurations are distributed in various sections. One of the sections of the Fields tab is named Add & Delete:
Microsoft Access provides many pre-configured fields you can add to your table. These ready-made sample columns are referred to as a Field Templates. To use a field template, while the table is displaying in Datasheet View:
Practical Learning: Using Fields
Introduction to the Fields of a Table in SQL
As you have probably seen by now, every table must have at least one field (or column). In the SQL, the list of columns of a table starts with an opening parenthesis "(", ends with a closing parenthesis and an optional semi-colon ");". If the table will be made of more than one column, you can separate them with commas. The formula to follow is:
CREATE TABLE table-name (column1, column2, column_n);
To make the statement easier to read, and because some columns can be long, you can create each on its own line. The syntax would become:
CREATE TABLE table-name ( column1, column2, column_n )
To create a column, you specify its name, followed by its data type, and some possible options. Therefore, the syntax of creating a column is:
column-name data-type, options
The name of a column can be in one or many words. If you put space after the first word, the database engine would treat the next word as a data type. For example, the following statement would produce an error:
CREATE TABLE Employees ( Last Name )
If you want to use space in a column name, include it between an opening square bracket "[" and a closing square bracket "]". The above statement would be changed to:
CREATE TABLE Employees ( [Last Name] )
After the name of the column, you must provide a data type. The most fundamental data type in the SQL is named Text (remember that the SQL is not case-sensitive; this means that TEXT, Text, and text are valid). Here is an example of creating a table with four text-based fields:
CREATE TABLE Employees ( EmployeeNumber TEXT, FirstName Text, LastName text, HourlySalary TExt );
To actually create the table, you must execute the code.
Introduction to Records
Data Entry Fundamentals
A table's cell holds one particular unit of data and all cells on a range belong to the same record:
Data Entry on a Table
To perform data entry on a table, you can click a cell under a column header and type a value:
In some cases, the field of the most left column is marked with (New):
There are three kinds of fields or cells the user will face: a field in which the user can type data, a field that displays a list as a combo box the user has to select from, and a field that does not receive input from the user. After setting the data in a particular field, you can click another cell and type the desired data.
Practical Learning: Performing Data Entry on a Table
|Desktop Computer||Dell||Precision T3500|
|Desktop Computer||Dell||Vostro 430|
SQL and Data Entry
In the SQL, data entry is performed using the INSERT INTO expression, followed by the table name, followed by the VALUES keyword, and followed by the values in parentheses. If the table is made of only one column, you can include the desired value in the parentheses. If the table is made of more than one column, you can separate the values with commas. The syntax to use is:
INSERT INTO table-name VALUES(column1, column2, column_n)
The table-name attribute must be the name of an existing table of the current database. If the name is wrong, the SQL would consider that the table you are referring to does not exist. Consequently, you would receive an error. The VALUES keyword indicates that you are ready to list the values of each field of the record. The values of the columns must be included in parentheses.
In the above syntax, the value of each field of the column must be entered in the exact order of the columns as they were created in the table. If the data type of a column is a text type, include its value between either single or double quotes. Here is an example:
INSERT INTO Employees VALUES('947085', 'Larry', 'Harrington', '24.75');
When you execute the code, you will receive a message that you are about to add a record. Read it and click Yes.
Columns Maintenance in the Datasheet View
Selecting a Column
Some operations will require that you select a column but it depends on the operation you want to perform. 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:
Changing the Width of a Column
To change the width of a column:
If you double-click, the column would be resized 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 Field Width would be widened enough to display the name of the column.
This would open the Field Width dialog box where you can type the desired value and click OK
Best Fitting the header of a Column
Instead of manually resizing a column, you can ask Microsoft Access to take care of that. To do that, right-click a column header and click Field Width. In the Column Width dialog box, click Best Fit.
Moving a Column
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
To insert a column, right-click the column that will succeed it and click Insert Field
Practical Learning: Inserting a Column
Renaming a Column
To rename a column, you must put it into edit mode. To do this:
Once the name is in edit mode, type the desired name and press Enter.
Practical Learning: Renaming a Column
The Visibility of a Column
A column can be hidden from a table so the user would not see it. To hide a column, you can drag the right border of its column header completely to its left border. 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 Fields.
Revealing a Hidden Column
You can right-click any column and click Unhide Fields. 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.
Deleting a Column
To remove a column from a table:
Any of these actions would present a warning to confirm whether you still want to delete the column(s).
Fundamentals of Designing a Table
Besides the Datasheet View, another way to create a table is by designing it. This is done in the Design View:
Mostly you, the database developer, have access to the Design View of a table. The user will hardly, if ever, use that view.
A table in Design View is divided in two sections: one in the upper area and another in the bottom:
To work on in a section, using the mouse, you can just click. To switch from one section to another, press F6.
If a table is currently opened in Design View, you can change its view. To switch a table from Design View to Datasheet View:
The Property Sheet of a Table
When designing a table, Microsoft Access provides a window that allows you to specify or manage some characteristics of a table. This is the role of the table Property Sheet:
To display the Property Sheet of the table, right-click any part of the table in Design View and click Properties.
Practical Learning: Starting a Table in Design New
The Name of a Field
As you may know already, a column must have a name. In the Design View:
Practical Learning: Creating Table's Columns in Design New
Introduction to Data Types
A data type represents the kind of information that a particular field should, would, or must hold. Microsoft Access provides all the types or categories of values necessary for a database.
Setting the Data Type of a Field
To make your database efficient, in some circumstances, or depending on the project (or customer), you should exercise as much control as possible on data entry. This aspect is mostly controlled at two levels: tables and forms.
We know that, when creating a table in Datasheet View, to create a column, you can click Click to Add, and select a field type from the list:
Type a name for the column. After creating a column, if you want to change it, on the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select from the list.
Besides the Datasheet View, you can use the Design View of a table to specify the type of value that a field can hold. To do this, after specifying the column name, click the arrow of the corresponding box in the Data Type column and select the desired type:
Data Type Properties
To further control how data is entered in the database, you can configure a field's characteristics.
To control the characteristics of a field in the Datasheet View of a table, click any field under the column header. On the Ribbon, click Fields. Use the options in the Properties, the Formatting, and the Field Validation tabs:
To control the properties of a table in the Design View, after selecting a data type in the Data Type column, in the lower section of the table, you can format or further configure the field. The lower part of the table Design View is made of two sections: the property pages on the lower left and the properties help section on the lower right:
The kind of Data Type you set for a field in the upper section controls what displays in the lower section of the view. The General tab controls the features of the selected data type. The options in the General property page depend on the data type that was specified:
Fields Management in Table Design View
Renaming a Column
To rename a field in Design View, click it and type the new name.
Inserting a Column
In a table's Design View, you can insert a new field at any position. You can also add a new field to the end of the table:
Moving Columns in the Design View of a Table
The columns of a table can be moved to have a different sequence of fields in the Design View of a table. To do this, click the button on the left side of the column. Click that button again but hold your mouse down. Drag it up or down until the guiding horizontal line is positioned where you want:
Release the mouse
Deleting a Column
You can delete a column or a group of columns of a table in Design View. To get rid of a column, right-click the button on its left and click Delete Rows.
Practical Learning: Maintaining Tables
Table and Columns/Fields Maintenance in SQL
Adding a Column
The formula to add a column or field to a table is:
ALTER TABLE table-name ADD COLUMN column-name options
Here is an example:
ALTER TABLE Employees ADD COLUMN EmploymentStatus text
Changing a Data Type
The formula to change a column is:
ALTER TABLE table-name ALTER COLUMN column-name options
Here is an example:
ALTER TABLE Employees ALTER COLUMN EmploymentStatus Integer
Deleting a Column
In SQL, to delete a column, the formula to follow is:
ALTER TABLE table-name DROP COLUMN column-name;
The ALTER TABLE and the DROP COLUMN expressions are required. The table-name is the name of the table that holds the column you want to delete. The column-name is the name of the column you want to remove from the table.
Delete a Table
In the SQL, to delete a table, use the DROP TABLE command. The formula to follow is:
DROP TABLE table-name;
The table-name parameter must be a valid name of a table of the current database. There are two significant issues with the DROP TABLE command: it does not warn you and it is not reversible.
Hee is an example:
DROP TABLE Customers;
Practical Learning: Ending the Lesson