Microsoft Access 2010 - Lesson 09: Characteristics of Tables and Fields

Fundamentals of Designing a Table

Introduction

In our introductions to Windows controls, we learned how to position some objects on a form or a report and how to specify their sizes. These two aspects (the location and the size) are referred to as characteristics of an object. In computer programming or in application development, a property is a piece of information that describes an object. Therefore, a property can be the location (position) of an object, its size, its color, its text, or anything that characterizes it. Some properties of an object are visible and can be set by you the database developer. Some properties can be set or changed by the user when interacting with the computer (but you should be able to predict or control what the user can or should do). Some other properties can be changed by either you or the user.

As seen in the previous lessons, there are various ways you can change the properties of a control but the table, the form, or the report must be displaying in Design View. The properties of an object can be accessed from the Properties window. The table on one hand has a different way of dealing with properties than the form or report on the other hand.

When we studied the creation of tables in Lesson 2, we saw how to create columns in the Datasheet View. Like the form or the report, the table can be presented in Design View. In fact, that's probably the best place to create the columns of a table. Instead of the Datasheet View, you can start a new table in Design View or you can modify an existing table 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:

The top area is made of columns (named Field Name, Data Type, and Description) and rows. When necessary, you will be directed to type or select something in one of the cells. The name of the column under which you must type or select will be specified in the lessons.

The lower portion of the window is made of two sections. To access the lower portion while the top section has focus, you can press F6 or click the lower item you want.

The left section of the lower part is made of two tabs labeled General and Lookup. Each tab contains two columns and various rows. The number of rows and the contents of cells depend on what is selected in the upper section. In our instructions, you will be directed when to do something in a tab or in a cell. As mentioned for the Properties window, the left column of the tabs in the lower section lists the names of characteristics. You cannot change the name of a property. The right column contains the values of the characteristics. When prompted to so so, you will create a new value, modify an existing value, or select one from a combo box. In some cases, a wizard will assist you with creating an expression as the value of a property.

The right side of the bottom part of the table is made of a read-only area that describes what is selected in one of the tabs of the lower-left section.

When given an instruction, you will be dealing with items in the top or the bottom section. To work on an item, sometimes you will be asked to click it to give it focus. You can then click the desired item. Another technique you can use to move from one section to the other is to press F6.

In Lessons 2 and 3, we had various introductions on how to create tables, forms, and reports. We also learned how to name them. We learned how to populate tables with columns and how to add fields to forms or reports with Windows controls but we did not go into their details as they relate to the operation system. One of the rules to observe in application or database development is that every object must have a name.

In our introduction to tables, we saw how to create a column in the Datasheet View. In the Design View:

An object such as a column is referred to as visible if it can visually be located on the screen. A user can use a column only if it is visible. When reviewing tables, we saw that, to hide a column, you could right-click it and click Hide Column. To hide a group of columns, you can right-click any column and click Unhide Column... Then, in the Unhide Column dialog box, clear the check box of any column you want to hide.

If you hide a column and save the table, the column is still available although it is not visible at a particular time. Also, if you hide a column, it has no influence on the form or report that is based on the table. This means that a column can be hidden from a table but its corresponding control on a form or report would still show it. In fact, if you display the Field List of a form or report that has the Record Source specified, all of the columns of the table would display in the Field List, even if they are hidden on the table.

When a field receives focus, you can display a sentence on the status bar to provide some guidance or suggestion to the user. The text that displays on the status bar should be explicit enough but not too long. When creating it, make sure it can fit in the left section of the status bar, which should be long enough to explain anything.

The process of creating a status bar text depends on the control. To create a status bar text for a field when designing a table:

The string would appear when the field receives focus in the table in Datasheet ViewIf the field participates in an expression for a field of a query, the status bar text would not appear.

To create a status bar text for a field on a form, while in Design View, access the Status Bar Text property of the control and type the desired string. Only the controls that can receive focus have a Status Bar Text property. For the same reason, because no field can receive focus on a report, this property is not available for fields on a report.

When we studied tables creation, we saw how to create columns and we saw how to set the string on the header portion of a column. On a column of a table, a caption is the string that the user sees on the column header. The caption that a column displays is not necessarily the name of the column.

By default, when you create a column of a table in the Datasheet View, the string you specify for the column will represent both its name and its caption. In some cases, you will need or want to display a multi-word string as the caption of a column. This is because the caption should be as explicit and simple as possible. For example, it is better for the user to read Date of Birth than DateOfBirth, or Full Name instead of FullName.

We already saw how to specify the name of a column in the Design View of a table. To specify the caption of a column:

A font is an art effect made of designed symbols used to represent letters and other characters on a field, a control or a label. A font is designed by an artist but usually follows a specific pattern. For example, a font designed to produce symbols readable in the US English language must be designed by a set of predetermined and agreed upon symbols. These English symbols are grouped in an entity called the English alphabet. When designing such a font, the symbols created must conform to that language. Because a font is primarily an art, one font can be different from another and a font is not necessarily a series of readable symbols.

Just like everything else in the computer, a font must have a name. A font is also designed to assume different sizes. Before using it on a data field or label, the font must have been installed. Microsoft Windows installs many fonts during setup.

The font used on the Microsoft Access application to display its menu and the names of objects is controlled by the operating system. Unless you have a good reason, you should not be concerned with it.

The names of fonts installed on your computer can be seen in the Fonts window accessible from Control Panel. Here is an example:

Microsoft Access uses some default fonts to display columns and fields on tables, forms, and reports. If you want to use a different font to display text on labels and fields of your database, you can changes these defaults. When changing the default font, in other words if you decide to change the default font, you should use only the most popular fonts that are more likely to be found on your userÃ¯Â¿Â½s computers.

When designing a table, Microsoft Office Access applies a default font (named Calibri) to the headers of the columns. If you want, you can change it. To assist you with setting the fonts of columns, the Ribbon is equipped with a Text Formatting section in the Home tab.

To change the font of columns and records of a table, display the table in Datasheet View. On the Ribbon, click Home. In the Text Formatting section, click the Font combo box and select the desired font. The font you select will be applied to all columns and all values (the records) of the table. The font you apply to a table does not have any influence on the form(s) or report(s) that are, or will be, based on that table.

Besides its name, a font is also known for its size. The size defines how much height and proportional width would be used to represent the characters of the selected font.

To specify the font size of the columns, open the table in Datasheet View. On the Ribbon, click Home. In the Text Formatting section, click the arrow of the Font Size combo box and select the desired size. The font size you apply to a table does not have any influence on the controls on the form(s) or report(s) that are, or will be, based on that table.

When a font is installed, a set of font sizes is created in the Font Size combo box. You can use those numbers but you can also set a new one. To do this, instead of selecting a value in the Font Size combo box, type the desired number and press Enter or Tab. The operating system would calculate the size and apply it.

Another aspect of the appearance of a font is its style, which is a technique of drawing the characters of the text, depending on the font. This characteristic comes in four options:

The font style applies only to a control that can display text. When you add a text-based control to a form or a report, by default, Microsoft Access applies the Regular font style to it. You can change this characteristic if you want. To change the font style of a control, switch the form or report to Design View. Select the control or the group of controls. On the Ribbon, click either Home or Design. In the Text Formatting section, click the button that represents the desired style: Bold

, Italic

, or Underline

You can specify more than one style on a control. To do this, click the button of the desired style. When a style is valid for a control, when you click that control, the style button is highlighted:

, or

. To remove a style, click the undesired button. To add a style to another style, simply click the desired button. Based on this, you can have one, two or three buttons highlighted in the combination of your choice.

Instead of the buttons on the Ribbon, you can apply a style or a combination of styles using the Properties window. To do this, access the Properties window for the control. If you want to apply the style to many controls, select them and access the Properties window for the group of selected controls. In the Properties window, click either the Format or the All tab:

As reviewed for the style buttons on the Ribbon, you can apply various styles using the Properties window. To do this, access the Properties window for the control or the group of controls and use the Font Weight, the Font Italic, or the Font Underline properties to set the styles as desired.

The Colors of Fields of a Table

Introduction

The color is a non-spatial characteristic applied on a Windows control or a field to define its visual density of darkness or whiteness. This density itself is a numeric value created by combining three numbers that each ranges from 0 to 255 included. The first number is referred to as red. The second is referred to as green. The third is referred to as blue. A combination of red, green, and blue creates a color. Since each number has 256 possible values (0 to 255), if you multiply them as 256 * 256 * 256, you get 16777216. This means that a combination of variances of red, green, and blue produces more than 16 millions of possible colors.

Font Color

There are two main aspects you will deal with as related to colors on a field or a control. The most usual way of setting a color on a field is on its characters or letters. This is also referred to as the fore color, which is simply the color of its font. There are various ways you can change the font color on a table. When displaying a table in Datasheet View, on the Ribbon, you can click Home. In the Home section, click the arrow of the Font Color button. This would display a table of colors:

Font Color

By default, this window displays 2 sections of colors. The top section is labeled Theme Colors. The main section is titled Standard Colors. This is where you will mostly select common colors. This section is made of 10 columns and 7 rows. The intersection of a column and a row shows a colored button. Each color has a name. To see the name of a color, position the mouse on its button and a tool tip would appear.

When the list of colors displays, if you see a color you like, you can click its button. If none of the displayed colors suits your need, you can click the More Colors... button. This would display another dialog box. You can use it to select a color from the Standard tab:

Colors

Or you can use the Custom tab to "compose" a color by specifying the variances of red, green, and blue.

After a color has been selected, it displays on top of the Font Color button. Next time, if you need the same color, you can just click the Font Color button and the already selected color would be applied. In the same way, you can keep changing colors as you see fit. Whenever a color has been used in your database, it is added to the section under Recent Colors. This allows you to keep track of the colors on your objects and to be able to re-apply an already used color.

Practical Learning: Setting Text Font on a Table

The Employees table should still be displaying in Datasheet View.
In the Text Formatting section of the Home tab of the Ribbon, click the arrow of the Font Color button (the button that displays A) and click the More Colors button
In the Colors dialog box, click Custom
Set the color values as follows:

Red: 255

Green: 250

Blue: 135
Click OK

Managing the Grid Lines of a Table

A table in Microsoft Access appears as a classic spreadsheet with a white background and gray grid lines. If this display is not appealing, you can change it. To do this, display the table in Datasheet View. On the Ribbon, you can click Home. In the Text Formatting section, click the Gridlines button . A window would appear where you can select an option by clicking its button:

Grid Lines

If you click:

Gridlines: None, the gridlines would be removed from the table. Here is an example:
Gridlines: Vertical, only the vertical lines that divide the columns would display on the table. Here is an example:
Gridlines: Horizontal, only the horizontal lines that divide the records of the table would display. Here is an example:
Gridlines: Both, both the vertical and the horizontal lines would display. This is the default

Background Color

The tables in Microsoft Office Access display their records with alternate colors. This gives them an attractive appeal. If you do not like these default colors, you can change them.

To change one of the background colors of a table, first display it in Datasheet View and click the Home button on the Ribbon. In the Text Formatting section, you can click the arrow of the Background Color button. This would display a window of colors that has the same functionality as the one for the Font Color button:

Background Color

To change the Alternate Row Color of a table, while it is displaying in Datasheet View, click the arrow of the Alternate Row Color button. A window of color similar to the one we reviewed for the Font Color would appear:

Alternate Color

Its functionality is also the same. You can then select or compose the desired color.

Practical Learning: Setting the Background Color of a Table

The Employees table should still be displaying in Datasheet View.
In the Text Formatting section of the Home tab of the Ribbon, click the arrow of the Background Color button (the button that displays a pouring bucket) and click the Dark Blue color (Standard Colors: 9th column - 7th row)
Still in the Text Formatting section of the Ribbon, click the arrow of the Alternate Row Color button and click the Light Blue color (Standard Colors: 7th column - 7th row)
Press Ctrl + S to save the table

Text Alignment

Most values of a database are made of regular text. Some others are meant to display numeric values, currency, dates, and times. In some cases, Microsoft Access is configured to align the values to the left or the right. In some other cases, you may want to force or customize this alignment of text. On a table, you can align the values of individual columns to the left, the center, or the right.

By default, the alignment of text-based fields, including any caption on a label, is set to the left (if the user is using a computer that follows US English). To apply an alignment on a table, first display it in the Datasheet View. To specify the alignment of the values of a column, click any cell under that column. On the Ribbon, click Home. In the Text Formatting section, you can click:

The Align Text Left button (this is the default button of the section) to position the text to the left
The Center button to position the text to the center
The Align Text Right button to position the text to the right

Practical Learning: Aligning Fields Text

The Employees table should still be displaying in Datasheet View.
On the table, click MI
On the Ribbon, click Home if necessary.
In the Text Formatting section, click the Center button
On the table, click any cell under Date Hired
In the Text Formatting section of the Ribbon, click the Align Text Right button
Save the table

Fields Management in Table Design View

Introduction

So far, to get acquainted with database fields, we have used two techniques of creating a table. One of the techniques we used, the Datasheet View, can be used to perform data entry or to test some fields. To better control the columns of a table, we also learned to create the fields in the Design View of a table. After the columns have been created, you can perform some maintenance operations on them, such as deleting or moving them.

Practical Learning: Introducing Table Design

To create a new table, on the Ribbon, click Create
In the Tables section, click Table
To save the new table, right-click the Table1 tab and click Save
Set the name to Customers and click OK
Right-click Click to Add and select Text
Type Customer and press Tab
Type Brought a Car and press Tab
Type Tag Number and press Enter
To switch the table to Design View, on the right side of the status bar, click the Design View button

Renaming a Column

One of the jobs involved with database maintenance is to review columns and make sure they are explicit enough for the user. There are three issues related with a column's name. Some columns, as you will find out are involved in relationships, and you will have to be careful when deciding to change their name. The other issues are related to a column's actual name and its caption.

A column's name is mainly made of two parts from the developer's standpoint: the actual name and the caption. A column's name is stored as part of the table and is involved with any business dealing the table has to cope with. The caption simply guides the user in identifying the use of a particular column. These two items are not strictly related. For example, you could have a column whose name is SocSecNbr while its caption displays Nat Number From Gvt and this will not affect the functionality of the table. Overall, as a developer, you should pay attention to your columns' names because these are the names you will refer to when performing calculations or when creating expressions.

To rename a column in the Datasheet View of the table, use any of the techniques we learned to change a column's name. After you have typed the name, the new name will replace the old one. To rename a field in Design View, click it and type the new name.

Practical Learning: Renaming a Field

Under Field Name, double-click Customer and type Full Name
Press the up arrow key to select ID and type Customer ID
Press the down arrow key twice to select Brought a Car and type Has Car
Press Ctrl + S to save the table

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, you can 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, you can 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
To insert a new column, you can also click anywhere on the column that will succeed it, press Insert, and type the name of the new field
To add a new column at the end of the table, click the first empty field under Field Name and type the desired name

Practical Learning: Inserting New Fields in Table Design View

Right-click Has Car and click Insert Rows
Type Billing Address as the name of the new column
Press the down arrow key
Press Insert
Type Billing City and press Enter three times
Press Insert and type Billing State
Click Has Car
On the Ribbon, click Design if necessary.
In the Tools section of the Ribbon, click the Insert Rows button
Type ZIP or Postal Code and press Tab three times
Again, in the Tools section of the Ribbon, click the Insert Rows button
Type Country
Click the first empty box under Tag Number and type Requires Handcap Assistance
Press the down arrow key and type Email Address
Save the table

Selecting Columns in the Design View of a Table

In the Design View of the table, there are various types of operations you can perform for database maintenance. Operations include naming or renaming fields, moving or deleting fields, and changing various other properties. In order to perform some of the operations on a field, you must first select it. If the desired operation involves a group of fields, you must first select them:

To select one field, you can position the mouse on the box to its left until the cursor turns into a right-pointing arrow:

Then click
To select more than one column, you can click and hold your mouse on one of the left boxes, then drag up or down to cover the other desired field names:

When all desired field names are highlighted, release the mouse
To select more than one column, you can also click one column that will be at one end, press and hold Shift, and then click the column that will be at the other end
To select columns at random, click the left box of one of them, press and hold Ctrl, then click the box of each of the desired boxes
To select all columns of a table, click the button of the left of Field Name

Moving Columns in the Design View of a Table

The columns of a table are displayed in the sequence in which they were created. In Lesson 2, we learned how to move columns and change their sequence in Datasheet View. You can also change the sequence of fields in the Design View of a table.

To move a column, click the box on the left side of its name and release the mouse. Click that button again and hold your mouse down. Then drag up or down. A horizontal line will guide you. Once it gets to the desired location, release the mouse
To move a group of columns, first select them and release the mouse. Click and hold the mouse on the left box of one of the selected columns. With the mouse down, drag up or down, referring to a horizontal line as a guide. When the horizontal line gets to the desired location, release the mouse

Practical Learning: Maintaining Fields in Table Design View

The Customers table of the Hotel Management1 database should still be opened in Design View.
Click the button on the left side of Requires Handcap Assistance
Click that button again but hold your mouse down
Drag it up until the guiding horizontal line is positioned under Customer ID
Release the mouse
Save the table

Deleting Columns

When in Design View, you can delete a field you don't need anymore or if it was added by mistake. To delete a column:

Right-click anywhere on its line (on the Field Name, the Data Type, or the Description columns) and click Delete Rows
When a column has focus, on the Ribbon, you can click Design. In the Tools section, you can click Delete Rows button
You can select the column by clicking the box on the left side of its name and press Delete

To delete a group of columns, first select them, then:

You can press Delete
You can right-click one of the selected items and click Delete Rows
In the Tools section of the Design tab of the Ribbon, you can click the Delete button

Practical Learning: Deleting a Field in Table Design View

To get rid of a column, right-click Requires Handcap Assistance and click Delete Rows
Click Email Address
On the Ribbon, if necessary, click Design.
In the Tools section, click the Delete Rows button
Save the table

Lesson Summary

Exercises

Yugo National Bank

Open the Yugo National Bank
Using the Table button of the Creation section of the Ribbon, start a new and change Add New Field to ChargeReason
Rename the ID column to ChargeReasonID
Save the table as ChargeReasons and close it

US Senate

Open the US Senate1 database
Using the Table button of the Creation section of the Ribbon, start a new and change Add New Field to SenatorName
In the same way, add two more columns named SeatingStatus and Gender
Rename the ID column to SenatorID
Save the table as Senators and close it

DateHired	EmployeeNumber	FirstName	MiddleInitial	LastName	MaritalStatus
08/12/2008	297-497	Arlene	P	Bonds	Married
10/25/2010	406-153	Jeremy	H	Kaminski
05/06/2010	927-597	Thomas		Jacobson	Single
07/10/2010	759-027	Christie	A	Aronson	Married
07/10/2006	394-759	Carlton		Huge	Divorced
02/22/2004	927-406	Patricia	D	Thorn	Single
03/02/2009	294-759	Justine	S	Garland

Style	Example
Regular	Regular Text
Bold	This text is bold
Italic	Italicized section
Underline	The words are underlined