MICROSOFT ACCESS
Data and Information
Data consists or raw facts and figures, Data consists of letter, sound, image or video within the computer.
Processed data is known as information. Using a data base program or software, they are converted into the meaningful result called information.
Database
A database is a collection of interrelated data. It refers to an organized collection of data stored on a computer in such a way that its content can easily be accessed, updated and queried upon with the help of a software program.
Example = Banks must have systems for keeping track of customers and their accounts.
Advantages of database
i. Database saves the data.
ii. Data can be stored in a small area or space in computerized database.
iii. Database protects from unauthorized access and use of data.
iv. It saves data from being lost.
Objects of MS Access
a. Table = Table are the core database object, used to store information. The tables are the containers for all the data in a database. In a table, data are arranged in a columns and rows.
b. Queries = Queries means to ask question about the data presented in the table. BY using queries, we can view records or data, modify the content of table and a lot more. It is a way of retrieving and editing information according to the condition asked. It is a strong tool using in searching of desired data from database.
c. Forms = A form is a database object used to enter and edit data in a table. Form can be used to edit the records of underlying tables or to enter new records.
d. Reports = A report is especially formatted collections of data organized according to our specification for summarizing and printing. They are the means to present the information as a printed document.
e. Macros = When macro is executed, it executes one or more database commands automatically. They are meant for automating the task that is done over and over again.
f. Modules = Modules are pieces of code used to impose particular behaviours to your application programmatically. They are more flexible and extensive than macros, although they are usually written for various and particular circumstances.
Attributes and Record
Attributes refer to a single data item related to a database object. The database schema associates one or more attributes with each database entity.
Record is a row of table in Microsoft Access. It contains information about a particular item stored in a table.
Database management system and it's software
A database management system is a collection of programs that enables you to store, modify, and extract information from database.
Any four database management systems are as follows:
i. Relational Database Management System
ii. Hierarchical Database Management System
iii. Network Database Management System
iv. Object Oriented Database Management System
Field Data Type
A characteristic of a field that determines what kind of data it can store is known as Field Data Type.
Data types used in Ms-Access
a. Text = A text field contains up to 255 characters or it can set the length in the field size, text data type can include numbers letters and symbols.
b. Memo = This type of field holds an unlimited amount of text. It can accommodate 65, 535 characters.
c. Number = This type of field allows only the numbers to be entered.
d. Date/Time = This field type only allows date and time to be entered. It occupies 8 bytes.
e. Currency = This field allows to enter currency.
f. AutoNumber = This type automatically generates and insert the unique number in the new row.
g. Yes/No = It allows the user to select Yes or No when completing the field.
h. OLE object = It allows to link to binary data or files.
i. Hyperlink = It allows to create a hyperlink. It stores up to 64,000 characters.
j. Lookup Wizard = The lookup wizard creates a field that allows the user to choose a value from a list of values by using a list box or combo box.
Rules for naming field
a. It can be up to 64 characters long.
b. The naming cannot include control characters.
c. It cannot begin with leading spaces.
d. It can include any combination of letters, numbers, spaces and special characters except a period (.), an exclamation mark (!), an accent grave (`) and brackets ([ ]).
Primary Key
The primary key is a special column or group of columns in the table whose value must be unique in the table.
We can set the primary key by creating a table relationship. To relate tables a field is required which should be common to all the related tables. The common field of main table is known as primary key field.
The differences between primary key and foreign key
Primary Key | Foreign Key |
It is a column or a set of column that can be used to uniquely identify a row in a table. | It is a column or a set of column that refer to a primary key or a candidate key of another table. |
A table can have a single primary key that can reference different tables. | A table can have multiple foreign key that can reference different tables. |
Field property: A field property that controls what can be put into a field and how it can be placed there is known as field properties.
Validatation Rule: Validation rule is a property of Date/time field.
Input Mask: An input mask refers to a string expression that governs what a user is allowed to enter in as input in a text box.
Relational Database management system: A relational Database management system is a database system that is based on relation model. It is a database management system designed to manage a relational database.
Sorting records: Sorting records is the arrangement of records on the basis of a field or fields of a table either in ascending order or in descending order.
Filtering records
Filtering records is the process of viewing required data of a table that specify the specified criteria.
The different techniques for filtering records are as follows:
i. Filter by selection
ii. Filter by form
iii. Advanced filter