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
- 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
Introduction to the MS Access Tutorial
- 1.1 Intended Audience
- 1.2 Pre-Requisite Knowledge
Brief overview of Relational Databases and Database Applications
A Business Example
Starting Microsoft Access
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
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
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
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
Summary
1 Introduction to the MS Access Tutorial
Welcome to the MS Accesstutorial. 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 justgetting started with the MS Access database management system.
1.2 Pre-Requisite Knowledge
This tutorial assumes the student isfamiliar 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
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.
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: |
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.
 |
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.
- 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.
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.
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.
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 byfollowing 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 theAccounts 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 maincharacteristics 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 requiresthe 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 choosePrimary 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.
- 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.
- 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.
- 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 ?
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.
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.
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.
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.
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.
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.
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 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.
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
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.
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.
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.
All materials Copyright, 1998-2002 Richard Holowczak
No comments:
Post a Comment