Working With MS-Access 2013
Category : 10th Class
Working With MS-Access 2013
Introduction
MS-Access 2013 is an application software integrated with Microsoft Office package which is used for database management applications. Data is the raw fact. It can be character, number or symbols which are processed by a computer. In terms of computer science you can say data may be characters, facts or number, which can be processed by a computer. A group of meaningful data is called information. Information may be broken down further.
In computerize system, you can store the data in spreadsheets or in database. An organized collection of data, which can be updated, accessed and managed in a systematic order is called database. With the help of computers, one can works on huge volume of data. It allows the user to access data conveniently and data can also be easily updated whenever it is required.
Need of Database
Without database you cannot maintain data in proper way. You can face a lot of problem, such as data duplicity and security. Using database you can manage your data in proper and secure way.
Database provides the following advantages:
v Sharing Data.
v Avoiding data redundancy.
v Maintaining integrity of data.
v Avoiding inconsistency of data.
v Securing data.
Disadvantages
Database maintenance increases the cost because it requires special database software and hardware. Database systems are complex, difficult and time-consuming to design. It requires initial training for all users and programmers.
Getting started with Access
Click on Start 
All Apps Microsoft Office Access 2013
In the left pane, the template categories?including the featured local templates?are listed, as well as the categories on Office Online. Templates are prebuilt databases focused on a specific task that you can download and use immediately.
In the example below, the featured templates are selected, and the template options are displayed in the center area of the screen. Featured templates include database template options that are available online, as well as templates available as part of the local version Access.
Opening a database
You have three main options on the Getting Started page. You can open a template database stored locally or online, an existing database, or a blank database.
To view templates included with Access:
v Click on Blank desktop database and click on create option.
v Now the screen will change to main screen of the database.
In the left pane of the Getting Started window, you will see a list of categories for the templates available on Microsoft Office Online. You must have an Internet connection to download these database templates.
The Blank desktop Database command allows you to create a database from scratch.
To open an existing database:
v In the Recent Access section, left click on the file name of the database you wish to open. It will appear in the window.
The Access Window
Before you can begin to use a database, you need to become familiar with the Access window.
The Ribbon
Like other software in the Microsoft Office 2013 suite, Access 2013 has a tabbed Ribbon system that you use to navigate the database. The Ribbon is organized into tabs. Each tab contains groups of commands that you use to perform tasks in Access.
For example, on the Home tab, you'll see that there is a Text formatting group that contains the font formatting commands such as font type, size, color and alignment. Unless you use keyboard shortcuts for everything, the Ribbon is how you get the work done in Access.
Quick Access Toolbar
Once you are familiar with Access, you may find that there are commands you use more often than others. To make it easier to use these commands, you can add them to the Quick Access toolbar. By default, the toolbar appears above the Ribbon on the left side of the Access window and includes the Save, Undo, and Redo commands.
Navigation Pane
The main control center of each Access database is the Navigation Pane. It shows you what is in the database and makes the information accessible to you. You can choose to close the Navigation Pane if you need more of the Access window to complete specific tasks.
To open and close the Navigation Pane
v Click the Shutter Bar Button on the right side of the Navigation Pane.
v The Navigation Pane appears as a single column when it is collapsed.
Database-Basic Concept
Before the working with database, it is necessary to understand about some basic concept and database related terms that are discussed below.
DBMS (Database Management System)
DBMS (Database Management System) is a program which retrieves, modifies and stores the data in database on request. A complete DBMS includes software and hardware utilities, procedures, data and users. The following are the different types of users who can retrieve or access the data from database.
End User
End user is a person who interacts directly with the DBMS through client software, such as, accountants. The end user should know the application and use of database.
DBA (Database Administrator)
Database administrator is a person responsible for implementing the database management system -within an organization.
Application Programmer
Application programmer is a person who develops the applications that is used for interacting with the data.
RDBMS (Relational Database Management System)
The RDBMS (Relational Database Management System) is the advanced form of DBMS. In RDBMS data is stored in the form of table. Each table has unique name. A table contains one or more rows and columns. The total number of attributes or columns which compromise a table is called as the degree of the table. RDBMS creates relationship between two or more tables.
The following are some of the key terms in RDBMS:
Tuple
A Tuple represents an object and information about that object. It contains the data corresponding to an entity. The number of tuples in a relation is called the cardinality of the relation.
Table
Table is a collection of rows and columns that contains data. A column contains same type of data, such as class name, table does not contain marks.
Attribute
Attribute is the property of an entity that differentiates it from other entities and provides information about the entity.
Domain
Describes the set of possible values for a given attribute. Any value that appears under a column must belong to its domain.
While designing the tables of a database, you need to enforce the data integrity in the database. Enforcing the data integrity ensures that data in the database is valid and correct. You enforce the data integrity in a database by using the following types of keys:
Candidate key
Candidate key is an attribute that contains unique value. The candidate key has the maximum eligibility to make primary key.
Unique Key
It is an attribute that contains unique value. Unique key can contain NULL value.
Primary Key
Primary key is an attribute that contains unique value. The main difference between primary key and unique key is that primary key cannot contain a NULL value.
Foreign Key
Creates relationship between two or more table. Basically it is an attribute that appears as a candidate key in one table and primary key in another table.
Designing a Database
Database designing requires a basic goal for the purpose of designing the database to remove the redundancy and errors. The following aspects are required to take decisions while designing database.
Determining the Types of Information Retrieving From the Data-base
A database designer should prepare themselves before the designing of the database for which types of data is required to retrieve from the database.
Determining Tables that a Database User is required
Table in a database is most important element which allows a user to create a well organized database and also for maintain information of each entity independently.
Determining Fields that a User is required
A user can decide the type and number of fields required for the tables in a database. The table in a database contains information about a single entity and this information is inserted in the fields of the table.
Identifying Primary Key in a Table
A user is required to identify a primary key for each table in database. It helps to reduce data duplication in a table and also used to connect one table to another table.
Data validation
Data validation is a very important database concept. It is the process by which Access tests the data that is being entered into the database to make sure it is in an acceptable, or valid, format.
Imagine one of your database users has entered an order date of January 4, 2017, in the month/date/year format as 01/04/2017. Another user has entered an order placed on that same date in the day/month/year format as 04/01/2017. If the database is tracking all sales for the month of January 2017, it may not show both orders as placed in January, even though both were placed on the same date.
Access 2013 allows you to set field properties and data validation rules to force the person entering data to follow a specific format.
Data types and validation rules
Data validation begins when data types are set during the process of building tables and fields. For example, if a field data type had been set to Currency and a text value is entered in that table field during data entry, Access will not accept an invalid format and will display a validation error, like the one below.
Data validation is accomplished by setting data validation rules and other field properties for various fields.
Setting data validation rules
Validation rules work most easily with numeric fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with or, as seen above.
Validation text
Validation text is a specialized error message that you can set to have Access tell the I person entering data the specific way you would like that person to enter it.
To set the Validation text, enter the error message exactly as you want it to appear to your user in the row directly beneath the Validation Rule row in the Field Properties section of Design View. For the validation rule that we set for Category you would set the validation text like this:
The following image shows the resulting error message that the user would see when the Category validation rule has been broken:
Using field properties to ensure data integrity
Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.
You need to login to perform this action.
You will be redirected in
3 sec
