About Access databases

A database is a collection of information that’s related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If your database isn’t stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you have to coordinate and organize yourself.

For example, suppose the phone numbers of your suppliers are stored in various locations: in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier’s phone number changes, you might have to update that information in all three places. In a database, however, you only have to update that information in one place — the supplier’s phone number is automatically updated wherever you use it in the database.

Access Database Files

Using Microsoft Access, you can manage all your information from a single database file. Within the file, you can use:

Tables to store your data.
Queries to find and retrieve just the data you want.
Forms to view, add, and update data in tables.
Reports to analyze or print data in a specific layout.
Data access pages to view, update, or analyze the database’s data from the Internet or an intranet.

- Store data once in one table, but view it from multiple locations. When you update the data, it’s automatically updated everywhere it appears.

- Display data in a query

- Display data in a form

- Display data in a report

- Display data in a data access page

Tables And Relationships

A Table is a fundamental building block of an Access Database. All Databases must have at least one table because this is where the data is stored. Tables are grids of rows and columns. Each column in a tale is called a field. Each field contains a specific type of information such as first name, last name, phone number, e-mail, date of joining etc. Each row many columns (fields) contains information about once specific item, person or transaction.

To store your data, create one table for each type of information that you track. To bring the data from multiple tables together in a query, form, report, or data access page, define relationships between the tables.

- Customer information that once existed in a mailing list now resides in the Customers table.

- Order information that once existed in a spreadsheet now resides in the Orders table.

- A unique ID, such as a Customer ID, distinguishes one record from another within a table. By adding one table’s unique ID field to another table and defining a relationship, Microsoft Access can match related records from both tables so that you can bring them together in a form, report, or query.

Queries

A Query is a filter through which data is evaluated. We can define filter criteria in a query and only those records which meet these criteria are displayed. There are three types of queries:

ü The most common type of query is a select query which displays a subset of the entire data, sorted and selected using the criteria we specified. For Example, show all student of BHCM 1^st semester.

ü Action Queries

ü Cross Tab Queries

To find and retrieve just the data that meets conditions that you specify, including data from multiple tables, create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data.

Forms

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.

Types of forms:

ü Columnar (Fields arranged as columns)

ü Tabular ( Displays data in row and column format similar to a datasheet.)

ü Datasheet (Identical to tabledatasheet)

ü Main/Subform (Displays oth main and related table data)

ü Chart (Displays information with a graph)

ü Pivot Table Form

- A table displays many records at the same time, but you might have to scroll to see all of the data in a single record. Also, when viewing a table, you can’t update data from more than one table at the same time.

- A form focuses on one record at a time, and it can display fields from more than one table. It can also display pictures and other objects.

- A form can contain a button that prints, opens other objects, or otherwise automates tasks.

Reports

A Report summarizes data in a format suitable for publishing i.e. to view data on the screen onto print it on a printer or to publish it on the web. Types of report in access are:

ü Design View (Begins with a blank page. We lay out the report the way we want using the design tools in the toolbox)

ü Report Wizard ( Choice of fields, sort criteria, and report layout used. Creates tabular or columnar report.)

ü Columnar AutoReport

ü Tabular AutoReport

ü Chart Wizard

ü Label Wizard

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.

- Use a report to create mailing labels.

- Use a report to show totals in a chart.

- Use a report to calculate totals.

Data Access Pages

To make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis, use a data access page. Microsoft Access retrieves the data from one or more tables and displays it on the screen with the layout that you created on your own in Design view, or with the layout you chose in the Page Wizard.

- Click the expand indicator …

- … to display the data and record navigation toolbar for the next level of detail.

- Use the record navigation toolbars to move to, sort, and filter records, and to get Help.

Example of simple Database based Applications are:

- Library Management System (Tables:- students, staffs, books, issue, retrieved, etc)

- Banking System (Tables:- customers, employee, deposit, withdraw, loan, etc)

- College Information (Tales:- students, teachers, results, fees, library_info, etc)

- Airline Ticketing (Tables:- aircraft_info, customer_detail, route_detail, etc)

- Bus Reservation System

- Accounting System

Now, here, First we plan and create our Database Structure – identifying which all fields are required, which field will contain what type of data (numbers, text, date, alphanumeric, data etc) and what will be the maximum width of each field. Once we have decided this structure we can then create a table either in the design mode or we can use the table wizard and use,

Once we have created the table we can then use the forms’ wizard to create user friendly and aesthetically pleasing layouts for data entry. Creating form s for data entry also ensures that the user inputs only the right kind of information and both, a data entry error as well as typing work is minimized.

Once the forms have been created and relevant data has been entered, using these we can then use the report wizard to generate any kind of report. Using reports we can not only organize and present our data in a more meaningful manner but can also use various standard functions like subtotals, totals, sorting etc, to summarize our data.

About primary keys

The power of a relational database system(RDBMS) such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null values from being entered in the primary key fields.

There are three kinds of primary keys that can be defined in Microsoft Access:

AutoNumber Primary Key

An AutoNumber field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don’t set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key.

Single-Field Primary Keys

If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. You can specify a primary key for a field that already contains data as long as that field does not contain duplicate values or Null values.

Multiple-Field Primary Keys

In situations where you can’t guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. The most common situation where this arises is in the table used to relate two other tables in a many-to-many relationship. For example, a students_list table can have primary key columns can be like the combination of Class and RollNo.

About relationships in an Access database

After you’ve set up different tables for each subject in your Microsoft Access database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you’ve done that, you can create queries, forms, and reports to display information from several tables at once. For example, Doctor Table and Patient Table.

For More Information go for MS-Access Help

DBMS

RDBMS

Parts of Access Window (and practice practical operations)

Creating New Database

Tables (fields, data type, unique fields, relationships)

Relationships (1:1, 1:M, M:1, M:M)

Primary Key

Creating Table, Forms, Queries, and Reports.