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. Practical Learning: Introducing Properties Start Microsoft Access To create a new database, click File and click Blank Database Set the name of the database to Hotel Management and click Create To save the default table, right-click the Table1 tab and click Save Set the name to Employees and press Enter 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: 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, right-click its tab and click Design View If a table is currently opened, to switch it to 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. Practical Learning: Introducing the Table's Design View To switch the table to Design View, right click the Employees tab and click 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   The Name of a Field 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: 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, use the Delete, the Backspace, and the arrow keys to edit it Practical Learning:  Creating Table's Columns in Design New Under the Field Name column header, double-click ID, type EmployeeID and press Enter Click the empty box under EmployeeID, type DateHired Press the down arrow key and type EmployeeNumber Complete the columns as follows:   EmployeeID DateHired EmployeeNumber FirstName MiddleInitial LastName MaritalStatus To save the table, press Ctrl + S 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. Text on the Status Bar 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: If the table is displaying in the Datasheet View, click any cell under its header. On the Ribbon, click Fields, in the Properties section, click Name & Caption. Click Description and type the desired text If the table is displaying in the Design View, click the name of the column. Under Description, type the string you want 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 In the top section of the table, click EmployeeID and press Tab twice In the Description field for the EmployeeID field, type Automatic number generated by the database engine Press the down arrow key. That puts the caret in the Description field for the DateHired field. Type  Date the employee was officially hired In the left section, click MiddleInitial, click the box under the Description column that corresponds to MiddleInitial, and type Middle initial made of one letter Press Ctrl + S to save the table To switch the table to Datasheet View, right-click its tab and click Datasheet View 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: In the Datasheet View of a table, click any cell under the column header. In the Properties section of the Fields tab of the Ribbon, click the Name & Caption button . This would display the Enter Fields Properties dialog box where you can make changes To s in the top section of the Design View, click the field under the Field Name column. In the lower section of the window, click Caption and type the desired word or group of words To change the caption of a column but keep its name, switch the table to Design View, under Field Name, click the field. In the lower section of the window, click Caption, press F2 and edit the string Practical Learning: Setting Objects Captions Notice the words in the columns headers (the captions) Enter the following records:   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     To switch the table to Design View, right-click the tab of the table and click Design View In the top section of the table, click EmployeeID In the lower section of the view, click Caption Type Empl ID and press Enter In the top section, click DateHired and press F6 Press the arrow keys to put the caret on Caption and type Date Hired Press F6 to up to the upper part User the arrow keys to select FirstName Press F6, select Caption and type First Name In the upper section, click LastName In the lower section, click Caption and type Last Name In the same way, set the Caption of MaritalStatus to Marital Status To save the table, press Ctrl + S Right-click the tab of the table and click Datasheet View Click a cell under EmployeeNumber On the Ribbon, click Fields In the Properties section, click the Name & Caption button In the Enter Fields Properties dialog box, click Caption and type Empl # Click Description and type A unique number assigned to an employee Click OK Click a box under MiddleInitial On the Ribbon, click Name & Caption Click Caption and type MI Click OK Right-click the column header of each column and click Field Width. Then click Best Fit The Font on the Fields of a Table   Introduction 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. 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 Employees table should still be displaying in Datasheet View. On the Ribbon, click Home if necessary. In the Text Formatting section, click the arrow of the Font combo box (the top-left combo box of the Text Formatting section) and click Garamond Press Ctrl + S to save the 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 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 Size combo box (the top-right combo box of the Text Formatting section) and click 12 Press Ctrl + S to save the 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: Style Example Regular Regular Text Bold This text is bold Italic Italicized section 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: To set a bold style, click the arrow of the Font Weight combo box and click its arrow to select one of the following options: Extra Light, Light, Normal, Medium, Semi Bold, Bold, Extra Bold, and Heavy By default, text is not italicized. This aspect it controlled by the Font Italic Boolean property whose default value is No. To italicize a text, set this property to Yes By default, the text of a new control is not underlined. This characteristic is controlled by the Font Underline Boolean property and its default value is No. If you want to underline the control or the text of the selected controls, you can set this property to Yes 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 Employees table should still be displaying in Datasheet View. In the Text Formatting section of the Home tab of the Ribbon, click the Bold button

 

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