Microsoft access tutorial 3

Unit 5. Properties of the fields

Introduction

Every field within a table has a severals characteristics set out which provide an additional control with regard to the way in which the field functions. The properties appear in the lower part of the Table design window when we have a field selected. The properties are grouped in two tabs: the General tab where we indicate the fields general characteristics, and the Lookup tab where we can define a list of valid values for the field. This last tab is explained in unit 3 together with the lookup wizard. The properties in the General tab can vary for one type of data to another, while the properties in the Lookup tab change according to the type of control associated with the field. Keep in mind that if the properties of a field are modified after data has already been introduced, this data may be lost. Next we will explain the various properties available for the different types of data.

Field size

For Text fields, this property determines the maximum number of characters that can be introduced into the field. By default it is set at 50 characters with a maximum value of 255. For Numeric fields the options are: Byte (equivalent of 1 character) to store integer values of between 0 and 255. Integer for those values between -32,768 and 32,767. Long integer for integer values between 2,147,483,648 and 2,147,483,647. Single for values between -3.402823E38 and-1.401298E-45 for negative values, and between 1.401298E-45 y 3.402823E38 for positive values. Simple para la introducción de valores comprendidos entre -3.402823E38 y -1.401298E-45 para valores negativos, y entre 1.401298E-45 y 3.402823E38 para valores positivos. Double for values between -1.79769313486231E308 y -4.94065645841247E-324 for negative values, and 1.79769313486231E308 y 4.94065645841247E-324 for positive values. Replication ID is used for autonumeric keys in replication bases. Decimal is for storing values between -10^38-1 and 10^38-1 (if we are in a .adp database) and numbers between -10^28-1 and 10^28-1 (if we are in a .mdb database) Autonumerical fields are Long integer. Size can not be specified with any other type of data.

Field format

This property is used to personalise the way in which data is presented on the screen or in a report. This can be establish in all types of data except the OLE object and Autonumerical. For Numeric and Currency fields the options are: General number: presents numbers in the same way as they have been introduced. Currency: presents the values introduced with a thousandth separator and the assigned monetary symbol in Windows e.g $ Euro: uses the currency format, with the euro symbol. Fixed: presents the values without the thousandth separator. Standard: presents the values with the thousandth separator. Percent: multiplies the value by 100 and adds the percent sign (%). Scientific: presents the number with scientific notation. Date/Time fields have the following formats: General date: if the value is only a date no time is shown; if the value is only a time no date is shown. This value is a combination of the Short date and Long time values. E.g '3/4/93', '05:34:00 PM' and '3/4/93 05:34:00 PM'. Long date: the date is seen together with the day of the week and the month in full. E.g Monday, August 21, 2000. Medium date: presents the month with the first 3 letters. E.g 21-Aug-2000. Short date:the date is presented with 2 digits for the day and month. E.g 01/08/00 or 01/08/2000. The Short date format assumes that dates between 1/1/00 and the 31/12/29 are dates between the years 2000 and 2029, and that the dates between 1/1/30 and the 31/12/99 are for the years 1930 to 1999. Long time: presents the time in normal format. E.g 05:35:20 PM Medium time: presents the time in PM or AM format. E.g 5:35 PM Short time: presents the time without seconds neither PM. E.g 17:35. Yes/No fields have predefined Yes/No, True/False, and On/Off formats available. Yes, True and ON are the equivalents of yes, as are No, False, Off of no. Note: The default control for Yes/No data is the verification box as we seen in Datasheet the data of this type appears in a verification box and does not affect the format property. If we choose to see the effect we need to change the default control in the Lookup tab in the fields properties, in the Display control property select Text box as we will show you next. The Text and Memo fields do not have predefined formats available, for the Text fields we will need to create your own custom formats. If you want more information on custom format clic here

Decimal places

This property allows us to indicate the number of decimals we would like to assign to a Number or Currency type data.

Input mask

The input mask is used to ease the data entry and to control the values that users are permitted to introduce in the field. E.g, you can create an input mask for a Telephone number field that shows the exactly how a new number should be introduced:(___) ___-____. Access has an input mask wizard that helps us to establish this property, to enter the wizard click on the button that appears to the right of the property once this property is activated. If you require more information about the customizing of an input mask click here

Caption

This property is used to indicate how we choose to visualize the title of the field. E.g, if a fields name is Birth and we indicate Date of birth as the value in the Caption property, we will see Date of birth in the header of the Birth field and in the labels in forms or reports..

Default value

The default value is the value automatically stored in the field if no value is introduced. It is usually used when we know that a determined field is going to have the same value the majority of the time, it is used to indicate or specify what the value will be so that it can be automatically introduced at the time of data being introduced into the table. E.G, if we have the Clients table with the Province field and the majority of the clients are from the TEXAS province, this value can be introduced into the Default value property of the Province field, and therefore at the moment of introducing a new client the value TEXAS will appear automatically without needing to be typed. This property can be used with all types of data except OLE object and Autonumber.

Validation rule.

This property permits us to control the entry of data according to a specified criteria.The criteria must to be typed to ensure that any value introduced into the field is good. E.g, if we decide that the values introduced should be between 100 and 2000, we type >=100 AND <=2000 in Validation Rule property, so when data is entered and not matches the condition, an error message will be displayed. This property can be use with all types of data except OLE object and Autonumerical. To build the condition you can use the Expression Builder as we will explain to you in this video tutorial.

Validation text

In this property we will write the text which we wish to appear in the case of us trying to introduce a value into a field that does not comply with the validation rule specified before. The message needs to be so that the person making the mistake will realize his error and be able to correct it. This property can be used with all types of data except OLE object and Autonumerical.

Required

Set this property to Yes value, so the user must enter a correct value into the field. When this property is set to No, the field can remain empty. This property can be used with all types of data except Autonumber.

Indexed

This property is used to set a single-field index. An index speeds up queries on the indexed field as well as sorting or grouping sorting.

E.g, if you are looking for employees using the Surname field, you can create an index on the field to make the search faster.

This property has three available values:

No: without index it is the default value for this property.

Yes (uplicates OK): When an index is assigned to a field and furthermore allows duplicate values (two rows with the same value in the field).

Yes (No duplicates): When an index is assigned but no duplicate values are allowed.

If you want to know more about indexes clic here .

Unit 6. Relationships (I)

In this unit we will see how to relate tables and the different types of relationships that can exist between two tables in a database. If you do not know what a relationship is in a database, here we will introduce you to some basic concepts to understand relationships better.

Creating the first relationship.

To create relationships in Access2003 firstly we need to position ourself in the Relationships window, we can opt for: in Database window, drop down Tools menu, and select Relationships... option. or, Click on the button on the toolbar.

The Show table dialogue box will appear: Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table. Repeat the previous step to add the second table, and so on. Finally click on the Close button to finish adding tables.

Now the Relationships window will appear with the tables added before. To create the relationship: Drag the field of the principal table to the equivalent field in the related table. In our case drag Number (in Customers table) to Customer (in Invoices table). Normally you drag the primary key of the primary table. To relate tables with two or more fields, first select the fields mantaining CTRL key down, and then drag them.

The Edit relationships dialogue box appears next:

At the top should be the names of the related tables (Customers and Invoices) and below this the names of the related fields (Number and Customer). Observe! they always have to be the same kinds of fields containing the same types of information.

Observe at the bottom the Relationship type assigned depends on the charateristics of the related fields (in our case One-to-Many)

Activate the Enforce Referential integrity by clicking on it.

If desired, the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated.

If you want to know more about referential integrity and Cascade procedures clic here .

To terminate, click on the Create button.

The relationship is created and will appear in the Relationships window.