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

1. Start Microsoft Access
2. In the middle section, click Blank Desktop Database
3. In the dialog box, change the File Name to FunDS1 (FunDS stands for Fun Department Store)
4. Click Create to create the new database file

Introduction to Tables

A table is a technique to organize a list of values in categories. Here is an example:

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.

Table Layout

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

1. On the Ribbon, click File
2. Click Options
3. On the left side, click Current Database
4. On the right side, under Document Window Options, click the Overlapping Windows radio button
5. Click OK
6. Read the message box and click OK
7. On the Ribbon, click File
8. Click Blank Desktop Database
9. Type the name of the file as Exercise1
10. Click Create

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

Overview

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

1. On the Ribbon, click Create
2. In the Tables section, click 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:

• Click File and click Save
• Right-click the Table_X (such as Table1) tab and click Save
• Press Ctrl + S
• 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)

A table can have almost any name: Employees, 2&&4DG, Pestes, verTT#@tg, etc. There are suggestions you should follow:

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

Practical Learning: Saving a Table

1. To save the table, right-click Table2 and click Save
2. Type Corporate Items as the name of the table
    
   
3. Click OK

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

Table Management

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:

• Double-click the table
• 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. 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:

• You can click the close button ,
• You can press Ctrl + Shift + F4

Practical Learning: Closing a Table

• To close the table, click its Close button .
  If there is another table (Table1), to close it, click its Close button

Selecting a Table

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

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

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

Deleting a Table

To remove a table from your database, in the Navigation Pane:

• Right-click the table and click Delete
• Click the table to select it. Then, on the Ribbon, click Home. In the Record section, click Delete
• Click the table to select it and press Delete

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

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

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:

• 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 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:

• Click or right-click the header of a column. This would bring a menu from where you can select an option

• On the table, click the header column or a cell under the column. In the Add & Delete section of the Fields tab of the Ribbon, click the type of field you want
• In the Add & Delete section of the Ribbon, click More Fields to display a list and click an option from that menu

Practical Learning: Using Fields

1. To create a new table, on the Ribbon, click Create
2. In the Tables section, click Table
   

   

3. Click Click To Add and click Short Text

   

4. When it is highlighted, type Order Date and press Enter
   
5. As the menu displays in the next column, click Short Text
6. Type Order Time and press Enter
7. As the menu appears in the next column header, click Short Text
8. Type Container and press Tab
9. Press Esc

   

10. To close the table, click its Close button
11. When asked whether you want to save it, click Yes
12. Type Customers Orders as the name of the table
13. Click OK
14. To start a new table, on the Ribbon, click Create
15. Click Table
16. In the Add & Delete section, click Short Text
17. Type Category and press Enter
18. Press Esc
19. In the Add & Delete section, click Short Text
20. Type Manufacturer and press Enter
21. Press Esc
22. In the Add & Delete section, click Short Text
23. Type Model and press Enter
24. Press Esc
25. Right-click Table1 and click Save
26. Set the name to Company Assets
27. Click OK
28. Click its Close button

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

1. In the Navigation Pane, under Tables, double-click Company Assets
2. Click the first empty field under Category and type Printer 
3. Press Enter and type HP for the Make
4. Press Tab and type LaserJet CP3525x as the model
5. Click the empty box under Acquired Date and type 10/08/2010
6. Complete the table as follows:
    

   Category	Manufacturer	Model
   Desktop Computer	Dell	Precision T3500
   Monitor	AOC	19-Inch Class
   Printer	HP	LaserJet CP3525x
   Desktop Computer	Dell	Vostro 430
   Monitor	ViewSonic	VA2231WM 22-Inch
   Server	Dell	PowerEdge T710

7. Close the Company Assets table

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:

• 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

Changing the Width of a Column

To change the width of a column:

• 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 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.
• 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 Field Width

  

  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

1. In the Navigation Pane, double-click the Customers Orders to open the table
2. To add a new field, right-click the Order Date column header and click Insert Field
3. Click the cell under Order Time
4. On the Ribbon, click Fields
5. In the Add & Delete section of the Ribbon, click Short Text

Renaming a Column

To rename a column, you must put it into edit mode. To do this:

• Double-click the name of the column in the header
• Right-click the column's name and click Rename Field

Once the name is in edit mode, type the desired name and press Enter.

Practical Learning: Renaming a Column

1. Right-click the Field1 column header and click Rename Field
2. Type Clerk and press Enter
3. Right-click Field2 and click Rename Field
4. Type Flavor and press Enter
5. Close the table
6. In the Navigation Pane, double-click (the) Company Assets (table) to open it
7. On the table, double-click Acquisition to put it into edit mode
8. Type Date Acquired and press Enter
9. On the table, right-click ID and click Rename Field
10. Press F2 and press Home
11. Type Asset and press the Space bar to get Asset ID
12. Click any cell under any column header

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:

• Right-click the column's name and click Delete Field
• Select the column (or a group of columns), right-click anywhere in the table and click Delete Field
• Select a column (or a group of columns). Then, in the Add & Delete section of the Ribbon, click the Delete button

Any of these actions would present a warning to confirm whether you still want to delete the column(s).

Fundamentals of Designing a Table

Introduction

Besides the Datasheet View, another way to create a table is by designing it. This is done in the Design View:

• To start creating a table in Design View:, on the Ribbon, click Create. In the Tables section, click the Table Design button
• To open an existing table in Design View, in the Navigation Pane, right-click it and click Design View
• If a table is already opened, to switch it to Design View
  • To switch it to Design View, right-click its tab or its title bar and click Design View
  • On the right side of the status bar, click the Design View button

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:

• Right-click the table's tab or title bar and click Datasheet View
• On the Ribbon, click Home or Design. In the Views section, click either the View button or click the down-pointing button under View and click Datasheet View
• On the status bar of Microsoft Access, click the Datasheet View button

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

1. On the Ribbon, click Create
2. In the Tables section, click Table Design

The Name of a Field

As you may know already, a column must have a name. In the Design View:

• To create a new column and give it a name, click an empty cell under Field Name and type the desired name
• To change the name of an existing column, double-click its name in the Field Name column to select it and type the desired name
• To edit the name of an existing column, click somewhere in the name to put it into edit mode, use the Delete, the Backspace, and the arrow keys to edit it

Practical Learning: Creating Table's Columns in Design New

1. If necessary, click the cell under Field Name. Type EmployeeNumber
2. Click the empty box under EmployeeNumber
3. Type DateHired
4. Press the down arrow key and type FullName
5. Close the table
6. A message box asks you whether you want to save. Click No

Introduction to Data Types

Overview

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:

• To insert a new field, right-click the field that will succeed it (you can right-click anywhere on the horizontal boxes of the column, that is, under the Field Name, under the Data Type, or under the Description columns), click Insert Rows, and type a name for the new column
• To insert a new column, click anywhere on the column that will succeed it. Then, on the Ribbon, click Design. In the Tools section of the Design tab, you can click the Insert Rows button

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

1. On the Ribbon, click File and click New
2. Click Blank Deskop Database
3. Set the file name to StatesStatistics1
4. Click Create
5. Close the default table without saving it
6. On the Ribbon, click Create
7. In the Tables section, click Table Design
8. Complete the table with the following fields
    

   Field Name

   Abbreviation

   StateName

   Capital

9. Right-click Abbreviation and click Primary Key
10. On the right side of the status bar of Microsoft Access, click the Datasheet View button
11. When asked whether you want to save, click Yes
12. Set the name of the form as States and press Enter
13. Create a few records as follows:
     

    Abbreviation	StateName	Capital
    AL	Alabama	Montgomery
    AK	Alaska	Juneau
    AZ	Arizona	Phoenix
    AR	Arkansas	Little Rock
    CA	California	Sacramento

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

• Close Microsoft Access