FIELD PROPERTIES.
The table’s Design view is divided into 2 parts. The lower part is used to display and assign field properties to
selected fields.
Field properties - a set of characteristics that provide additional control over how the data in a field is stored,
entered, or displayed.
Each data type has a different set of properties.
The different field properties include:
(i). FieldSize.
The FieldSize property defines/ controls the maximum no. of characters that can be entered/ stored in a
Text or Number field.
(ii). Format.
The Format property specifies how (the way) data is displayed in a field & printed.
Number & Currency fields provide predefined display formats. They include Currency, Fixed, General,
Percent, Scientific, General data, Medium date, Medium time.
(iii). Decimal places.
It is used to define the no. of decimal places in values. This option is used on fields that already have the
Fixed format such as in Number and Currency fields.
(iv). Default value.
It allows you to define a value that will automatically be inserted into the field as you add new records. It
is used in Text and Memo fields.
For example, to insert the current date in the Admission date field use “Date ( )”.
The default value affects only new records inserted.
(v). Validation Rule.
It allows you to create an expression or a formula that will test data when being entered into the field.
It automatically rejects faulty/flawed entries, e.g., entering very large or very small figures in the Age or
Salary fields.
Validation Rule Description
Between 18 and 55 Limits an age field from 18 to 55.
<10000 Allows any value less than 10000
<=500 AND <=1000 Accepts any entry between 500 and 1000.
>Date( ) Allows entries whose dates are the current date or later.
<Date( ) Allows the current date and before.
Note. To test whether the Validation rule is applicable, choose Test Validation Rules on the Edit menu.
(vi). Validation Text.
Defines an error message that will be displayed when the validation rule is violated/broken.
It must be typed in earlier.
Enter a useful message that can be clearly understood by the input clerk.
(vii). Required.
It controls the entry of important fields.
When this option is set to YES, an entry must be made within that field, i.e., it ensures that the field is not
left blank.
A YES option is equivalent of typing IS NOT NULL as a field Validation rule.
(viii). Input Mask.
Lets you define a pre-formatted pattern for the entry of data into a Date or a Text field. The data in that
field must conform to a pattern.
Examples of data include:
0#/0#/0# - this forces a 2-digits entries for the year, month and date in a date field.
A - Allows letters or digits into a field, but an entry is required.
9 - Allows letters or digits in a field, but no entry is required.
000000 - Allows a 6-digit number (not more or less than that).
999999 - Allows 6 or less digits.
AAAAA - only 5 characters are to be entered.
> - converts the field entry to Uppercase.
< - converts the field entry to Lowercase.
(ix). Allow Zero Length.
Applies to Text and Memo field data types.
Setting the value in Allow Zero Length property to YES and the Required property YES, the field must
contain at least one character.
(x). Caption.
This is an alternative name used (other than the fieldname) in order to appear in the fieldname header
button in Table datasheet view and as a label in Forms.
They provide a neat and descriptive name since the fieldname should be kept small for practical use.
(xi). Indexed.
[Yes (Duplicates OK)] – Gives sorted, indexed field and can allow data duplicates.
[Yes (No duplicates)] – Gives sorted, indexed field but cannot allow data duplicates.
Note. It is not available for Memo or OLE object data types.
(xii). New Values.
Applies only to AutoNumber fields.
Ms-Access can increment the AutoNumber field by 1 for each new record, or fill in the field with a
randomly generated no., depending on the New Values property setting that you choose.
RELATIONSHIPS IN A MS-ACCESS DATABASE FILE.
To store your data, create one table for each type of information that you track.
After setting up the different tables for each subject, you can define relationships between the tables.
Relationship - An association established between common fields (columns) in two tables.
Defining relationships is a way of telling Ms-Access how to bring information/ data from multiple tables back
together again in a query, form, or report.
After defining the relationship, you can create queries, forms, and reports to display information from several
tables at once.
Relating two tables.
In order to relate two tables, each table should include a field or set of fields that uniquely identifies each record
stored in the table. Such a field is called the Primary key of the table.
The Primary key field relates two tables so that Ms-Access can bring together the data from the two tables for
viewing, editing, or printing.
In one table, the field is a Primary key that you set in table Design view. That same field also exists in the
related table as a Foreign key.
Define relationships between tables.
When you create a relationship between tables, the related fields may not have the same names, but must have
the same data type.
A relationship can be One-to-one, One-to-many, or Many-to-many.
QUERIES.
A Query is a question about the data stored in your tables, or a request to perform an action on the data.
You use queries to view, change, and analyze data in different ways.
A query can find & bring together data that meets conditions that you specify from multiple tables. It can also
serve as the source of data for a Form, or a Report.
A query can also update or delete multiple records at the same time, and perform predefined or custom
calculations on your data.
Limit results by using criteria
You can limit the records that you see in the query's results or the records that are included in a calculation by
specifying criteria.
For example;
(i). To limit the records in the query's results, enter criteria in one or more fields.
Between #6/1/01# And #6/15/01#
(ii). Use the Or row for alternative criteria in the same field.
Between #6/1/01# And #6/15/01#
Or Between #7/1/01# And #6/30/01#
(iii). Enter criteria for different fields. For example, for orders between 6/1/01 and 6/15/01 ...
Between #6/1/01# And #6/15/01#
(iv). Calculate total order amounts, but display only those that are more than $100,000.
>100000
FORMS.
A Form is an Access database object on which you place controls for taking actions or for entering, displaying,
and editing data in fields.
A form is a type of a database object that is primarily used to enter or display data in a database.
To easily view, enter, and change data directly in a table, create a form. When you open a form, Microsoft
Access retrieves the data from one or more tables, and displays it on the screen with the layout you choose in
the Form Wizard, or with the layout that you created on your own in Design view.
REPORTS.
A Report is an Access database object that prints information formatted and organized according to your
specifications. Examples of reports are sales summaries, phone lists, and mailing labels.).
To analyze your data or present it a certain way in print, create a report. For example, you might print one
report that groups data and calculates totals, and another report with different data formatted for printing
mailing labels.