Every business generates data, and how a business manages it has a significant impact on performance. The traditional method of organising data was by using the humble pen and paper (no wonder they call it book-keeping), and as businesses grew, the increasing volume and complexity of data demanded drastic measures. With the advent of computers came software capable of managing huge databases. Earlier database management programs were tied to specific architectures; later software was standardised to run on all platforms. Oracle, DB2, Microsoft SQL Server, and MySQL are some enterprise-level data management tools.
But these tools are not entirely suitable for the home and the small office segment, because using them requires knowledge of relational database concepts and query languages, which could mean having to enrol in a course! Besides, using software like these to maintain the data generated by a small business is like using a cannon to kill a rabbit-it’s simply not necessary.
There is therefore the need for a middle-road software that’s simple to use and gets your data-related jobs done. Microsoft Access fits into this category.
Inside Access
Microsoft Access is a database manager. It is part of the Microsoft Office suite and has many versions, the latest being MS Access 2007. Here we have used Access 2003,an earlier version because it has been around for a longer time, and chances are most people haven’t been exposed to the newer edition. That said, the essential elements are the same for the two editions, with the only major difference being the user interface.
Access is a Desktop application, not a client-server one, and hence is not scalable for use over a wide network. It uses tables, forms, queries, and reports to store, manipulate, and access data. Here we are going to describe how to use these data elements to create and manipulate a database consisting of student records for a coaching academy. 
Here’s how to create a database in Access
This is an example; Access can be used by a wide variety of small-and medium-sized businesses like bookshops, travel agencies, clinics, restaurants, etc. to store and handle data. In cases where the database or the number of users becomes too large for Access to handle, it can be upsized by linking the front-end of the database (the user interface) with enterprise-level database management tools (like Microsoft SQL Server, Oracle, or MySQL, like we mentioned).
| Access 2007-What’s New? |
|
Access 2007 is part of the newly-released Microsoft Office 2007, which had been timed for release along with Vista. As with other Office 2007 components, Access 2007 has been packed with a lot of new and useful features. Here is a low-down on some of them: New look and feel: Access 2007 has a completely revamped look and feel. The “Getting started” page is a completely new experience compared to that in the earlier edition. The Ribbon-one of the most widely-talked about features in Office 2007-is used inside Access, replacing all the old-style menus, buttons, and toolbars. The benefit of the Ribbon is you don’t need to dig deep into the menus to find out a particular option; the Ribbon displays it to the user according to the context. You get lots of slick templates to choose from-this gives your user interface that “cool” look. Also, Access now implements the Single Document Interface, where forms and reports open in new tabs. |
Tables
Tables are the backbone of any database management system-data is typically stored in tabular form in a database. This is also the case with Access. To start storing data, open Access and select File > New. Click on Blank database, enter a name for your database, and save it. A Database window pops up showing all the elements in an Access database. 
The database window is your primary handle on Access
You have three options there-Create table in Design View, Create table by using wizard and Create table by entering data. We don’t recommend using the Wizard-it looks simple and is tempting, but you can easily foul up and have to return to square one. Use either the first or the third option. For beginners, the third option might be the best, but once you get the hang of it, you will be benefited by creating your table in Design view.
For our student record database we created two tables-student info and fees, using Design view. A tip: design the layout of your tables on paper before you get cracking at the computer. Think about all the fields you need to put in a single table, and how many tables you need. A database is only as efficient as the table structure.
Each table needs a “primary key.” In plain English, this simply means a unique value-like a serial number or ID-that is not likely to be repeated in the table. So select a field as the primary key, or if you are not too sure, let Access do it for you. Take care with the data types of the fields. You get a short description of each data type, so it’s very unlikely that you’ll use Text as the data type if your field is Date. Browse through the options and select what seems to be the best fit. Give a name for your table and save it. This appears in the Database window.
Click on the newly-created table and enter your data. If you have done your spadework right, the data will fit nicely into their places. If you goofed up, sweat not: go to the Database window, right-click on the table, select Design view from the menu, and tweak the field types. If you need to add or delete columns, you can do so from inside the table.
You can add data to your table-or delete data-simply by choosing to edit it.
The student info table, one of the two tables used in our sample database
The second table used by us, the Fees table
Queries
Queries are used to extract data from databases. In Access, queries are of five types-select queries, action queries, parameter queries, crosstab queries, and SQL queries. Most users deal with select queries-those that retrieve required data from your tables and display them in spreadsheet view. Action queries do jobs like deleting or updating records. Parameter queries are interactive: a single query can give different results, depending on your parameter. Crosstab queries are used for restructuring data and doing operations like addition, calculating averages, etc. SQL queries are written in SQL (Structural Query Language) and are the most powerful of them all. In fact, all the other types of queries are converted by Access into the equivalent SQL forms and then executed. However, SQL queries are not suitable for the lay user because its grammar and syntax is complex.
You can edit the data in tables just like you
would in an Excel worksheet
From the Database window, click on the Query tab and select New. You get five options-Create query in Design view, and create query using the Wizards (for Simple Query, Crosstab Query, Duplicate Query, and Unmatched Query). Parameter queries and SQL queries can be created using the Design view. For our student database, we have used the Design view to create queries, though the job can be done using the Wizard too.
Click on Design view. Select the tables that contain the data for your query. In fact, you can also create sub-queries by selecting the queries you created earlier. Select the requisite data fields from the tables, add a sorting order if necessary, and save and name your query. That’s all-click on the created query in the Database window and view the results. As an example, we have used both the Fees and the Student Info table and selected appropriate fields to create a query that relates student ID, name, subjects taken, and month joined.
Parameter queries can be created by adding an expression typed in square brackets with a prompt in the Criteria cell of the Field to be used as the parameter. For example, to find students who have fees less than a particular amount, type < [Fees lesser than:] in the Fees field of your table. When you click on the saved query, a message box pops up prompting you to enter a value for the chosen parameter. So if you need to find students who pay fees less than 500, enter 500 as the parameter. 
This query was created in the Design view…
And here are the results of the query
Imagine building the same query every month-just use
Reports for…well…regular reports!
Forms
These represent another data element in Access. Forms are mainly used to enter data into tables, and to view data. They can be used to edit data as well. They are also used to make a splash-screen or a “switchboard”-an entry screen where tabs and buttons guide you to the major parts of the database. Well-designed forms serve to demystify a database for first-time users who might be apprehensive of using one. Besides, they are easy on the eyes.
Forms are of five types-columnar, tabular, datasheet, pivot table, and pivot chart. Pivot tables and pivot charts are like bar diagrams-they are used to efficiently summarise and visually display large amounts of data according to set parameters. To create a form, you can use either Design view or the Wizard. For our sample database, we chose the Wizard. 
The Form Wizard takes some of the headache out of form-making
Select your data fields from the field list. You can use tables or queries as the data source, but make sure the fields come from tables and queries where the fields have certain relations. Table relations may be one-to-one, many-to-one, or many-to-many: these refer to the different permutations in which fields in one table relate with fields in other tables. For instance, one single student might take up more than one subject. This would create a one-to-many relationship. 
The Student Info Form
There is also the concept of subforms in Access. A subform is a smaller form that can be embedded in a larger form. Subforms are particularly suitable for complex databases. If you want to create subforms or create other types of forms using the Wizard, select New in the Database window. Choose your form type and the table or query from where your form gets the data. In our database, we used forms to enter data for the student info table. Click on Create form by using Wizard and choose Student info table as the source. Choose all the fields from the source table, select layout and style, and click Finish. You will be able to check out the data in the Student info table, edit it, and add new data. Any changes or additions you make will be reflected in the Student info table.
Reports
As the name indicates, reports are usually used to represent data in easily viewable or printable formats. Tables and queries are used as data sources for reports. You can design reports to represent the way you want to display the data: sort data in alphabetical or in increasing / decreasing order, and display sum, average, maximum, and minimum of data. If the report runs into multiple pages, you can add page numbers. Access gives you the option to display data in visual form.
For beginners, the best way of creating reports is using the Wizard. This way you can create autoreports-using a single table or query as the data source-in columnar and tabular forms, or you can pick and choose your fields using the Report Wizard. There
are Wizards for representing data in visual format (the Chart Wizard) and another to print out labels.
In the Report tab, click New and choose the type of report you want. For our student database, we used the Autoreport Wizard. We chose the data source as the query for the number of students joining in January. After clicking Finish, we got a nice, printed report. 
A sample view of the Report Wizard
If you don’t want to use all the fields or if you want to customise the look and feel of your reports, use the Report Wizard. Follow the instructions and you will get the result. To export reports to other formats like .html or .doc, right-click on the saved report, select Export from the menu, and select the required format. The Chart Wizard has plenty of options (bar chart, column chart, 3D area chart, and more) to visually represent your data. 
You get several visual options for your reports
Use Data Access Pages to update your web site every
time your database changes
Pages
Imagine a business with a Web site containing data that changes regularly. The data on the Web site needs to be updated regularly. If the data volume is small, the data can be manually updated. But for larger databases, this method is infeasible. Data Access Pages lets you connect your database to the Internet so that any changes made in the database are automatically reflected on your site. You can also edit an existing Web page. Pages are displayed using a combination of technologies like ODBC, Active Server Pages, IIS, and DHTML. 
The Page Wizard in Access
Creating a page is simple. As with other data elements in Access you can create pages in design view as well as using a wizard. We recommend the Wizard. Click on New tab in the window and select the type of wizard you want. We selected the Page Wizard. The data sources for Pages are tables or queries. As in other Wizards, the process of creating a page is the same. The created pages are stored by default in C:My Documents. If you intend to display the pages over a network, specify a network path.
A note here: pages created by Access are only visible if you use IE 5.0 or above. If you use other browsers such as Firefox or Opera, only the title of the page will be visible, not the data elements. This is a big drawback; those browsers are widely used today.
Macros And Modules
These are advanced features in Access. Both add functionality to your other data items like forms and reports.
Macros trigger specific events when run. For example, if you open a database, a switchboard that enables you to navigate to previously-constructed data elements like reports, forms, etc. can be displayed. This is done by a macro. If there is a Print button in your report or form, it is a macro that does the job when the button is clicked.
Modules are collections of user-defined routines, subroutines, and procedures that automate many tasks. Modules, once written, can be used with any data object in the database. For example, a simple three-line module can be written to launch a Word application from inside Access at the click of a button. You could write a module that can connect Oracle to Access or test whether a string contains alphanumeric characters.
The Big Picture
Microsoft Access gives a lay user the ability to create databases that have the structural robustness of databases built using enterprise level tools like Oracle. It is also extensively used by developers to develop database models for rapid application development. Access is packaged with an eclectic collection of templates; templates are also available online, from Microsoft’s Web site and also elsewhere. A sample database called Northwind comes with every copy of Access. This database illustrates all the functionalities of Access. Take some time to go through the Northwind database. Believe us, it will be a wise investment.
After you are comfortable with the basics of Access, you will be able to explore features such as adding security options, using macros to automate tasks, and more. You could also look up sample databases on the Web (www.databaseanswers.org/data_models is a very good repository) to see how they are structured.
There are similar database management tools, both paid and free, like Lotus Approach and OpenOffice.org Base. Access, however, is most widely used in this segment because of the ubiquity of the Windows / Office duo. The idea of databases and anything associated with them may sound geeky, but as you get more and more comfortable with Access, you will start to discover the sea change Access will bring to the way in which you deal with all those mounds of data.