MS ACCESS

Microsoft Access is a Database Management System (DBMS) from Microsoft that combines the
relational Microsoft Jet Database Engine with a graphical user interface and software development
tools. It is a member of the Microsoft Office suite of applications, included in the professional and
higher editions.
1. Microsoft Access is just one part of Microsoft’s overall data management product strategy.
2. It stores data in its own format based on the Access Jet Database Engine.
3. Like relational databases, Microsoft Access also allows you to link related information easily.
For example, customer and order data. However, Access 2013 also complements other
database products because it has several powerful connectivity features.
4. It can also import or link directly to data stored in other applications and databases.
5. Microsoft Access stores information which is called a database. To use MS Access, you will
need to follow these four steps:
I. Database Creation - Create your Microsoft Access database and specify what kind of
data you will be storing.
II. Data Input - After your database is created, the data of every business day can be
entered into the Access database.
III. Query - This is a fancy term to basically describe the process of retrieving information
from the database.
IV. Report (optional) - Information from the database is organized in a nice presentation
that can be printed in an Access Report.
V. Before MS Access 2007, the file extension was *.mdb, but in MS Access 2007 the
extension has been changed to *.accdb extension.
VI. An RDBMS allows you to define the kind of data you have and how the data should be
stored.
MS Access uses “objects" to help the user list and organize information, as well as prepare
specially designed reports. When you create a database, Access offers you Tables, Queries, Forms,
Reports, Macros, and Modules. Databases in Access are composed of many objects but the
following are the major objects:
1. Tables
2. Relationship
3. Queries
4. Forms
5. Reports
Together, these objects allow you to enter, store, analyze, and compile your data. Here is a
summary of the major objects in an Access database;
Table: - Table is an object that is used to define and store data. When you create a new table,
Access asks you to define fields which is also known as column headings
 Each field must have a unique name, and data type.
 MS ACCESS
 Tables contain fields or columns that store different kinds of data, such as a name or an address,
and records or rows that collect all the information about a particular instance of the subject, such
as all the information about a customer or employee etc.
 You can define a primary key, one or more fields that have a unique value for each record, and
one or more indexes on each table to help retrieve your data more quickly.
Query: - An object that provides a custom view of data from one or more tables. Queries are a way
of searching for and compiling data from one or more tables.
 Running a query is like asking a detailed question of your database.
 When you build a query in Access, you are defining specific search conditions to find exactly the
data you want.
 In Access, you can use the graphical query by example facility or you can write Structured Query
Language (SQL) statements to create your queries.
 You can define queries to Select, Update, Insert, or Delete data.
 You can also define queries that create new tables from data in one or more existing tables.
Form: -Form is an object in a desktop database designed primarily for data input or display or for
control of application execution. You use forms to customize the presentation of data that your
application extracts from queries or tables.  Forms are used for entering, modifying, and viewing
records.
 The reason forms are used so often is that they are an easy way to guide people toward entering
data correctly.
 When you enter information into a form in Access, the data goes exactly where the database
designer wants it to go in one or more related tables.
Report: - Report is an object in desktop databases designed for formatting, calculating, printing,
and summarizing selected data.
 You can view a report on your screen before you print it.
 If forms are for input purposes, then reports are for output.
 Anything you plan to print deserves a report, whether it is a list of names and addresses, a
financial summary for a period, or a set of mailing labels.
 Reports are useful because they allow you to present components of your database in an easy-
to-read format.
 You can even customize a report's appearance to make it visually appealing.
 Access offers you the ability to create a report from any table or query.
 MS ACCESS
TYPE OF DATA Description
SHORT TEXT Text or combination of text and numbers, including numbers that do not require
calculating
LONG TEXT Lengthy tet or combinations of text and numbers.
NUMBER Numeric data used in mathematical calculations
DATE/TIME Date and time values for the years 100 through 9999
CURRENCY Currency values and numeric data used in mathematical calculations involving
data with one to four decimal places.
AUTO A unique sequential number or random number assigned by Microsoft access
NUMBER whenever a new record is added to a table.
YES/NO Yes and no values and field that contain only one of two values ( yes/no,
true/false, or on/off)
Attachment Files, such as digital photos. Multiple files can be attached per record. This data
type is not available in earlier versions of Access.
OLE objects OLE objects can store pictures, audio, video, or other BLOBs (Binary Large
Objects)
Hyperlink Text or combinations of text and numbers stored as text and used as a hyperlink
address.
Lookup The Lookup Wizard entry in the Data Type column in the Design view is not
Wizard actually a data type. When you choose this entry, a wizard starts to help you
define either a simple or complex lookup field. A simple lookup field uses the
contents of another table or a value list to validate the contents of a single value
per row. A complex lookup field allows you to store multiple values of the same
data type in each row.
Calculated You can create an expression that uses data from one or more fields. You can
designate different result data types from the expression.

The primary key: -A primary key is a single field or combination of fields in a table that uniquely
identifies each record in that table. No two records can have the same value in the primary key
field or combination of fields. It is advisable to create the primary key before entering data in a
table because Access will prevent duplicate values being created in the designated field. It is
particularly important to set a primary key when creating relationships between tables as Access
uses it to link tables together.
Relationship: -A relationship is a definition that declares a link between two tables based upon a
common field. Each table is said to be on one or other side of the relationship. The ability to define
relationships allows related data to be stored in separate tables, allowing greater freedom and
efficiency in database design and reducing the need to duplicate data. Many tables can be linked
together in this way.
There are three types of table relationships you can have in Access
a. One-to-one
b. One-to-Many,
c. Many-to-Many,