Access change list box to combo box

	Combo and List Boxes

Bound Combo Boxes

Introduction

A combo box is a Windows control that holds a list of text items. The user can select one item from the list. Microsoft Access supports bound and unbound combo boxes. The application also provides the tools to help you create and configure a combo box using either the Lookup Wizard or the Combo Box Wizard.

A Bound Combo Box

A combo box is said to be bound if its values come from a field of a table. Such a field must be the foreign key that represents the records of a related table.

The easiest way to create a bound combo box is to use a wizard because you would let Microsoft Access configure everything behind-the-scenes after you have just made some selections in the wizard. The combo box can be created in a table where it is referred to as a lookup field, or in a form where it is a formal combo box. If a lookup field is created in a table, it can be added to a form or report where it would be created as a combo box. If only a value was provided for the foreign key of a table, then you must formally create a combo box for it.

Practical Learning: Introducing Combo Boxes

1. Start Microsoft Access
2. In the list of files, click Ceil Inn1
3. In the Navigation Pane, double-click the Occupancies form
4. After viewing the form, in the Views section of the Ribbon, click the button under View and click Design View
5. In the Tools section of the Ribbon, click Add Existing Fields
6. In the Field list, drag EmployeeNumber and drop it on the form.
   Notice that the control is a text box
7. Press Delete to remove the new text box
8. In the Controls section of the Ribbon, click the Combo Box and click in the Detail section of the form
9.  In the first page of the wizard, make sure the first radio button is selected. Read it and click Next:

10. In the second page of the wizard, click Table: Employees and click Next

11. In the third page of the wizard, double-click FirstName

12. Click Next
13. Accept the step and click Next

14. Click the arrow of the combo box and select EmployeeNumber

15. Click Next
16. In the text box, type Processed By:

17. Click Finish
18. On the Ribbon, click Design if necessary.
    In in the Tools section, click Add Existing Fields
19. In the Field List, drag DateOccupied and drop it on the form

20. Close the form
21. When asked whether you want to save, click Yes
22. In the Navigation Pane, double-click the Occupancies table to open it
23. Click a cell under Processed For and notice that it is a simple text box
24. In the Views section of the Ribbon, click the View button [or click under it and click Design View]
25. Change the data type of AccountNumber to Lookup Wizard
26. In the first page of the wizard, make sure the first radio button is selected. Read it and click Next
27. In the second page of the wizard, make sure Table: Customers is selected [otherwise, select it] and click Next
28. In the third page of the wizard, double-click FirstName and click Next
29. Accept the step and click Next
30. Accept the label as AccountNumber and click Finish
31. When asked to save the table, click Yes
32. When a message box warns you that some data may be lost, click Yes
33. Close the table
34. In the Navigation Pane, right-click the Occupancies form and click Design View
35. In the Tools section of the Ribbon, click Add Existing Fields
36. In the Field list, drag AccountNumber and drop it on the form,
    Notice that the control is a combo box
37. Preview the form and save it

The Characteristics of a Lookup Field/Combo Box

In some cases, if you create your lookup field of combo box using the wizard, some factors may not produce the expected result. Fortunately, you can modify the configuration of the control. Many options are available.

The configurations of the lookup field can be found in the Lookup section of the bottom part of the table in Design View. The configuration of a combo box of a form can be found in the Data or the All tab of the Property Sheet.

The available characteristics or properties are:

• Display Control: This is only available in a table, not on a form or report. This property allows you to specify how the field would display its value. The default is the combo box, which is suitable for most scenarios
• Row Source Type: This property specifies the type of list that contains the actual values. The default is Table/Query, which indicates that the values are stored in a table or a query. One of the options is Field List, which is mostly available if the list is programmatically created
• Row Source: This is the list of values. There are many techniques you can use to create that list. To build the list, you can right-click the field and click Build. If you know the expression to apply, right-click the field and click Zoom
• Bound Column: This is a number that specifies the number of columns that will connect with the primary key. The default value is 1. This should be the number of columns used in the primary key
• Column Count: This is a natural number that specifies the number of columns that will appear when the user clicks the arrow of the combo box on the field. If you select only one column from the Available Fields list of the second page of the wizard, this property would have a value of 1. Otherwise, if you want more columns, set this property accordingly
• Column Heads: This property specifies whether the top section of the list would have a caption
• Field Widths or Column Widths: The Field Widths appears in a form. The Column Widths appears in a form or a report.
  This property is made of one or more sections separated by semi-colons. Each section contains a number that specifies the width of the corresponding column of the lookup field when the list displays. In most cases, the first column, and that represents the foreign key, is not presented to the user and therefore should be set to 0. Each one of the other sections shows the desired width of the column and the columns can have different widths
• List Rows: This characteristic specifies the number of records that will be visible when the list appears. You should use a value between 4 and 16 [the default]. A value higher than 16 is usually too long. Most programming environments [in fact as set in the Microsoft Windows operating system] use 8
• List Width: This value is the total width of the list when it comes up. This should be the sum of numbers from the Field Widths property

Practical Learning: Configuring a Combo Box

1. In the Navigation Pane, right-click the Occupancies form and click Design View
2. In the top section of the form, double-click the EmployeeNumber combo box
3. In the Property Sheet, click the All tab
4. Click Name and type EmployeeNumber and make sure Column Count is set to 2
5. Click Column Width and type 0; 1.65
   Make sure Column Heads is set to No and list Rows is set to 16
6. Click List Width and type 1.65
7. Right-click Row Source and click Build...
8. In the bottom section of the window, replace LastName with FirstName & " " & LastName
9. Close the window
10. When asked whether you want to save, click Yes
11. Close the form
12. When asked whether you want to save, click Yes
13. In the Navigation Pane, right-click the Occupancies table and click Design View
14. In the top section of the table, click AccountNumber
15. In the bottom section of the window, click the Lookup tab
16. Right-click Row Source and click Build...
17. In the bottom section of the window, replace FirstName with FirstName & " " & LastName
18. Close the window
19. When asked whether you want to save, click Yes
20. Click Column Width and type 0; 1.35
21. Click List Width and Type 1.35
22. Switch the table to Datasheet View
23. When asked whether you want to save, click Yes
24. Close the table
25. In the Navigation Pane, right-click the Occupancies form and click Design View
26. On the form, click the AccountNumber combo box and press Delete
27. In the Tools section of the Ribbon, click Add Existing Fields
28. From the Field list, drag each field that is not on the form and drop it on the form
29. Complete the design of the form. Here is an example:

30. Switch the form to Form View
31. Close the form
32. When asked whether you want to save, click Yes

An Unbound Lookup Field/Combo Box

Introduction

A combo box [or a lookup field] is said to be unbound if it holds its own list of values instead of the values comming from a column of a table. Microsoft Access provides the means of creating the controls and adding the values. As mentioned for the bound combo box [or a lookup field], you can manually create and configure the control or you can use a wizard.

To easily add a lookup field to a table, use the Lookup Wizard. To easily add a combo box to form, use the Combo Box Wizard. In both cases, in the first page of the wizrd, click the second radio button. If you had created a lookup field in a table, you can drag its item from the Field List to a form or report, in which case it would become a combo box, Of course, the control would receive the same name as the table's column. If you create a combo box on form using the Combo Box Wizard, the control would receive a name that starts with Combo. An example would be Combo21.

Practical Learning: Introducing Unbound Lookup Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click Altair Realtors1 from the previous lesson
3. In the Navigation Pane, right-click the Properties table and click Design View
4. Under Field Name, right-click FinishedBasement and click Insert Rows
5. Type PropertyType and press Tab
6. Change the data type of Property Type to Lookup Wizard
7. In the first page of the wizard, read the second radio button and click it:

8. Click Next
9. In the second page of the wizard, under Col1, type Townhouse and press the down arrow key
10. Type Single Family and press the down arrow key
11. Type Condominium

12. Click Next
13. Accept the label as PropertyType and click Finish

14. In the bottom section of the window, click Caption and type Property Type

The Characteristics of a Lookup Field/Combo Box

As mentioned previously, after creating a lookup field or a combo box using the wizard, you can customize it. In fact, you cab configure the field or the combo box by setting its properties. The configurations of a lookup field are done in the Lookup section of a table in Design View. The configuration of a combo box of a form are done in the Property Sheet. The properties are:

• Display Control: This is the same as mentioned previously
• Row Source Type: For an unbound lookup field or combo box, this property should be set to Value List
• Row Source: This is the list of values. The list is made of sections separated by semi-colons. Each section contains a value [you can optionally set the value in double-quotes]
• Field Widths or Column Widths: This is the total widths of the individual columns

Practical Learning: Controlling an Unbound Lookup Field

1. Under Field Name, right-click FinishedBasement and click Insert Rows
2. Type Condition and press F6
3. In the bottom section of the window, click Lookup
4. Change the following characteristics:
   Display Control: Combo Box
   Row Source Type: Value List
   Row Source Type: Excellent;Good Shape;Needs Repair;Unknown
   
5. To switch the table, in the Design tab of the Ribbon, in the Views section, click the View button
6. When asked whether you want to save, click Yes
7. Update the following records:
    

   Property #	Property Type	Condition
   524880	Single Family	Good Shape
   688364	Single Family	Excellent
   611464	Single Family	Good Shape
   749562	Townhouse	Good Shape
   427115	Single Family
   200417	Condominium	Excellent
   927474	Townhouse	Needs Repair
   682630	Single Family	Good Shape
   288540	Condominium	Good Shape

8. Close the table
9. In the Navigation Pane, right-click the Properties form and click Design View
10. On the Ribbon, in the Tools section of the Design tab, click Add Existing Fields
11. From the Field List, drag PropertyType and Condition, and drop them on the form
12. Complete the design of the form as you see fit. Here is an example:

13. Close the form
14. When asked whether you want to save, click Yes

Combo Box Events

When using a combo box, depending on the characteristics of the control, if the user types a value that is not in the list, the control would fire an event named On Not List. You can use this event to display a message to the user and to take an appropriate action.

Page 2

The Web Browser

Introduction

A web browser is a control that shows a file. The file can be a regular picture or a web address that is identified as a Uniform Resource Locator [URL]. A web browser is very flexible with the type of document it can show. Still, you must follow some rules to prepare the document.

The primary type of document intended for a browser is a webpage. You probably already know how to create such a document. A browser can also be asked to display a picture. The web browser of Microsoft Windows supports various or all types of pictures, including those with the extensions .bmp, .jpg, .jpeg, .gif, .png, etc.

A web browser can be added only to a form. To get it, after displaying the form in Design View, in the Controls section of the Design tab of the Ribbon, click the Web Browser Control and click the form.

Practical Learning: Introducing the Web Browser

1. On the Ribbon, click File and click Open
2. In the list of files, click StatesStatistics1
3. In the Navigation Pane, right-click the States form and click Design View
4. On the form, click the State's Website tab [you may have to click it twice until the orange rectangle appears] to select it
5. In the Controls section of the Ribbon, click the Web Browser Control
6. Click inside the orange rectangle in the tab on the form.
   If a wizard starts, click Cancel
7. Enlarge and heighten the control to occupy most of the tab page

8. On the form, click the Wikipedia tab to select it
9. In the Controls section of the Ribbon, click the Web Browser Control and click inside the orange rectangle in the tab on the form.
   If a wizard starts, click Cancel
10. Apply the same location and the same size as the other browser

Characteristics of a Web Browser

Probably the most important detail of a web browser is the document it displays. This is specified by the Record Source property. You cannot create a web browser on a table. Instead, you can create a text-based field, then use it as the record source of a browser. When performing data entry, enter the name of the file with extension, the complete path and name of the file, or the URL of the web page that the browser would display.

Practical Learning: Using a Web Browser

1. On the form, click the State's Websize tab and click the WebBrowser on it
2. In the Property Sheet, click Data.
   Click Control Source and set its value to StateWebsite
3. On the form, click the Wikipedia tab and click the WebBrowser on it
4. In the Data tab of the Property Sheet, set the Control Source to Wikipedia
5. To preview the form, right-click its tab and click Form View

6. Close the form
7. When asked whether you want to save, click Yes

Web Browser Events

The web browser has many events appropriate for its functionality:

We already know that you can submit the path of a file or a URL to it. When a file path or a URL is given to a web browser, before it processes it, the control fires an event named On Before Navigate. If there is no problem in this event, the control shows the file or the web page. When the control has finished displaying the document, the web browser fires the On Document Complete event. If there is a change on the document, the control fires an On Progress Change event.

When a web browser has received a file path or a URL, it makes an attempt to show that file or the web page. If it encounters a problem, it fires an On Navigation Error event.

At any time, and if you allow it, the user can change the document the control is displaying. When a new document must be displayed, the control fires an On Updated event.

Attachments

Introduction

The OLE Object provides a convenient means of adding an external file to a record. One of the problems with it is that it can add only one object. Sometimes you want to associate many documents to one record and you may have a different number of objects for different records. Microsoft Access solves this problem through an attachment. An attachment is a technique of associating one or more files to a record.

Practical Learning: Introducing Attachment Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click Altair Realtors1 from the preious lesson
3. In the Navigation Pane, right-click the Properties table and click Design View

Creating an Attachment Field

You can create an attachment field in the Datasheet View or in the Design View of a table. To create an attachment in the Datasheet View:

• Click Click to Add. In the list that appears, click Attachment
• To insert a column that would hold attachments, click a cell of the column that will precede it. On the Ribbon, click Fields. In the Add & Delete section, click More Fields and click Attachment

If creating a table in Design View and if you are creating a new column, to configure it to hold one or more attachments, specify its Data Type as Attachment. If a field has already been created and the records have been added to the table, you cannot change the data type of a field to Attachment. You would receive an error when you try to save the table:

In other words, you can specify the Attachment type only if you are creating a new field.

After creating an attachment field on a table, you can configure it on a related form. In the Controls section of the Ribbon, the attachment is represented .

Practical Learning: Creating an Attachment Field

1. In the top section of the table, click the first empty cell under Field Name, and type Pictures
2. Press Tab and, in the Data Type, select Attachment
3. Save and close the table
4. In the Navigation Pane, right-click the Properties form and click Design View
5. In the Design tab of the Ribbon, in the Tools section, click Add Existing Fields
6. From the Field List, drag Pictures and drop it on the form
7. Move the accompanying label above the newly added control and change its text to Picture[s]
8. Appropriately position and resize the attachment placeholder as you see fit

9. Save the form and switch it to Form View

Using an Attachment

You can add the attachment[s] either in the Datasheet View of a table or in the Form View of a form. Once the object is displaying, you can either double-click the field or right-click the field and click Manage Attachments. This would open the Attachments dialog box:

In the Attachments dialog box, to create an attachment, click the Add button. This would open the Choose File dialog box. It behaves like the Open dialog box. When using it, select the file or object and click Open. The selected object would be added to the Attachments list view. In the same way, you can add the other objects one at a time. Alternatively, to add many objects, in the Choose File dialog box:

1. To select objects one at a time:
   1. Click one of the objects to add
   2. Press and hold Ctrl
   3. Click each object to add, one object at a time
   4. After selecting the desired objects, release Ctrl
   5. Click Open
2. To select objects in a range:
   1. Click one object at one end of the range
   2. Press and hold Shift
   3. Click the object at the other end of the range
   4. Release Shift
   5. Click Open
3. To select objects in a range:
   1. Click somewhere on the left [or the right] or the top [or the bottom] side of an object at one end of the range and hold the mouse down
   2. Drag in the opposite direction to draw a fake rectangle that would touch or include the objects to be added
   3. When the objects have been included in the range, release the mouse
   4. Click Open

To remove an item from the collection, click it in the Attachments list box and click Remove. Once you are ready with the list of attachments, click OK.

After the attachments have been created, to access those of a record, navigate to that record. Then, right-click the placeholder of the attachment. A menu would come up. Among other menu items, the menu has a Forward and a Back options:

• If the record has only one attachment, both menu items would be disabled
• If the record has only two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. At this time, the Forward menu item would be disabled and the Back option would be enabled. You can click Back to access the previous object
• If the record has more than two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. You can keep clicking Forward to advance in the collection. You can click Back to access the previous object. From the second object to the one before the last, both the Forward and the Back menu items would be enabled. When you get to the last object of the collection, the Forward menu item would be disabled and the Back item would be enabled

Practical Learning: Attaching Objects to a Record

1. For each record, right-click the placeholder under Picture[s] and click Manage Attachments...
2. In the Attachments dialog box, click Add...
3. From the resources that accompany these lessons, inside the Houses folder, locate a file whose 6 [first] characters are the same as the property number.
   If the file name does not end with a, such as 475974.bmp, click it:
   If the file name ends with a, such as 524880a.bmp, click it and click Open. Then click Add again and add additional files with the same 6 digits in the name and that ends with a letter such as a, b, c, etc

4. After adding the picture[s], click OK
5. Navigate to each record. Right-click the picture and review the pictures attached to each record
6. Close the form
7. Close Microsoft Access

Page 3

Expressions Fundamentals

Introduction to Expressions and Operations

An expression, also called an operation, is a technique of combination of a value and an operator, two values and an operator, etc. The value can be a known value or it can come from a field of a table. Based on this, to create an expression or to perform an operation, you need at least one value or field and a symbol. A value or field involved in an operation is called an operand. A symbol involved in an operation is called an operator.

Neither Microsoft Access nor Microsoft Visual Basic is case-sensitive. Therefore, any word we are going to use that involves a field, its name, and new words we will introduce in this section, whether written in uppercase, lowercase or a mix, as long as it is the same word, represents the same thing. Based on this, the words TRUE, True and true, as related to Microsoft Access, represent the same word.

 

A unary operator is one that uses only one operand. An operator is referred to as binary if it operates on two operands.

Practical Learning: Introducing Expressions

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Ceil Inn2 database
3. In the Navigation Pane, right-click the Employees form and click Design View

Introduction to Constants

A constant is a value that does not change. Examples of constants are algebraic numbers because they never change. Therefore, any number you can think of is a constant. Every letter of the alphabet is a constant. In the same way, any word you can think of is a constant.

Common Operators

The Assignment Operator =

In order to provide a value to an existing field, you can use an operator called assignment and its symbol is "=". It uses the following formula:

Field/Object = Value/Field/Object

The operand on the left side of the = operator is referred to as the left value:

• This operand must always be able to be written to
• It cannot be a known value such as a constant
• It cannot be an expression that needs to be evaluated
• It can be the name of a table's field
• It can be an appropriate property of a field
• It can be the name of a control on a form or a report.

The operand on the right side of the operator is referred to as the right value. It can be a constant, a value, an expression, the name of a field, or an object.

There are various ways you can use the assignment operator. For example, in the Property Sheet for the control, in its Control Source, write an expression that assigns the existing field. In some other cases, the assignment operator will be part of a longer expression.

Practical Learning: Using the Assignment Operator

1. On the form, click the txtHourlySalary text box in the group box
2. In the Property Sheet, click the Data tab and click Control Source
3. In the Record Source, type = HourlySalary:

4. Save the form

Positive and Negative Values

An algebraic value is considered positive if it is greater than 0. A value is referred to as negative if it is less than 0. To express a negative value, it must be appended with the - symbol. Examples are -12, -448, -32706.

Besides a numeric value, the value of a field or an object can also be expressed as being negative by typing a - sign to its left. For example, -txtLength means the value of the control named txtLength must be made negative.

Algebraic Operators

The addition is used to add one value or expression to another. It is performed using the + symbol. The formula to follow is:

Value1 + Value2

The multiplication allows adding one value to itself a certain number of times. The multiplication is performed with the * symbol. The formula to follow is:

Value1 * Value2

The division is used to get the fraction of one number in terms of another number. Microsoft Access supports two types of results for the division operation. If you want the result of the operation to be a natural number, use the backlash "\" as the operator. The formula to follow is:

Value1 \ Value2

This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. The second type of division results in a decimal number. It is performed with the forward slash "/". The formula to follow is:

Value1 / Value2

After the operation is performed, the result is a decimal number.

Practical Learning: Performing Algebraic Operations

1. On the form, click the txtMonthlySalary text box
2. In the Data tab of the Property Sheet, click Control Source and type = HourlySalary * 160
3. On the form, click the txtYearlySalary text box
4. In the Data tab of the Property Sheet, click Control Source and type = txtMonthlySalary * 12
5. Switch the form for Form View

6. Switch the form back to Design View

The Exponentiation: ^

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator [Shift + 6]. It uses the following mathematical formula:

yx

In Microsoft Access, this formula is written as y^x and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated.

The Remainder Operator: Mod

The division operation gives a result of a number with or without decimal values. Sometimes you will want to get the value remaining after a division renders a natural result. The remainder operation is performed with the Mod keyword. The formula to follow is:

Value1 Mod Value2

Microsoft Access Operators

The Period Operator: .

We already know that a property is something that characterizes or describes an object. An example of a property is the width of a control or the text it contains. To access the property of an object, type the name of the object, followed by a period, followed by the name of the property you need. The formula to follow is:

Object.PropertyName

The property you are trying to use must be a valid property of the object.

The Square Brackets Operator: []

To name our objects so far, in some cases we used a name made of one word without space. Actually, you can use spaces or some special characters in a name. Unfortunately, when such names get involved in an expression, there would be an error or the result would be unpredictable.

To make sure Microsoft Access can recognize any name in an expression, you can/should include it between an opening square bracket "[" and a closing square brackets "]". Examples are [© Year], [Soc. Sec. #], or [Date of Birth]. Even if the name is in one word, to be safe, you should [always] include it in square brackets. Examples are [Country], [FirstName], or [SocialSecurityNumber]. Therefore, the =txtLength expression can be written =[txtLength].

Practical Learning: Using the Square Brackets

1. On the form, click one of the salary text boxes in the group box.
   In the Property Sheet, notice that the square brackets were automatically added:

2. Save the form

The Collection Operator: !

The objects used in Microsoft Access are grouped in categories named collections. For example, the forms of a database belong to a collection of objects named Forms. The reports belong to a collection of objects named Reports. The data fields belong to a collection named Fields. The controls on a form or report belong to a collection named Controls

To refer to a particular object in an expression, use the exclamation point operator "!". To do this, type the name of the collection followed by the ! operator, followed by the name of the object you want to access. For example, on a form, if you have a text box named txtLength and you want to refer to it, you can type [Controls]![txtLength]. Therefore, the =txtLength expression can be written =Controls!txtLength, and =[txtLength] can be written =Controls![txtLength] or =[Controls]![txtLength].

The name of the collection is used to perform what is referred to as qualification: the name of the collection "qualifies" the object. In other words, it helps to locate the object by referring to its collection. This is useful in case two objects of different categories are being referred to.

In a database, Microsoft Access allows two objects to have the same name, as long as they do not belong to the same category. For example, you cannot have two forms named Employees in the same database. In the same way, you cannot have two reports named Contracts in the same database. On the other hand, you can have a form named Employees and a report named Employees in the same database because each object belongs to a different collection. For this reason, when creating expressions, you should [strongly] qualify the object you are referring to, using its collection. An example would be Forms!Employees which means the Employees object of the Forms collection. If the name of the form is made of more than one word, or for convenience, you must use square brackets to delimit the name of the form. The form would be accessed with Forms![Employees].

To refer to a control placed on a form or report, you can type the Forms collection, followed by the ! operator, followed by the name of the form, followed by the ! operator and followed by the name of the control. An example would be Forms!People!LastName. Using the assignment operator that we introduced earlier, if on a form named People, you have a control named LastName and you want to assign its value to another control named FullName, in the Control Source property of the FullName field, you can enter one of the following expressions:

=LastName =[LastName] =Controls!LastName =[Controls]![LastName] =Forms!People!LastName =[Forms]![People]![LastName]

These expressions would produce the same result.

The Parentheses Operator: []

Parentheses are used in two main circumstances: in expressions [or operations] or in functions. The parentheses in an expression help to create sections. This regularly occurs when more than one operators are used in an operation. Consider the following operation: 8 + 3 * 5

The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write [8 + 3] * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + [3 * 5]. This would produce 23.

As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one.

Introduction to Functions

Overview

A function is a task that must be performed to produce a result on a table, a form, or a report. It is like an operation or an expression with the first difference that someone else created it and you can just use it. For example, instead of the addition operator "+", to add two values, you could use a function.

In Microsoft Access, you cannot create a function. You can only use those that have been created and exist already. These are referred to as built-in functions.

Microsoft Access ships with various functions to perform different tasks. There are so many of them that we will review only a few.

If you had to create a function [remember that we cannot create a function in Microsoft Access; the following sections are only hypothetical but illustrative of the subject of a function], a formula you would use is:

FunctionName[] End

This formula is very simplistic but indicates that the minimum piece of information a function needs is a name. The name allows you to refer to this function in other parts of the database. The name of the function is followed by parentheses. As stated already, a function is meant to perform a task. This task would be defined or described in the body of the function. In our simple syntax, the body of the function would start on the line under its name and would stop one the line just above the End word. The person who creates a function also decides what the function can do. Following our simple formula, if we wanted a function that can open Solitaire, it could appear as follows:

FunctionExample[] Open Solitaire End

Once a function has been created, it can be used. Using a function is referred to as calling it. To call a simple function, you would just type its name.

A function produces a result. This is also stated that a function returns a value. Based on this, the result of a function can be provided for further use and assigned [passed] to a field or to another function. To display the result of a function in a field, you can access the field's Control Source property, use the assignment operator "=", and type the name of the function followed by parentheses. This can be done as follows:

The person who creates a function also decides what kind of value the function can return. For example, if you create a function that performs a calculation, the function may return a number. If you create another function that combines a first name and a last name, you can make the function return text that represents a full name.

Arguments and Parameters

When asked to perform its task, a function may need one or more values to work with. If a function needs a value, such a value is called a parameter. The parameter is provided in the parentheses of the function. The formula used to create such a function would be:

ReturnValue FunctionName[parameters] End

Once again, the body of the function would be used to define what the function does. For example, if you were writing a function that multiplies its parameter by 12.58, it would appear almost as follows:

Decimal FunctionName[Parameter] parameter * 12.58 End

While a certain function may need one parameter, another function would need many of them. The number and types of parameters of a function depend on its goal. When a function uses more than one parameter, a comma separates them in the parentheses. The formula used is:

ReturnValue FunctionName[Parameter1, Parameter2, Parameter_n] End

If you were creating a function that adds its two parameters, it may appear as follows:

NaturalNumber AddTwoNumbers[Parameter1, Parameter2] Parameter1 + Parameter2 End

Once a function has been created, it can be used in other parts of the database. Once again, using a function is referred to as calling it. If a function is taking one or more parameters, it is called differently than a function that does not take any parameter. We saw already how you could call a function that does not take any parameter and assign it to a field using its Control Source. If a function is taking one parameter, when calling it, you must provide a value for the parameter, otherwise the function would not work [when you display the form or report, Microsoft Access would display an error]. When you call a function that takes a parameter, the parameter is called an argument. Therefore, when calling the function, we would say that the function takes one argument. In the same way, a function with more than one parameter must be called with its number of arguments.

To call a function that takes an argument, type the name of the function followed by the opening parenthesis "[", followed by the value [or the field name] that will be the argument, followed by a closing parentheses "]". The argument you pass can be a constant number. Here is an example:

The value passed as argument can be the name of an existing field. The rule to respect is that, when Microsoft Access is asked to perform the task[s] for the function, the argument must provide, or be ready to provide, a valid value. As done with the argument-less function, when calling this type of function, you can assign it to a field by using the assignment operator in its Control Source property.

If the function is taking more than one argument, to call it, type the values for the arguments, in the exact order indicated, separated from each other by a comma. As for the other functions, the calling can be assigned to a field in its Control Source.

All the arguments can be constant values, all of them can be the names of fields or objects, or some arguments can be passed as constants and others as names of fields.

Optional Arguments and Default Values

We have mentioned that, when calling a function that takes an argument, you must supply a value for the argument. There is an exception. Depending on how the function was created, it may be configured to use its own value if you fail, forget, or choose not, to provide one. This is known as the default argument. Not all functions follow this rule and you would know either by checking the documentation of that function or through experience.

If a function that takes one argument has a default value for it, then you don't have to supply a value when calling that function. Such an argument is considered optional. Whenever in doubt, you should provide a value for the argument. That way, you would not only be on the safe side but also you would know with certainty what value the function had to deal with.

If a function takes more than one argument, some argument[s] may have default values while some others don't. The arguments that have default values can be used and you don't have to supply them.

The Expression Builder

Introduction

To assist you with writing an expression or calling a [built-in] function and reducing mistakes, Microsoft Access is equipped with a dialog box named the Expression Builder.

The Expression Builder is used to create an expression or call a function that would be used as the Control Source of a field.

Using the Expression Builder

To access the Expression Builder, open the Property Sheet for the control that will use the expression or function, and click its ellipsis button . This would call the Expression Builder dialog box:

The Expression Builder is resizable: you can enlarge, narrow, heighten, or shorten it, to a certain extent.

Under the title bar, there is the Calculated Control link. If you click that link, a Help window would come up. Under the link, there is an example of an expression.

The main area of the Expression Builder is a rectangular text box used to show the current expression. If you already know what you want, you can directly type an expression, a function, or a combination of those.

To show a reduced height of the Expression Builder, click the >:

To show the whole dialog box, click More >>.

Under the text box, there are three boxes. The left list displays some categories of items. Some items in the left list appear with a + button. To access an object, expand its node collection by double-clicking its corresponding button or clicking its + button. After you have e xpanded a node, a list would appear. In some cases, such as the Forms node, another list of categories may appear.

To access an object of a collection, in the left list, click its node. This would fill the middle list with some items that depend on what was selected in the left list. Here is example:

The top node is the name of the form or report on which you are working. To access a function, first expand the Functions node. To use one of the Microsoft Access built-in functions, in the left list, click Built-In Functions. The middle list would display categories of functions. If you see the function you want to use, you can use it. If the right list is too long and you know the type of the function you are looking for, you can click its category in the middle list and locate it in the right list.

Once you see the function you want in the right list, you can double-click it. If it is a parameter-less function, its name and parentheses would be added to the expression area:

If the function is configured to take arguments, its name and a placeholder for each argument would be added to the expression area:

You must then replace each placeholder with the appropriate value or expression. To assist you with functions, in its bottom section, the Expression Builder shows the syntax of the function, including its name and the name[s] of the argument[s]. To get more information about a function, click its link in the bottom section of the Expression Builder. A help window would display. Here is an example:

Depending on the object that was clicked in the left list, the middle list can display the Windows controls that are part of, or are positioned on, the form or report. For example, if you click the name of a form in the left list,the middle list would display the names of all the controls on that form. To use one of the controls on the object, you can double-click the item in the middle list. When you do, the name of the control would appear in the expression area.

Some items in the middle list hold their own list of items. To show that list, you must click the item in the middle list. For example, to access the properties of a control positioned on a form, in the left list, expand the Forms node and expand All Forms:

Then, in the left list, click the name of a form. This would cause the middle list to display the controls of the selected form. To access the properties of the control, click its name in the middle list. The right list would show its properties:

To get help while using the Expression Builder, you can click Help. After creating an expression, if you are satisfied with it, to submit it, click OK. To abandon whatever you have done, click Cancel or press Esc.

The Calculated Field of a Table

You can create a field in a table so that the field would hold an expression. To do this, start the table in either the Datasheet View or the Design View:

• In the Datasheet View
  • Click Click to Add, position the mouse on Calculated Field, and select one of the options:

  • Click a cell under the desired column. In the Add & Delete section of the Ribbon, click More Fields, postion the mouse on Calculated Field and select one of the options
• In the Design View, specify the name of the new field. Set its Data Type to Calculated

In both cases, the Expression Builder would come up. You can then type or create the desired expression in the top text box.

Page 4

	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:

Name	Email Address	Phone Number	Relationship
Bill			Friend
James	jamesemail.com	[102]399-2893
Hermine		[101] 447-8384	Cousin
Khan	@Khan.com

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

Previous	Copyright © 1997-2019, FunctionX, Inc.	Next