This chapter is from the book
This chapter is from the book
This chapter is from the book
A Brief Introduction to Access
In case you are not very familiar with the workings of Microsoft Access, let's take a few minutes to go over the basic tools available in the Access interface.
Learning Access
Although we will be walking through the process of building a database in Access, if you are not familiar with the menus and features, it is a good idea for you to start with a book that focuses strictly on Access. There are several good books on learning Access including Microsoft Access 2000—Illustrated Complete (Course Technology, 2001) by Lisa Friedrichsen and Elizabeth Eisner and Microsoft Access Version 2002 Step by Step (Microsoft Press, 2001) by Online Training Solutions Inc.
To start our brief tour, open the application on your local computer and let's walk through the fundamental objects created within Access.
The Database Window
The Database window, shown in Figure 3.2, provides an easy-to-use interface to create, edit, and delete database objects. An Access database is more than just a bunch of data stuffed into a single file.
)
Figure
3.2 The Database window provides access to the various database objects
that can be created and manipulated from within Access.
Instead, an Access database is a collection of objects that store your data, represent the data in different views, and contain information about how each of the objects should interact with one another.
Tables
A table is the fundamental building block of any database because it is where the data is stored. The table consists of fields and records—fields specify the nature of the information that is being stored and rows contain a single record of data. For instance, in Figure 3.3, the first row contains information about Nancy Davolio. The fields show us that the table contains her employee ID, last name, first name, address, telephone number, and so on.
Using the table view of Access, you can add records to a table, edit the data, or remove the record altogether.
)
Figure
3.3 The table view allows us to see the rows and columns that make up our
table.
Queries
As the number of records in your table grows, so will the difficulty in finding and editing data located within it. Think of a query as a better way of organizing the data contained in your table for a specific purpose.
For instance, suppose you have a table that contains quite a few records, similar to the one shown in Figure 3.4, but you only want to see the records of people who live in Tennessee.
To accomplish this, you can build a query that scans the data and only returns the information that meets the criteria. As shown in Figure 3.5, designing a query that (in plain language) says, "Show me all the records where the state field has TN in it," will accomplish your goal.
)
Figure
3.4 The table view can sometimes make it difficult to find the data you
are looking for.
)
Figure
3.5 A query can help you quickly find the data you are seeking.
Forms
Entering data into your database can quickly become an eye-straining task. Staring at all those columns, rows, and gridlines definitely isn't the most visually appealing way to populate your database. The alternative to entering data directly into the table is to build a custom form that is easier on the eyes. A simple form, shown in Figure 3.6, allows you to enter the demographic data for each of your users.
)
Figure
3.6 A form can make it easier to input and update data.
When a database is used strictly as the back end of a Web application, the database forms are not used much because the Web pages you build to interact with the tables serve as your forms.
Reports
Although a query is a much more manageable way of viewing your data, it still leaves something to desire visually. Reports, on the other hand, allow you to customize the way your data is presented in printable format. Reports draw their information from a table or a query and organize the information into an attractive layout that you can customize. A sample report, shown in Figure 3.7, represents the query of users in Kentucky in a format that is more visually appealing.
)
Figure
3.7 Reports provide a custom view of your data that is easily printed.
Pages
One of the newer features within Access is the Data Access Page component. Data Access Pages are Active Server Pages that allow you to display your forms on the Web. Through these pages, you can interact with your database in a manner similar to a small Web application.
Relationships
Although technically not an object that is stored within an Access database, the Relationships tool, shown in Figure 3.8, is an extremely important feature within Access and certainly deserves an introduction.
)
Figure
3.8 The Relationships window helps you avoid storing duplicate data.
One of the biggest goals in constructing a database is to avoid storing duplicate data. If you have duplicate data located within your database, and one piece of data is updated and the other is not, information within your database is now incorrect. To avoid storing duplicate data, you build tables that relate to one another. For instance, suppose that in addition to your users table you add a purchases table that stores all of the items that each user buys, the quantity he purchased, and the date the transaction was completed. Without a relationship and a set of rules to maintain referential integrity, you could potentially add records to the purchases table without relating those records to the person who purchased them. The result would be orphaned data. As you will see when we build our own database, creating relationships within Access is a very easy process that will ensure your database functions smoothly.