Characteristics of Tables and Fields
Fundamentals of Designing a Table
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.
Practical Learning: Introducing Properties
The Design View of a Table
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.
Practical Learning: Introducing the Table's Design View
A Table in Design 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.
Introduction to Tables Columns Design
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:
Practical Learning: Creating Table's Columns in Design New
The Visibility of a Column
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.
Practical Learning: Setting Status Bar Text for Fields
The Caption of a Field
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:
Practical Learning: Setting Objects Captions
The Font on the Fields of a Table
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
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.
The Fonts of Columns of a Table
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.
Practical Learning: Setting the Font of a Table
The Size of a Font
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.
Practical Learning: Setting the Font Size of a Table
The Style of a Font
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:
|Bold||This text is bold|
|Underline||The words are underlined|
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.
Practical Learning: Setting the Font Size of a Table
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.
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:
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:
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
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:
If you click:
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:
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:
Its functionality is also the same. You can then select or compose the desired color.
Practical Learning: Setting the Background Color of a Table
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:
Practical Learning: Aligning Fields Text
Fields Management in Table Design View
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
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
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:
Practical Learning: Inserting New Fields in Table Design View
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:
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.
Practical Learning: Maintaining Fields in Table Design View
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:
To delete a group of columns, first select them, then:
Practical Learning: Deleting a Field in Table Design View
Yugo National Bank