Zicklin School of Business - Baruch College
City University of New York
Microsoft Access Tutorial
Richard Holowczak
richard_holowczak@baruch.cuny.edu
Computer Information Systems Department
Zicklin School of Business, Baruch College, CUNY
--------------------------------------------------------------------------------
Table of Contents
1 Introduction to the MS Access Tutorial
1.1 Intended Audience
1.2 Pre-Requisite Knowledge
2 Brief overview of Relational Databases and Database Applications
3 A Business Example
4 Starting Microsoft Access
4.1 Review of Starting Microsoft Access
5 Creating and Viewing Tables
5.1 Creating a Table Using the Design View
5.2 Exercise: Creating a Table
5.3 Viewing and Adding Data to a Table
5.4 Exercise: Adding Data to a Table
5.5 Creating Relationships Between tables
5.6 Review of Creating and Viewing Tables
6 Creating and Running Queries
6.1 Single Table Queries
6.2 Exercise: Single Table Queries
6.3 Multiple Table Queries
6.4 Exercise: Multiple Table Queries
6.5 Review of Creating and Running Queries
7 Creating and Running a Data Entry Form
7.1 Creating a Single Table Form using the Wizard
7.2 Exercise: Creating a Single Table Form
7.3 Review of Creating and Running a Data Entry Form
8 Creating and Running a Report
8.1 Creating a Single Table Report using the wizard
8.2 Exercise: Creating a Single Table Report
8.3 Review of Creating and Running a Report
9 Summary
--------------------------------------------------------------------------------
1 Introduction to the MS Access Tutorial
Welcome to the MS Access tutorial. This tutorial is designed to get the user up and running with MS Access (henceforth simply "Access") in a rapid fashion. The four basic modules of Access are demonstrated: Tables, Forms, Reports and Queries. A business example is discussed first which provides a background for developing a simple database.
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.
1.1 Intended Audience
This tutorial is intended for students just getting started with the MS Access database management system.
1.2 Pre-Requisite Knowledge
This tutorial assumes the student is familiar with the basic operation of a personal computer and Microsoft Windows '95, Windows '98, Windows NT, Windows 2000 or Windows XP. Specific skills required for this tutorial are:
Use of the mouse and keyboard
Formatting and working with floppy disks
Opening and saving files on the hard disk and on floppy disks
Managing files in general including the difference between floppy disks, drive letters, hard disk, etc.
Running programs from the Windows Start menu
Minimizing, maximizing and resizing windows
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.
2 Brief overview of Relational Databases and Database Applications
The first databases implemented during the 1960s and 1970s were based upon either flat data files or the hierarchical or networked data models. These methods of storing data were relatively inflexible due to their rigid structure and heavy reliance on applications programs to perform even the most routine processing.
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.
Number - may only store numbers, possibly with a decimal point.
Character - may store numbers, letters and punctuation. Access calls this data type Text.
Date - may only store date and time data.
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.
3 A Business Example
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.
4 Starting Microsoft Access
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:
(Note that your Windows desktop may look slightly different).
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:
File - Menu items to Open, Close, Create new, Save and Print databases and their contents. This menu also has the Exit item to exit Access.
Edit - Cut, Copy, Paste, Delete
View - View different database objects (tables, queries, forms, reports)
Insert - Insert a new Table, Query, Form, Report, etc.
Tools - A variety of tools to check spelling, create relationships between tables, perform analysis and reports on the contents of the database.
Window - Switch between different open databases.
Help - Get help on Access.
The tabs in the main window for the database include:
Tables - Displays any tables in the database.
Queries - Displays any queries saved in the database.
Forms - Displays any forms saved in the database.
Reports - Displays any reports saved in the database.
Macros - Displays any macros (short programs) stored in the database.
Modules - Displays any modules (Visual Basic for Applications procedures) stored in the database.
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.
4.1 Review of Starting Microsoft Access
To start Microsoft Access:
Make sure a formatted floppy disk is in drive A:
Use the Start button on the task bar to open: Programs -> MS Office -> Microsoft Access
To create a new database, choose Blank Database and specify a new file name for the database. Be sure to use the drive letter (A
and a descriptive name for the new database. Click on the OK button to create the new database.
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.
To exit Access, pull down the File menu and select the Exit menu item.
--------------------------------------------------------------------------------
5 Creating and Viewing Tables
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.
5.1 Creating a Table Using the Design View
To create a table in Access using the Design View, make sure the Tables tab is displayed (that is, Access should be set to work with tables rather than with queries, forms, reports, etc.) and perform the following steps:
For Access '97, Click on the New button and highlight Design View in the dialog box that appears:
Then click on the OK button.
For Access 2000, double click on the "Create Table in Design View" item.
The Table Design View will appear. Fill in the Field Name, Data Type and Description for each column/field in the table. The CustomerID field is filled in below:
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:
Now that all of the fields have been defined for the table, a Primary Key should be defined. Click on the CustomerID field with the Right mouse button and choose Primary Key from the pop-up menu.
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.
As a final step, the table must be saved. Pull down the File menu and choose the Save menu item. A dialog box will appear where the name of the new table should be specified. Note that Access gives a default name such as Table1 or Table2. Simply type over this default name with the name of the table.
For this example, name the table: Customer Then click on the OK button.
At this point, the new Customer table has been created and saved. Switch back to the Access main screen by pulling down the File menu and choosing the Close menu item. This will close the Design View for the table and display the Access main screen. Notice that the new Customer table appears below the Table tab.
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
5.2 Exercise: Creating a Table
Create the Accounts table by following the same steps used to create the Customer table.
Click on the New button and highlight Design View in the dialog box that appears. Then click on the OK button.
The Table Design View will appear. Fill in the Field Name, Data Type and Description for each column/field in the Accounts table.
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:
Define a Primary Key for the Accounts table. Click on the AccountNumber field with the Right mouse button and choose Primary Key from the pop-up menu.
Save the new Accounts table by pulling down the File menu and choosing the Save menu item. Fill in the name of the table: Accounts Then click on the OK button.
5.3 Viewing and Adding Data to a Table
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.
5.4 Exercise: Adding Data to a Table
For this exercise, open up the Accounts table and add data for the seven accounts shown in section 2. Be sure to enter the data exactly as shown including the capitalization of the data in the AccountType field. e.g., type Savings instead of savings or SAVINGS.
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.
5.5 Creating Relationships Between tables
Recall that one of the main characteristics of relational databases is the fact that all tables are related to one another. In the Bank database thus far, the Customers table is related to the Accounts table by virtue of the CustomerID field appearing in both tables. Access has a means to make this relationship explicit using the Relationships screen. Access uses this information when designing reports, forms and queries that require more than one table to be displayed.
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.
5.6 Review of Creating and Viewing Tables
Creating a new table requires the following steps:
Click on the Tables tab on the Access main screen
Click on the New button.
Choose the Design View and click the OK button.
Fill in the name, data type and description of each of the fields in the table.
Designate a primary key by clicking on one of the fields with the right mouse button and then choose Primary Key from the pop-up menu.
Save the table by pulling down the File menu and choosing Save.
Close the new table by pulling down the File menu and choosing Close.
To change the design of an existing table (e.g., to add, change or delete a field):
Click on the Tables tab on the Access main screen
Highlight the name of the table to be modified and click on the Design button.
Make the necessary changes.
Save the table by pulling down the File menu and choosing Save.
Close the table by pulling down the File menu and choosing Close.
To add, delete or change data in an existing table:
Click on the Tables tab on the Access main screen
Highlight the name of the table to be modified and click on the Open button.
Make the necessary changes to the data.
Save the table data by pulling down the File menu and choosing Save.
Close the table by pulling down the File menu and choosing Close.
To create or edit relationships between tables:
Pull down the Tools menu and select the Relationships menu item.
To display tables, right click and choose Add Tables
To create new relationships, drag a key field from one table and drop it on the associated field in another table
To edit an existing relationship, double click on the relationship line.
To delete an existing relationship, click on the relationship line and press the delete key.
--------------------------------------------------------------------------------
6 Creating and Running Queries
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:
Which Customers live in Georgia ?
Which Accounts have less than a $500 balance ?
In this section, we show how to use the Access Wizards to create queries for a single table and for multiple tables.
6.1 Single Table Queries
In this section, we demonstrate how to query a single table. Single table queries are useful to gain a view of the data in a table that:
only displays certain fields (columns) in the output
sorts the records in a particular order
performs some statistics on the records such as calculating the sum of data values in a column or counting the number of records, or
filters the records by showing only those records that match some criteria. For example, show only those bank customers living in GA.
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:
Tables/Queries - A pick list of tables or queries you have created.
Available Fields - Those fields from the table that can be displayed.
Selected Fields - Those fields from the table that will be displayed.
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:
Open the query to view information - that is, the wizard will execute the query and show the data.
Modify the query design - the wizard will switch to the Design View to allow further modification of the query.
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:
Field - The name of the field from the table
Table - The table the field comes from
Sort - The order in which to sort on this field (Ascending, Descending or Not Sorted)
Show - Whether or not to display this field in the query output
Criteria - Indicates how to filter the records in the query output.
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.
6.2 Exercise: Single Table Queries
For this exercise, use the Simple Query wizard to create a query on the Accounts table showing just the AccountNumber, AccountType and Balance fields.
From the Access main screen, click on the Queries tab. Then click on the New button.
Choose the Simple Query wizard option and click on the OK button.
Under Table/Queries: choose the Accounts table. Then move the AccountNumber, AccountType and Balance fields over to the Selected fields area. Then click the Next button.
In the next panel, you will be asked to choose between a detail or summary query. Choose detailed query and click on the Next button.
Name the new Query : AccountsQuery and click on the Finish button.
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.
From the Queries tab on the Access main screen, highlight the AccountsQuery and click on the Design button.
Change the Sort order for the AccountNumber field to Ascending.
Add the following statement to the Criteria: are under the AccountType field:
= 'Savings'
Run the query by pulling down the Query menu and choosing the Run menu item. The output is shown below:
Finally, save and close the query to return to the Access main screen.
6.3 Multiple Table Queries
Up to this point, queries involving only one table have been demonstrated. It is almost a given that queries will need to involve more than one table. For this example, assume that a manager would like to see a list of all of the customers and the type of account(s) that each one maintains at the bank. Such a query requires data from both the Customers table as well as the Accounts table. In such queries, Access will rely on the Relationships established between tables to guide how the data will be assembled to satisfy the query.
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').
6.4 Exercise: Multiple Table Queries
For this exercise, create a new query called "Accounts Summary Query" that joins the Cusomers table (include the CustomerID and Name fields) with the Accounts table (include the Balance field only). In the second step of the wizard, click on the Summary choice (instead of Details) and then click on the Summary Options... button. Check off all of the Summary option boxes such as Sum, AVG, Min and Max as shown in the figure below:
The resulting query should appear as follows:
6.5 Review of Creating and Running Queries
In this section, the basic steps for creating and running queries were introduced. The query wizard can be used to create simple queries that access a single table. It is also possible to then modify the query to sort or filter the records.
Creating a query using the query wizard:
From the Access main screen, click on the Queries tab. Then click on the New button.
From the Queries tab on the main Access screen, click on the New button and choose the Simple Query wizard option.
Under Table/Queries: choose the appropriate table for the query and then indicate which fields in the table will appear in the query output.
If querying more than one table, change the Table/Queries: selection to display additional tables and select the necessary fields.
If the table contains numeric fields, either detailed or summary information may be specified for the query.
Finally, name the new query and click on the Finish button.
As a final note, Forms and Reports can be created based on existing queries.
--------------------------------------------------------------------------------
7 Creating and Running a Data Entry Form
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.
7.1 Creating a Single Table Form using the Wizard
In this example, we will create a simple data entry form for the Customer table. To begin the process, click on the Forms tab on the Access main screen. As with the other components in Access, there are buttons for creating a New form, Open an existing form and Design an existing form. For this example, click on the New button to create a new 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.
Columnar - Places the labels to the left of each field. This is similar to a paper form. This layout is suitable for viewing data one record at a time.
Tabular - Places the field labels at the top of the screen and the records are displayed below. This is similar to how a spreadsheet would display the data and is suitable for displaying multiple records of data at a time.
Datasheet - The data appears in the same fashion as when viewing or adding data to a table.
Justified - Places the labels above each field with the fields spread out on the form. This is suitable for viewing a single record at a time as with the columnar layout.
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.
7.2 Exercise: Creating a Single Table Form
For this exercise, we will create a data entry form for the Accounts table created in a previous exercise.
Click on the Forms tab on the Access main screen and then click on the New button to create a new form.
Select the Form wizard and select the Accounts table. Then click the OK button.
Select all of the available fields and click on the Next button.
Choose a Tabular layout and click on the Next button.
Choose the Standard style and click on the Next button.
Name the form: AccountsDataEntry
Then click on the Finish button to create, save and view the new form.
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.
7.3 Review of Creating and Running a Data Entry Form
The basic steps for creating a simple data entry form are:
Choose a table and a form wizard
Specify the fields (columns) that will appear in the form
Specify the layout for the form
Specify the style (fonts/colors, etc.) for the form
Save, create and run the new form
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.
--------------------------------------------------------------------------------
8 Creating and Running a Report
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.
8.1 Creating a Single Table Report using the 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:
Columnar - Places the labels to the left of each field. This is similar to a paper form.
Tabular - Places the field labels at the top of the report page and the records are displayed below. This is similar to how a spreadsheet would display the data.
Justified - Places the labels above each field with the fields spread out on the report page.
Generally, reports use the tabular layout. For this example, choose Tabular layout and set the page Orientation to Landscape so that all of the fields will fit across one page. This is shown in the figure below. Click on the Next button to continue.
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.
8.2 Exercise: Creating a Single Table Report
For this exercise, we will create a report showing all of the Accounts information.
From the Reports tab on the Access main screen, click on the New button.
Select the Report wizard, select the Accounts table and then click the OK button.
Select all of the fields in the Accounts table by moving them all over to the Selected Fields side and then click Next
Group the report by CustomerID by clicking on the CustomerID field and then clicking on the right arrow button. This is shown in the following figure:
Click on the Next button.
Choose to sort the report on the AccountNumber field. Note that a new button will appear called Summary Options.
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.
Choose a Block layout and click on the Next button.
Choose the Corporate style and the click on the Next button.
Finally, name the report: AccountsReport and click on the Finish button to create, save and run the report.
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.
8.3 Review of Creating and Running a Report
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.
--------------------------------------------------------------------------------
9 Summary
In this tutorial, we have covered the basics for creating an Access database including tables with data, queries to retrieve data, forms to enter data and reports to display and summarize data.
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.
--------------------------------------------------------------------------------
File: accessall.html Date: Tue Sep 3 11:39:35 EDT 2002
All materials Copyright, 1998-2002 Richard Holowczak
and I sent it to a friend 
