Database Management System (DBMS) Access

What Is Access?

Microsoft Access is a computer application used to create and manage computer-based databases on desktop computers and/or on connected computers (a network). Microsoft Access can be used for personal information management (PIM), in a small business to organize and manage data, or in an enterprise to communicate with servers.
Access is a powerful database management program used to organize, track, and retrieve data.

In an Access database, data is stored in tables.

Each table contains data that pertains to one particular subject, such as employees or customers.

Access is a relational database management system (RDBMS).

In an RDBMS, a database may contain multiple tables that can be used together.

You can establish relationships to connect the tables, provided the tables have one field in common.

Understanding the Database Window

The Database window allows you to gain access to all the data and objects stored in your database.





The toolbar provides shortcuts for working with objects.


The Objects bar represents the types of objects available in Access.The title bar displays the name and file format of the database.

Objects: The Components of Access

In Access, the term database refers to the raw data as well as the collection of database objects.

The objects provide the structure of an Access database.



You use the objects to enter, manipulate, and extract data.


Objects can be tailored to fit the data that will be stored in the database.


Objects can be modified as the database grows and changes over time.

Views

Access provides several different views for you to use as you work with the objects.


The views vary depending on the type of object open and the kind of work you want to do.



Some views let you work with the object’s content, or data.


Other views let you work with the object’s design, or structure.

The Datasheet view displays data in a column-and-row format, called a datasheet.



Use the Datasheet view to add, delete, and edit the information in a database.



In Design view, you work with the structure of an object, but not the data.


Use the Design view to create new database objects and modify the design of existing objects.

Tables

The table is the basic structure that holds all of the data in an Access database.
 
Tables are made up of fields (columns) and records (rows).



A field contains categories of data.


A record contains the set of fields for one particular entity.

Queries

A query is a question to the database.

A query generally asks for a set of records from one or more tables that meets specific criteria.



Queries let you view and manipulate selected subsets of your data.


A query is a stored question rather than the stored answer to a question.


This means that the results of the query will change if the underlying data in the database changes.




A Select query is used to retrieve data from one or more tables and display the results in a datasheet.


A Parameter query prompts you for the information it needs to retrieve records.



Use a Crosstab query to perform calculations on records and then group the results.


An Action query is used to make changes to several records in just one operation.


A SQL query is created from SQL statements that can be used with Access and other relational databases.


You can view a query using different views.



Use Datasheet view to display the results of a query.


Use Design view to display and modify the underlying structure of a query.


Access also provides other query views.


SQL view


PivotTable view


PivotChart view

 

Forms

 
An Access form is the electronic equivalent of a paper form.


You can use a form to create a custom layout for your data.



Forms have three views.


Use Design view to create a new form or change the structure of an existing form.


Use either Datasheet view or Form view to view, enter, and edit data in forms.


Datasheet view shows several records at one time, while Form view shows only one record at a time.

 

Reports

 
Access reports are based on data from tables or queries.



Reports let you control the size and appearance of data in order to produce presentation-quality output.



You can use three different views to work with reports.


Use Design view to create a new report or change the structure of an existing report.


Use Print Preview to display a WYSIWYG preview of all the data in a report.


Use Layout Preview to view the layout of a report; this view shows only a portion of the data contained in the report.

 

Macros and Modules

A macro is a set of commands called actions that you can create to automate a task.


Macros let you automatically perform a set of actions rather than issuing them separately.


Macros are designed to save time and effort.

Macros are best used for relatively simple tasks, such as opening forms or running reports.

A module is a set of Microsoft Visual Basic program statements, declarations, and procedures that are stored together as a unit.

Data Access Pages

 
A data access page (sometimes called a page) is a Web page that is connected to an Access database.


A data access page allows you to publish live data on the Web.


You can work with data access pages in three views.

Data is displayed in Page view by default when you open a data access page.

Use Design view to change the structure of the page.

Use Web Page Preview to see how the page will look when viewed through a Web browser.

Planning Ahead

 

Creating a database requires careful thought.

Before beginning to build a database, you should answer several questions:



What is the database for and what should the database do?


What categories of information (or fields) are needed to achieve the desired results?


How should those fields be divided into separate tables?


How might these tables relate to each other to use information from two or more of them simultaneously?


 

Determining the Purpose of the Database

 
Before you can configure your database, you need to determine how it will be used.


If one exists, investigate the manual system currently in use.


Review reports and forms that you’ll need to duplicate, and note the items of information they must include.

Talk to people who actually use the database to find out how they use the system and what they need.

If a manual system does not exist, think carefully about what you want the system to do.


Jot down the items of information the system should track.


Sketch the forms and reports you think you’ll need.

Consider how you want to sort and extract data.

Determining the Categories of Information

 
Make a list of all of the categories of information the database should include.


Make sure to break the categories down into their smallest parts.


For example, instead of just one category for a customer’s name, include separate categories for first name and last name.

The list does not need to be in any particular order.

Determining How Many Tables You Need

 
Each table in an Access database should contain information on a single subject.


If you try to include information on more than one subject in one table, you end up with duplicate data, or data redundancy.


When you duplicate data, you do a lot more typing than necessary.

You run the risk of more data entry errors.

The duplicate data wastes storage space.

When you use multiple tables in a database, you avoid data redundancy.

Determining How Tables Will Work Together

 
In order to use two or more tables in combination, they must include a common field.


A relationship is the association between common fields in two tables.


The field that you use as a common field is known as a primary key.

A primary key is a field or set of fields that uniquely identifies each record in a table.

A field that holds information that is not unique, like a name field, shouldn’t be used as the primary key.

 

Creating a Database

Access provides two ways to create a database.

 
You can use the Database Wizard, which uses a template to help you create a database and its objects.


You can create a blank database and then create each database object individually as you need it.

Click New on the Database toolbar.

Click Blank Database in the New File task pane.

In the New Database dialog box, navigate to the drive and folder in which you want to save the database.

Type a name in the File name text box.

Click Create.

Creating Tables

 

To create a table, you must design its structure by identifying the fields in the table and defining the properties of those fields.

Access provides three ways to create tables.


You can use the Table Wizard to create the table from a template.

You can use Design view to create the table from scratch.

You can enter data into a blank datasheet.

Creating a Table With the Table Wizard

 
When you use a wizard, Access displays a series of dialog boxes that prompts you for information.


When you use the Table Wizard to create a table, the first step is to select the fields to be included.


You can remove and rename fields later if you need to.

You can also change the order of the fields.

 

 

Naming the Table and Selecting a Primary Key

 

Once you’ve selected the fields for your table, you need to choose a name for the table.

You also need to choose a field to act as the primary key.


You complete both of these activities in the Table Wizard dialog box.

Type a name for the table.

Click the No, I’ll set the primary key option.

Click Next.

Select the field to be used as the primary key.

Click Next.

 

 

Creating Tables in Design View

When you create tables in Design view, you have more flexibility over the field characteristics than when you use the Table Wizard.




Type the first field name and press Tab.

Click the desired data type and press Tab.

If desired, type a description for the field and press Tab.

Repeat the steps for each field.

You can also use Design view to modify existing tables, whether you created them in Design view or with the Table Wizard.
Double-click the Create table in Design view option in the Database window.

 

Setting Field Properties

After you assign a data type to a field, Access automatically sets default field properties for the field.

The field properties control the way the field looks and behaves.


You can modify the field property settings to fit the data your database will contain.

Click the name of the field you wish to change in the top pane of the Design view window.

Press F6 to jump to the Field Properties pane.

Click the property box that you wish to change.

Type or click a new property.

 
 

Choosing a Primary Key

After you create a table, you need to set a primary key for the table.

In Design view, click the row selector (the small box to the left of a field) of the field you wish to designate as the primary key.


Click the Primary Key button on the Table Design toolbar.

 

Saving a Table

When you create tables with the Table Wizard, Access automatically saves them.

When you create or modify tables in Design view, you need to tell Access to save the table.


To save a table, click the Save button or click Save on the File menu.

The first time you save a table, Access requests a table name.

The next time you save the table, Access uses the name you initially provided.

You can use the Save As command on the File menu to save a copy of the table under a different name.

 

Modifying Tables in Design View

At some point, you may need to make changes to the structure of your table.

You may need to add or delete fields.


You might decide to change the field’s name or data type.

You may decide to reorder the fields.

You can use Design view to change the structure of your table.

 

Creating an Input Mask

Access provides several ways to help you control how information is entered in a field.

You can control data entry by specifying a data type or field size for a field


You can also use an input mask to control data entry.

An input mask is a pattern that you create that specifies what kind of data to enter.

It also specifies the number of characters allowed in a field.

Data entry is easier, because the input mask shows you exactly how to enter the data.

You can use the Input Mask Wizard to create an input mask.

 
 

Entering Records Using a Datasheet

When you are entering a large number of records, a form is usually the best object to use.

For smaller jobs, you can use the datasheet to add, delete, or make minor modifications to records.


It’s easy to use the datasheet to add records.

Type data in a field.

Press Tab to move between fields.

Press Tab after the last field of a record to create a new record, or press the New Record button to move to a new record.

 

 

Defining Relationships Between Tables

After you have created some tables in your database, you should establish relationships between them.

Table relationships allow you to create queries and reports that include data from more than one table.


After you’ve established the relationships, you can print the information for your reference.

Relationship Types

Any two tables can have one of three types of relationships.

In a one-to-one relationship, every record in one table can have one matching record in the other table.


In a one-to-many relationship, every record in the primary table can have many matching records in the related table.

Each record in the related table has just one associated record in the primary table.

The related table links to the primary table through a foreign key.

In a many-to-many relationship, a record in either table can relate to many records in another table.

Referential Integrity

Access uses referential integrity to safeguard your data.




For example, when referential integrity is enforced, you can’t delete a record in a primary table if there are matching records in the related table.
Referential integrity refers to rules that Access enforces to make sure you don’t accidentally delete or modify data that is related to another table.  

 

Displaying Related Records in a Subdatasheet

A subdatasheet allows you to view and edit data in a related table, query, or form.

To view a subdatasheet, click the plus sign in front of any record in a table.


Click the minus sign in front of any record to close a subdatasheet.