Computer Information Systems Department
Zicklin School of Business, Baruch College, CUNY
It is assumed that users of this tutorial are proficient in working with Microsoft Windows '95, Windows '98, Windows NT, Windows 2000 or Windows XP and with MS Excel. This includes the use of the keyboard and mouse. The tutorial is based on Microsoft Access which is part of the Microsoft Office Professional suite that also includes MS Excel, MS Word and MS Powerpoint.
The original tutorial was developed specifically for MS Access '97 (part of the MS Office '97 release). Now that MS Access 2000 is widely available, this tutorial has been ammended to include explanations of where Access '97 and Access 2000 differ. Most of the basic features and functions are the same between the two versions.
The tutorial begins with a brief overview of Relational Databases. The majority of database management systems in use today are based on what is called the relational database model. Access is a relational database management system. We then describe a business example and give an outline for the database and applications we wish to develop. In the sections that follow, we give step-by-step instructions for creating the tables, data entry forms, reports and queries for the application.
The student should have a new, formatted floppy disk placed in the A: drive of the PC. This tutorial assumes the floppy disk used is the A: drive. If the student wishes to use another drive (such B: or C:) then simply substitute the desired drive letter accordingly.
In the late 1970s, the relational database model which originated in the academic research community became available in commercial implementations such as IBM DB2 and Oracle. The relational data model specifies data stored in relations that have some relationships among them (hence the name relational).
In relational databases such as Sybase, Oracle, IBM DB2, MS SQL Server and MS Access, data is stored in tables made up of one or more columns (Access calls a column a field). The data stored in each column must be of a single data type such as Character, Number or Date. A collection of values from each column of a table is called a record or a row in the table.
Different tables can have the same column in common. This feature is used to explicitly specify a relationship between two tables. Values appearing in column A in one table are shared with another table.
Below are two examples of tables in a relational database for a local bank:
Customer Table
| CustomerID | Name | Address | City | State | Zip |
|---|---|---|---|---|---|
| Number | Character | Character | Character | Character | Character |
| 1001 | Mr. Smith | 123 Lexington | Smithville | KY | 91232 |
| 1002 | Mrs. Jones | 12 Davis Ave. | Smithville | KY | 91232 |
| 1003 | Mr. Axe | 443 Grinder Ln. | Broadville | GA | 81992 |
| 1004 | Mr. & Mrs. Builder | 661 Parker Rd. | Streetville | GA | 81990 |
Accounts Table
| CustomerID | AccountNumber | AccountType | DateOpened | Balance |
|---|---|---|---|---|
| Number | Number | Character | Date | Number |
| 1001 | 9987 | Checking | 10/12/1989 | 4000.00 |
| 1001 | 9980 | Savings | 10/12/1989 | 2000.00 |
| 1002 | 8811 | Savings | 01/05/1992 | 1000.00 |
| 1003 | 4422 | Checking | 12/01/1994 | 6000.00 |
| 1003 | 4433 | Savings | 12/01/1994 | 9000.00 |
| 1004 | 3322 | Savings | 08/22/1994 | 500.00 |
| 1004 | 1122 | Checking | 11/13/1988 | 800.00 |
The Customer table has 6 columns (CustomerID, Name, Address, City, State and Zip) and 4 rows (or records) of data. The Accounts table has 5 columns (CustomerID, AccountNumber, AccountType, DateOpened and Balance) with 7 rows of data.
Each of the columns conforms to one of three basic data types: Character, Number or Date. The data type for a column indicates the type of data values that may be stored in that column.
In some database imploementations other data types exist such as Images (for pictures or other data). However, the above three data types are most commonly used.
Notice that the two tables share the column CustomerID and that the values of the CustomerID column in the Customer table are the same the values in the CustomerID column in the Accounts table. This relationship allows us to specify that the Customer Mr. Axe has both a Checking and a Savings account that were both opened on the same day: December 1, 1994.
Another name given to such a relationship is Master/Detail. In a master/detail relationship, a single master record (such as Customer 1003, Mr. Axe) can have many details records (the two accounts) associated with it.
In a Master/Detail relationship, it is possible for a Master record to exist without any Details. However, it is impossible to have a Detail record without a matching Master record. For example, a Customer may not necessarily have any account information at all. However, any account information must be associated with a single Customer.
Each table also must have a special column called the Key that is used to uniquely identify rows or records in the table. Values in a key column (or columns) may never be duplicated. In the above tables, the CustomerID is the key for the Customer table while the AccountNumber is the key for the Accounts table.
In this section, we will outline a business example that will be used as a basis for the examples throughout the tutorial. In organizations, the job of analyzing the business and determining the appropriate database structure (tables and columns) is typically carried out by Systems Analysts. A Systems Analyst will gather information about how the business operates and will form a model of the data storage requirements. From this model, a database programmer will create the database tables and then work with the application developers to develop the rest of the database application.
For this tutorial, we will consider a simple banking business. The bank has many customers who open and maintain one or more accounts. For each Customer, we keep a record of their name and address. We also assign them a unique CustomerID. We assign this unique identifier both for convenience and for accuracy. It is much easier to identify a single customer using their CustomerID rather than by looking up their full name and address. In addition, it is possible for the bank to have two customers with the same name (e.g., Bill Smith). In such cases, the unique CustomerID can always be used to tell them apart.
In a similar fashion, all accounts are assigned a unique account number. An account can be either a checking account or a savings account. Savings accounts earn interest but the only transactions allowed are deposits and withdrawals. Checking accounts do not earn interest. We maintain the date that the account was opened. This helps us track our customers and can be useful for marketing purposes. Finally, we maintain the current balance of an account.
In the previous section, we gave the structure and some sample data for the Customer table and the Accounts table. These will be used to support the data storage part of our Banking application.
In any database application, each of the tables requires a means to get data into them and retrieve the data at a later time. The primary way to get data into tables is to use data entry forms. The primary ways to get data back out of tables or to display data in tables are to use queries or reports.
For this tutorial, we will create a data entry form for each table, a query for each table and a report for each table.
In the following sections, we will first introduce how to start Access and how to create a new database.
As with most Windows 95/98/NT/2000 programs, Access can be executed by navigating the Start menu in the lower left-hand corner of the Windows Desktop. A view of a Windows Desktop is given here:
|
|
To start Access, click on the Start button, then the Programs menu, then move to the MS Office menu and finally click on the Microsoft Access menu item. The MS Office Professional menu is shown below.
|
Note that this arrangement of menus may vary depending on how MS Office was installed on the PC you are using.
Once Access is running, an initial screen will be displayed:
From this initial screen, the user can create a new database (either blank or with some tables created with the database wizard), or open up an existing database.
In general, the first time one begins a project, a new, blank database should be created. After that point, use the Open existing database option to re-open the database created previously.
Warning - If you have previously created a database, and then create it again using the same name, you will overwrite any work you have done.
For the purposes of this tutorial, if you are going through these steps for the first time, choose the option to create a new, blank database as shown in the above figure.
By selecting Blank Database and clicking on the OK button, the following screen will appear in order to give the new database a file name. Fill in File Name as a:\bankdb.mdb and click on the Create button to create the database as in the following figure:
In the above file name, the a:\ indicates that the new database will be created on the A: disk drive. bankdb is the name chosen for this particular database and .mdb is the three letter extension given for Microsoft DataBase files.
It is advisable to keep the name of the database (bankdb in the above example) relatively short and do not use spaces or other punctuation in the name of the database. Also, the name of the database should reflect the database's contents.
Once the new database is created, the following main Access screen will appear:
| MS Access '97 | MS Access 2000 |
|---|---|
| 
|
The two main features of this main screen are the menu bar that runs along the top of the window and the series of tabs in the main window. The menu bar is similar to other Microsoft Office products such as Excel. The menus include:
The tabs in the main window for the database include:
In MS Access 2000, these tabs appear along the left hand side of the window by default. MS Access 2000 also adds some selections such as Web Pages and Favorites (not covered in this tutorial).
This tutorial focuses on the first four tabs: Tables, Queries, Forms and Reports.
To open an existing database, choose Open an Existing Database, highlight More Files... and click on the OK button. Then navigate to the A: drive, highlight the existing database file on the floppy disk and click the OK button again to open the database.
Tables are the main units of data storage in Access. Recall that a table is made up of one or more columns (or fields) and that a given column may appear in more than one table in order to indicate a relationship between the tables.
From the business example discussed earlier, we concluded that two tables would be sufficient to store the data about Customers and their bank Accounts. We now give the step-by-step instructions for creating these two tables in Access.
There are a number of ways to create a table in Access. Access provides wizards that guide the user through creating a table by suggesting names for tables and columns. The other main way to create a table is by using the Design View to manually define the columns (fields) and their data types.
While using the wizards is a fast way to create tables, the user has less control over the column names (fields) and data types. In this tutorial, we will describe the steps to create a table using the Design View. Students are encouraged to experiment on their own with using the Create Table wizard.
Then click on the OK button.
For Access 2000, double click on the "Create Table in Design View" item.
Note that the default name given for the table is Table1. In a later step, we will assign an appropriate name for this table.
Fill in the information for the fields as follows:
| Field Name | Data Type | Description |
|---|---|---|
| CustomerID | Number | The Unique Identifier for a Customer |
| Name | Text | The Name of the Customer |
| Address | Text | The Address of the Customer |
| City | Text | The City of the Customer |
| State | Text | The home State of the Customer |
| Zip | Text | The Zip Code of the Customer |
A figure showing the design view with the new table definition filled in is given below:
Notice that a small key appears next to the
field name on the left side.
Note: To remove a primary key, simply
repeat this procedure to toggle the
primary key off.
When defining the fields (columns) for a table, it is important to use field names that give a clear understanding of the data contents of the column. For example, does the field CNO indicate a Customer Number or a Container Number ?
Field names in Access can be up to 64 characters long and may contain spaces. However, the use of spaces in field names and table names is strongly discouraged. If you wish to make field names easier to read, consider using an underscore character to separate words. However be certain no spaces appear before or after the underscore.
The following table summarizes some different ways to give field names:
| Description | Bad | Good |
|---|---|---|
| Unique identifier for a customer | CID | CustomerID or Customer_ID |
| Description for a product | PDESC | ProductDescription |
| Employee's home telephone number | Employee_home_telephone_number | HomePhone |
| Bank account number | BA# | AccountNumber |
| Field Name | Data Type | Description |
|---|---|---|
| CustomerID | Number | The Unique Identifier for a Customer |
| AccountNumber | Number | The Unique Identifier for a Bank Account |
| AccountType | Text | The type of account (Checking, savings, etc.) |
| DateOpened | Date | The date the account was opened |
| Balance | Number | The current balance (money) in this account (in $US) |
A figure showing the design view with the new table definition filled in is given below:
Data can be added, deleted or modified in tables using a simple spreadsheet-like display. To bring up this view of a single table's data, highlight the name of the table and then click on the Open button.
In this view of the table, shown in the figure below, the fields (columns) appear across the top of the window and the rows or records appear below. This view is similar to how a spreadsheet would be designed.
Note at the bottom of the window the number of records is displayed. In this case, since the table was just created, only one blank record appears.
To add data to the table, simply type in values for each of the fields (columns). Press the Tab key to move between fields within a record. Use the up and down arrow keys to move between records. Enter the data as given below:
| CustomerID | Name | Address | City | State | Zip |
|---|---|---|---|---|---|
| 1001 | Mr. Smith | 123 Lexington | Smithville | KY | 91232 |
| 1002 | Mrs. Jones | 12 Davis Ave. | Smithville | KY | 91232 |
| 1003 | Mr. Axe | 443 Grinder Ln. | Broadville | GA | 81992 |
| 1004 | Mr. & Mrs. Builder | 661 Parker Rd. | Streetville | GA | 81990 |
To save the new data, pull down the File menu and choose Save.
To navigate to other records in the table, use the
navigation bar at the
bottom of the screen: 
To modify existing data, simply navigate to the record of interest and tab to the appropriate field. Use the arrow keys and the delete or backspace keys to change the existing data.
To delete a record, first navigate to the record of interest. Then pull down the Edit menu and choose the Delete menu item.
To close the table and return to the Access main screen, pull down the File menu and choose the Close menu item.
Note that when entering the dates, type in the full four digits for the year. By default, Access only displays the last two digits of the year; however, all four digits are stored in the table.
Be sure to save the data when you are done. The figure below shows the Accounts table and data as it should appear when you are done with this exercise.
At this point in the tutorial, we have created two tables, Customers and Accounts, and added data to each one. In the subsequent sections, we will cover how to query and report on the data in the tables and how to create a user-friendly data entry form using the Access wizards.
To get started, display the Relationships screen by pulling down the Tools menu and selecting the Relationships menu item. The blank Relationships screen will appear as follows:
The next step is to display all of the tables on the relatinoships
screen. Right click anywhere on the Relationships screen and
select the Show Tables... option from the pop-up menu:
When the Show Table dialog box appears, highlight both the Customers table and the Acounts table as shown below and then click on the Add button.
Then click on the Close button to close this dialog box. The Relationships screen will now reappear with the two tables displayed as below:
To connect the Customers table with the Accounts table to form a relationship, click on the CustomerID field in the Customers table and drag it over on top of the CustomerID field on the Accounts table. Upon releasing the mouse button, the Edit Relationships dialog box will appear as below:
Access will do its best to determine the Relationship Type (almost always One-to-Many). For this example, Access knows that CustomerID is a key of the Customer table so it chooses this field as the "One" side. This makes the Accounts table the "Many" side as One customer may have Many accounts.
One additional step to be taken is the check off the box labeled "Enforce Referntial Integrity". This option puts constraints into effect such that an Accounts record can not be created without a valid Customer and Access will also prevent a user from deleting a Customer record if a related Accounts record exists. At this point, click on the Create button to create the relationship. The Relationships screen should reappear with the new relationship in place as follows:
Note the symbols "1" (indicating the "One" side) and the infinity symbol (indicating the "Many" side) on the relationship. Close the relationships screen and select Yes to save the changes to the Relationships layout.
If the relationship does not appear in the above fashion, highlight it and press the delete key to delete it. Then go back to the table design view and make certain that the CustomerID field is designated as the key of the Customers table. Then go back to the Relationships screen and try to recreate the relationship.
To change the design of an existing table (e.g., to add, change or delete a field):
To add, delete or change data in an existing table:
Queries are a fundamental means of accessing and displaying data from tables. Queries can access a single table or multiple tables. Examples of queries for our bank database might include:
In this section, we show how to use the Access Wizards to create queries for a single table and for multiple tables.
Creating a query can be accomplished by using either the query design view or the Query wizard. In the following example, we will use the query wizard to create a query.
Queries are accessed by clicking on the Queries tab in the Access main screen. This is shown below:
To create a new query, click on the New button. The New Query menu will appear as below. Select the Simple Query wizard option and click the OK button.
The first step in the Simple Query wizard is to specify the table for the query and which fields (columns) should be displayed in the query output. Three main sections of this step are:
For this example, pull down the Tables/Queries list and choose the Customer table. Notice that the available fields change to list only those fields in the Customer table. This step is shown below:
From the list of Available fields on the left, move
the Name, Address, City and State fields over
to the Selected Fields area on the right.
Highlight one of the fields and then click on the
right arrow button 
in the center between the two areas.
Repeat this for each of the four fields to be
displayed. When done with this step, the
wizard should appear as below:
Click on the Next button to move to the next and final step in the Simple Query wizard.
In the final step, give your new query a name. For this example, name the query: Customer Address
At this point, the wizard will create the new query with the option to either:
For this example, choose Open the query to view information and click on the Finish button. When this query executes, only the customer's name, address, city and state fields appear, however, all of the rows appear as shown in the figure below:
Close this query by pulling down the File menu and choosing the Close menu item. The Access main screen showing the Queries tab should appear. Note the new query CustomerAddress appears under the Queries tab.
In the following example, we will modify the CustomerAddress query to only display customers in a certain state. To accomplish this, we will make use of the Query Design View.
Open up the CustomerAddress query in the design view by highlighting the name of the query and clicking on the Design button. The design view will appear as in the figure below:
The Query Design view has two major sections. In the top section, the table(s) used for the query are displayed along with the available fields. In the bottom section, those fields that have been selected for use in the query are displayed.
Each field has several options associated with it:
For this example, we will filter the records to only display those customers living in the State of Georgia (GA). We will also sort the records on the City field.
To sort the records on the City field, click in the Sort area beneath the City field. Choose Ascending from the list as shown in the figure below:
To filter the output to only display Customers
in Georgia, click in the Criteria area
beneath the State field and type
the following statement:
= 'GA'
The = 'GA' statement tells Access to only show those records where the value of the State field is equal to 'GA'.
Run the query by pulling down the Query menu and choosing the Run menu item. The output is shown in the figure below:
Finally, save and close this query to return to the Access main screen.
For this exercise, use the Simple Query wizard to create a query on the Accounts table showing just the AccountNumber, AccountType and Balance fields.
The output is shown below:
Close this query by pulling down the File menu and choosing Close.
In the next part of the exercise, we will modify the query to sort the output on the account number and only display the Savings accounts.
Before proceeding with these next instructions, make certain the One-to-Many relationship between the Customers and Accounts table has been created (see section 5.5 Creating Relationships for a review of this process).
To start the process of creating a multiple table query, highlight the Query tab (Access '97) and click on the New button to create a new query. Select the "Simple Query Wizard" option as was done previously. When the simple query wizard appears, select the CustomerID and Name fields from the Customers table, then switch the Tables/Queries selection to the Accounts table and select the CustomerID, AccountType and Balance fields from the Accounts table. The result from this step is down below:
Click the Next button to continue. In the next step of the wizard, an option will appear to provide some level of Summary. For this example, leave the default at "Detail ..." as shown below and then click on the Next button.
In the final step of the wizard, name the query "Customer Accounts Query" and click on the Finish button. The multiple table query results should appear as follows:
As with single table queries demonstrated previously, one can change the query definition in design view by adding filters (e.g., show account information for all customers in 'GA').
The resulting query should appear as follows:
Creating a query using the query wizard:
If querying more than one table, change the Table/Queries: selection to display additional tables and select the necessary fields.
As a final note, Forms and Reports can be created based on existing queries.
Data entry forms are the primary means of entering data into tables in the database. In a previous section, we described how to add data to a table using a spreadsheet-like view of the data. Data entry forms offer a more user-friendly interface by adding labels for each field and other helpful information.
Access provides several different ways of creating data entry forms. These include creating the forms by hand using a Design View as well as a number of wizards that walk the user through the forms creation process. In this section, we cover the basic steps for using a wizard to create a data entry form.
A New Form dialog box will appear with several options for creating a new form. For this tutorial, choose the Form wizard. At the bottom of the dialog box, there is a prompt to supply the name of the table or query to be used for the new form. In this case, select the Customer table as in the following figure and then click on the OK button.
In the next step of the Form wizard, we need to specify the fields from the Customer table that will appear on the form. In this case, we want all of the fields to appear. Move each of the fields from the Available Fields side over to the Selected Fields side as in the following figure. Then click on the Next button.
Forms can have several different layouts or arrangement of the labels and fields on the screen.
For this example, choose the columnar layout as shown in the figure below and click on the Next button.
Access has several sample display styles that determine how the form will appear, including elements such as fonts, colors and the background used in the form. For this example, select the Standard style as shown below and click on the Next button.
As a final step, give this new form the name: CustomerDataEntry and then click on the Finish button as shown below:
The new form will be created by the wizard and then opened. It should appear as in the figure below:
Use the tab key to navigate between fields
in the form. To move to the next or previous record,
use the record navigation bar at the
bottom of the form:
The buttons on the navigation bar perform the following functions:
Go to the first record.
Go to the previous record.
Go to the next record.
Go to the last record.
Go past the last record to add a new record.To close the form and return to the Access main screen, pull down the File menu and choose Close.
To open the form at any time, highlight the form name under the Forms tab on the Access main screen and click on the Open button.
The new form is shown in the figure below:
Close the form and return to the Access main screen, by pulling down the File menu and choosing Close.
In this section we covered the basic steps required to create and run a data entry form. Access provides wizards which are adept at building simple forms with a minimal amount of work. More advanced work on forms would concentrate on using the Design View to change a form's appearance and to add or remove fields and labels once a form is created.
Reports are similar to queries in that they retrieve data from one or more tables and display the records. Unlike queries, however, reports add formatting to the output including fonts, colors, backgrounds and other features. Reports are often printed out on paper rather than just viewed on the screen. In this section, we cover how to create simple reports using the Report wizard.
In this example, we will create a simple report for a single table using the Report wizard. As with the Queries and Forms, we begin by selecting the Reports tab from the Access main screen.
To create a new report, click on the New button. The New Report dialog box will appear as shown below. Select the Report wizard and then select the Customer table as shown below. Then click the OK button.
In the next step of the Report wizard, we need to specify the fields from the Customer table that will appear on the report. In this case, we want all of the fields to appear. Move each of the fields from the Available Fields side over to the Selected Fields side as in the following figure. Then click on the Next button.
In the next step, we have the opportunity to add Grouping Levels to the report. A grouping level is where several records have the same value for a given field and we only display the value for the first records. In this case, we will not use any grouping levels so simply click on the Next button as shown below.
In the next step, we are given the opportunity to specify the sorting order of the report. For this example, we will sort the records on the CustomerID field. To achieve this, pull down the list box next to the number 1: and choose the CustomerID field as shown in the figure below. Then click on the Next button.
The next step is to specify the layout of the report. The three options are:
In the next step, the style of the report can be selected. For this example, choose the Corporate style and click on the Next button to continue.
Finally, give a name for the new report: CustomerReport and then click on the Finish button to create, save and display the new report.
The output from the report is shown in the figure below. Note that on some screens, the last field, Zip, may not display without scrolling over to the right.
Once the report is displayed, it can be viewed, printed or transferred into Microsoft Word or Microsoft Excel. The button bar across the top of the screen has the following functions:
 | Print the report |
 | Zoom into a region of the report |
 | Display the report as one, two or multiple pages |
 | Zoom into or out of the report |
 | Transfer the report into MS Word |
 | Close the report |
To close the report and return to the Access main screen, pull down the File menu and choose Close or click on the Close button.
For this exercise, we will create a report showing all of the Accounts information.
button.
This is shown in the following figure:
Click on the Next button.
Click on the Summary Options button. Choose the Balance field and select the Sum option. Choose the option to show both Detail and Summary data. Then click on the OK button.
Click on the Next button.
The output from the AccountsReport is shown below:
Note the Grouping at the level of the CustomerID and the Sum for each customer's balances.
To close the report and return to the Access main screen, pull down the File menu and choose Close.
As can be seen in the report exercise, there are many ways to create reports to show summarization, sorting and layout of the data. Further study of Reports will show how to modify the layout using the Design View. Students are encouraged to work with the Report wizards to create different styles and types of reports.
Students are encouraged to further their Access knowledge and skills by working through more advanced tutorials and by reading the on-line help and Access documentation.
All products or company names in this tutorial
are used for identification purposes only,
and may be trademarks of their respective owners.
All names and information used as examples in
this tutorial are fictitious.