The first databases implemented during the 1960s and 1970s were based upon either flat data files or thehierarchical or networked data models. These methods of storing data were relatively inflexible due to theirrigid structure and heavy reliance on applications programs to perform even the most routine processing.

In the late 1970s, therelational database model

which originated in the academic research community becameavailable in commercial implementations such as IBM DB2 and Oracle. The relational data model specifies datastored inrelations

(Access calls a column afield). The data stored in eachcolumn must be of a singledata type

such as Character (sometimes called a "string"), Number or Date. Acollection of values from each column of a table is called arecord

or arow

in the table.

Different tables can have the same column in common. This feature is used to explicitly specify a relationshipbetween two tables. Values appearing in column A in one table are shared with another table. Below are twoexamples of tables in a relational database for a local bank:

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 theCustomer table are the same the values in the CustomerID column in the Accounts table. Thisrelationship

allows us to specify that the CustomerMr. Axe

has both a Checking and a Savings account that were bothopened on the same day: December 1, 1994.

Another name given to such a relationship isMaster/Detail. In a master/detail relationship, a single masterrecord (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 isimpossible to have a Detail record without a matching Master record. For example, a Customer may notnecessarily have any account information at all. However, any account informationmust

be associated with asingle Customer.

Each tablealso must have a special column called theKey

that is used to uniquely identify rows or records inthe table. Values in a key column (or columns) may never be duplicated. In the above tables, the CustomerID isthe key for the Customer table while the AccountNumber is the key for the Accounts table.

LAB 2

2.1

A Business Example

In this section, we will outline a business example that will be used as a basis for the examples throughout thetutorial. In organizations, the job of analyzing the business and

determining the appropriate database structure(tables and columns) is typically carried out bySystems Analysts. A Systems Analyst will gather informationabout how the business operates and will form amodel

of the data storage requirements. From this model, adatabase programmer will create the database tables and then work with the application developers to developthe rest of the database application.

For this tutorial, we will consider a simple banking business. The bank has many customers who openandmaintain one or more accounts. For each Customer, we keep a record of their name and address. We also assignthem a unique CustomerID. We assign this unique identifier both for convenience and for accuracy. It is mucheasier to identify a single customer using their CustomerID rather than by looking up their full name andaddress. In addition, it is possible for the bank to have two customers with the same name (e.g., Bill Smith). Insuch 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 checkingaccount or a savings account. Savings accounts earn interest but the only transactions allowed are deposits andwithdrawals. Checking accountsdo not earn interest. We maintain the date that the account was opened. Thishelps us track our customers and can be useful for marketing purposes. Finally, we maintain the current balanceof an account.

In theprevious section,

we gave thestructure andsome sample data

for the Customer table and the Accountstable. 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

alater time. The primary way to get data into tables is to usedata entry forms. The primary ways to get data backout of tables or to display data in tables are to usequeriesorreports.

For this tutorial, we will create a data entry form for each table, a query for each table and a report for eachtable.

In the following sections, we will first introduce how to start Access and how to create a new database.

2.2Starting Microsoft Access

As with most Windows programs, Access can be executed by navigating theStart

menu in the lower left-handcorner of the Windows Desktop.

To start Access, click on theStart

button, then thePrograms

menu, then move to theMS Office

menu andfinally click on theMicrosoft Access

menu item. The MS Office Professionalmenu is shown below.

Microsoft Office Menu for Office 2007

Note that this arrangement of menus may vary depending on how MS Office was installed on the PC you areusing.

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 thedatabase 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 theOpen 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 willoverwrite 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 tocreate a new, blank database as shown in the above figure.

In Access 2007, click on the round Office button in the upper left corner and choose New from the drop downmenu. Fill inFile Name

asbankdb.accdb

and click on theCreate

button to create the database as in the figurebelow.

New Database screen for Access 2007

In the above file name,bankdb

is the name chosen for this particular database and.accdb

is the file nameextension given forMicrosoft DataBase 2007

files.Itis advisable to keep the name of the database (bankdb

inthe 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, thefollowing main Access screen will appear:

The screen layout for MS Access 2007 is significantly different from past versions. Most of the tabs along thetop of the screen have been rearranged. In addition, the default main screen after creating a new databaseautomatically switches to the Design view to create a new table.

and specify a new file name for the database. Be sureto use a descriptive name for the new database. Click on theOK

button to create the new database.

3.

To open an existing database, chooseOpen an Existing Database, highlightMore Files...

and click ontheOK

button. Then navigate to the drive, highlight the existing database file on the floppy disk and clicktheOK

button again to open the database.

To exit Access, pull down theFile

menu (or Office menu) and select theExit

menu item.

2.4

Creating and Viewing Tables

Tables are the main units of data storage in Access.Recall that a table

is made up of one or morecolumns

(orfields) and that a given column may appear in more than one table in order to indicate a relationship between thetables.

From thebusiness example

discussed earlier, we concluded that two tables would be sufficient to store the dataaboutCustomers

and their bankAccounts. We now give the step-by-step instructions for creating these twotables in Access.

There are a number of ways to create a table in Access. Access provideswizards

that guide the user throughcreating a table by suggesting names for tables and columns. The other main way to create a table is by usingtheDesign 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 theDesign View. Students

areencouraged to experiment on their own with using the Create Table wizard.

LAB 3

3.1

Creating a Table Using the Design View

To create a table in Access using the Design View, perform the following steps:

1.

In Access 2007, the Create New Table tab should already be highlighted and a new table named table1created. If this is not the case, click on theCreate

tab and click on theTable

icon. Then pull down theView

menu and chooseDesign View.

2.

The Table Design View will appear. Fill in theField Name,Data Type

andDescription

for eachcolumn/field in the table. The CustomerID field is filled in below:

Table Design View for Access 2007

3.

Note that the default name given for the table isTable1. In a later step, we will assign an appropriatename for this table.

4.

Fill in the information for the fields as follows:

Field Name

Data Type

Description

CustomerID

Number

The Unique Identifier for a Customer

CustomerName

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

5.

A figure showing the design view with the new table definition filled in is given below:

6.

Now that all of the fields have been defined for the table, a Primary Key should be defined. Recall thatthe Primary Key will be used to uniquely identify a record in

the table (in this case a Customer).Highlight theCustomerID

field and click on thePrimary Key

button on the button bar

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.

7.

As a final step, the table must be saved. Pull down theOffice

menu and choose theSave As

menuitem. A dialog box will appear where the name of the new table should be specified. Note that Accessgives a default name such asTable1

orTable2. Simply type over this default name with the name of thetable.

For this example, name the table:Customer

Then click on theOK

button.

At this point, the new Customer table has been created and saved.

Note about naming fields in MS Access

When defining the fields (columns) for a table, it is important to use field names that give a clear understandingof the data contents of the column. For example, does the fieldCNO

indicate a Customer Number or a ContainerNumber ?

Field names in Access

can be up to 64 characters long and may contain spaces.However,the use of spaces infield names and table names is strongly discouraged. If you wish to make field names easier to read, considerusing an underscore character to separate words. However be

certain no spaces appear before or after theunderscore.

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

3.2

Exercise: Creating a Table

Create theAccounts

table by following the same steps used to create the Customer table.

1.

Click on theCreate

tab and then click on theTable

button.

2.

Pull down the View menu and choose Design. The Table Design View will appear. Fill in theFieldName,Data Type

andDescription

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)

3.

A figure showing the design view with the new table definition filled in is given below:

4.

Define a Primary Key for the Accounts table. Click on theAccountNumber

field with theRight

mousebutton and choosePrimary Key

from the pop-up menu.

5.

Save the new Accounts table by pulling down theFile

menu and choosing theSave

menu item. Fill inthe name of the table:Accounts

Then click on theOK

button.

3.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 viewof a single table's data, highlight the name of the table and then double-click on the name of the table.

In this view of the Customer table, shown in the figure below, the fields (columns) appear across the top of thewindow 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. Inthis case, since the table was justcreated, only one blank record appears.

To add data to the table, simply type in values for each of the fields (columns). Press theTab

key to movebetween fields within a record. Use the up and down arrow keys to movebetween records. Enter the data asgiven 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 theOffice

menu and chooseSave.

To navigate to other records in the table, use thenavigation 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 arrowkeys 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 theEdit

menu and choose theDelete

menu item.

To close the table and return to the Access main screen, pull down theFile

menu and choose theClose

menuitem.

3.4

Exercise: Adding Data to a Table

For this exercise, open up the Accounts table and add data for the seven accounts shown insection 2. Be sure toenter the data exactly as shown including the capitalization of the data in the AccountType field. e.g., typeSavings

instead ofsavings

orSAVINGS.

Note that when entering the dates, type in the full four digits for the year. By default, Access displays all 4digits of the year (older version of Access only displayed two digits).

Be sure to save the data when you are done. The figure below shows the Accounts table and data as it shouldappear when you are done with this exercise.

At thispoint 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 auser-friendly data entry form using the Access wizards.

LAB:4

4.1

Creating Relationships Between tables

Recall that one of the main characteristics of relational databases is the fact that all tables are related to oneanother. In the Bank database thus far, the Customers table is related to the Accounts table by virtue of theCustomerID field appearing in both tables. Access has a means to make this relationship explicit using theRelationships screen. Access uses this information when designing reports, forms and queries that require morethan one table to be displayed.

To get started, make sure the Accounts table and the Customer table are both closed. Access will halt creationof any relationships if the table are currently opened. To close a table, either right-click on the table name in thetab above the table and choose the close menu item, or click the small X to right above the table.

Next, display the Relationships screen by clickig on theDatabase Tools

tab and then click on theRelationships

button as shown below.

The blank Relationships screen will appear as follows:

The Show Table dialogbox will appear by default. Highlight both the Customers table and the Accounts tableas shown below and then click on theAdd

button.

Then click on theClose

button to close this dialog box. The Relationships screen will now reappear with thetwo tables displayed as below:

To connect the Customers table with the Accounts table to form a relationship, click on the CustomerID field inthe Customers table and drag it over on top of the CustomerID field on the Accounts table. Upon releasing themouse button, the Edit Relationships dialog box will appear as below:

Access will do its best to determine the Relationship Type (almost always it will selectOne-to-Many). For thisexample, 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 asOne

customer may haveMany

accounts.

One additional step to be taken is the check off the box labeled "Enforce Referential Integrity". This option putsconstraints into effect such that an Accounts record can not be created without a valid Customer record, andAccess will also prevent a user from deleting a Customer record if a related Accounts record exists. At thispoint, click ontheCreate

button to create the relationship. The Relationships screen should reappear with thenew relationship in place as follows:

Note the symbols "1" (indicating the "One" side) and the infinity symbol (indicating the "Many" side) on therelationship. Close the relationships screen and selectYes

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 goback to the table design view and make

certain that the CustomerID field is designated as the key of theCustomers table. Then go back to the Relationships screen and try to recreate the relationship.

4.2

Review of Creating and Viewing Tables

Creating a new table requires the following steps:

1.

Click on theTables

tab on the Access main screen

2.

Click on theNew

button.

3.

Choose theDesign View

and click theOK

button.

4.

Fill in the name, data type and description of each of the fields in the table.

5.

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.

6.

Save the table by pulling down theFile

menu and choosingSave.

7.

Close the new table by pulling down theFile

menu and choosingClose.

To change the design of an existing table (e.g., to add, change or delete a field):

1.

Click on theTables

tab on the Access main screen

2.

Highlight the name of the table to be modified and click on theDesign

button.

3.

Make the necessary changes.

4.

Save the table by pulling down theFile

menu and choosingSave.

5.

Close the table by pulling down theFile

menu and choosingClose.

To add, delete or change data in an existing table:

1.

Click on theTables

tab on the Access main screen

2.

Highlight the name of the table to be modified and click on theOpen

button.

3.

Make the necessary changes to the data.

4.

Save the table data by pulling down theFile

menu and choosingSave.

5.

Close the table by pulling down theFile

menu and choosingClose.

To create or edit relationships between tables:

1.

Pull down theTools

menu and select theRelationships

menu item.

2.

To display tables, right click and chooseAdd Tables

3.

To create new relationships, drag a key field from one table and drop it on the associated field in anothertable

4.

To edit an existing relationship, double click on the relationship line.

5.

To delete an existing relationship, click on the relationship line and press the delete key.

LAB 5

5.1

Creating and Running Queries

Queries are a fundamental means of accessing and displaying data from tables. Queries can access a single tableor 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 multipletables.

5.2

Single Table Queries

In this section, we demonstrate how to query a single table. Single table queries are useful to gain a view of thedata 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 orcounting the number of records, or



filters the records by showing only those records that match some criteria. For example, show only thosebank customers living in GA.

Creating a

query can be accomplished by using either the query design view or the Query wizard. In thefollowing example, we will use the query wizard to create a query.

The first step in the Simple Query wizard is to specify the table for the query and which fields (columns) shouldbe displayed in the query output. Three main sections of this step are:

1.

Tables/Queries-

A pick list of tables or queries you have created.

2.

Available Fields-

Those fields from the table that can be displayed.

3.

Selected Fields-

Those fields from the table thatwill

be displayed.

For this example, pull

down the Tables/Queries list and choose the Customer table. Notice that the availablefields change to list only those fields in the Customer table. This step is shown below:

From the list ofAvailable fields

on the left, move the Name, Address, City and State fields over to theSelected Fields

area on the right. Highlight one of the fields and then click on the right arrow buttoninthe center between the two areas. Repeat this for each of the four fields to

be displayed. When done with thisstep, the wizard should appear as below:

Click on theNext

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 modificationof the query.

For this example, chooseOpen the query to view information

and click on theFinish

button. When thisquery executes, only the customer's name, address, city and state fields appear, however, all of the rows appearas shown in the figure below:

Close this query by pulling down theOffice

menu and choosing theClose

menu item. The Access main screenshowing the Queries tab should appear. Note the new queryCustomerAddress

appears under the Queries tab.

In

the following example, we will modify theCustomerAddress

query to only display customers in a certainstate. 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 ofthe query and clicking ontheDesign

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 displayedalong with the available fields. In the bottom section, those fields that have been selected for use in the queryare 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 thosecustomers living in the State of Georgia (GA).We will also sort the records on the City field.

To sort the records on theCity

field, click in theSort

area beneath theCity

field. ChooseAscending

from thelist as shown in the figure below:

To filter the output to only display Customers in Georgia, click in theCriteria

area beneath theState

field andtype the following statement:

= 'GA'

The= 'GA'statement tells Access to only show those records where the value of theState

field is equal to'GA'. Note the use of single quotes to surround the characters.

Run the query by clicking on theRun

button (with the large red exclaimation point). The output is shown in thefigure below:

Finally, save and close this query to return to the Access main screen.

5.3

Exercise: Single Table Queries

For this exercise, use the Simple Query wizard to create a query on the Accounts table showing just theAccountNumber, AccountType and Balance fields.

1.

From the Access main screen, click on the Queries tab. Then click on theNew

button.

2.

Choose theSimple Query wizard

option and click on theOK

button.

3.

UnderTable/Queries:

choose the Accounts table. Then move the AccountNumber, AccountType andBalance fields over to the Selected fields area. Then click theNext

button.

4.

In the next panel, you will be asked to choose between a detail or summary query. Choose detailedquery and click on theNext

button.

5.

Name the new Query :AccountsQuery

and click on theFinish

button.

The output is shown below:

Close this query by pulling down theOffice

menu and choosingClose.

In the next part of the exercise, we will modify the query to sort the output on the account number and onlydisplay the Savings accounts.

1.

From the Queries tab on the Access main screen, highlight the AccountsQuery and click on theDesign

button.

2.

Change theSort

order for theAccountNumber

field to Ascending.

Add the following statement to theCriteria:

are under theAccountType

field:

= 'Savings'

3.

Run the query by pulling down theQuery

menu and choosing theRun

menu item. The output is shownbelow:

4.

Finally, save and close the query to return to the Access main screen.

5.4

Multiple Table Queries

Up to this point, queries involving only one table have been demonstrated. It is almost a given that queries willneed to involve more than one table. For this example, assume that a manager would like to see a list of all ofthe customers and the type of account(s) that each one maintains at the bank. Such a query requires data fromboth the Customers table as well as the Accounts table. In such queries, Access will rely on the Relationshipsestablished 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 theCustomers and Accounts table has been created (seeCreating Relationships

for a review of this process).

To start the process of creating a multiple table query, highlight theQuery

tab (Access '97) and click on theNew

button to create a new query. Select the "Simple Query Wizard" option as was done previously. When thesimple query wizard appears, select the CustomerID and Name fields from the Customers table, then switch theTables/Queries selection to the Accounts table and select the CustomerID, AccountType and Balance fieldsfrom the Accounts table. The result from this step is down below:

Click theNextbutton to continue. In the next step of the wizard, an option will appear to provide some level ofSummary. For this example, leave the default at "Detail ..." as shown below and then click on theNext

button.

In the final step of the wizard, name the query "Customer Accounts Query" and click on theFinish

button. Themultiple table query results should appear as follows:

As with single table queries demonstrated previously, one can change the query definition in design view byadding filters (e.g., show account information for all customers in 'GA').

5.5

Exercise: Multiple Table Queries

For this exercise, create a new query called "Accounts Summary Query" that joins the Cusomers table (includethe CustomerID and Name fields) with the Accounts table (include the Balance field only). In the second step ofthe wizard, click on theSummary

choice (instead of Details) and then click on theSummary Options...

button.Check off all of the Summary option boxes such asSum,AVG,Min

andMax

as shown in the figure below:

The resulting query should appear as follows:

5.6

Review of Creating and Running Queries

In this section, the basic steps for creating and running queries were introduced. The query wizard can be usedto create simple queries that access a single table. It is also possible to then modify the query to sort or filter therecords.

Creating a query using the query wizard:

1.

From the Access main screen, click on the Queries tab. Then click on theNew

button.

2.

From the Queries tab on the main Access screen, click on theNew

button and choose theSimple Querywizard

option.

3.

UnderTable/Queries:

choose the appropriate table for the query and then indicate which fields in thetable will appear in the query output.

If the table contains numeric fields, either detailed or summary information may be specified for thequery.

5.

Finally, name the new query and click on theFinish

button.

As a final note,FormsandReportscan be created based on existing queries.

LAB:6

6.1

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, wedescribed how to add data to a table using a spreadsheet-like view of the data. Data entry forms offer a moreuser-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 handusing a Design View as well as a number of wizards that walk the user through the forms creation process. Inthis section, we cover the basic steps for using a wizard to create a data entry form.

6.2

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 ontheForms

tab on the Access main screen. As with the other components in Access, there are buttons for creatingaNew

form,Open

an existing form andDesign

an existing form. For this example, click on theNew

button tocreate a new form.

A New Form dialog box will appear with several options for creating a new form. For this tutorial, choose theForm wizard. At the bottom of the dialog box, there is a prompt to supply the name of the table or query to beused for the new form. In this case, select theCustomer

table as in the following figure and then click on theOK

button.

In the next step of the Form wizard, we need to specify the fields from the Customer table that will appear onthe form. In this case, we want all of the fields to appear. Move each of the fields from theAvailable Fields

side over to theSelected Fields

side as in the following figure. Then click on theNext

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 issuitable 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 issimilar to how a spreadsheet would display the data and is suitable for displaying multiple records ofdata at a time.



Datasheet-

The data appears in the same fashion as whenviewing or adding data to a table.



Justified-

Places the labels above each field with the fields spread out on the form. This is suitable forviewing a single record at a time as with thecolumnar layout.

For this example, choose thecolumnar layout

as shown in the figure below and click on theNext

button.

Access has several sample display styles that determine how the form will appear, including elements such asfonts, colors and the background used in the form. For this example, select theStandard

style as shown belowand click on theNext

button.

As a final step, give this new form the name:CustomerDataEntry

and then click on theFinish

button asshown 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 recordnavigation 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 thelast record.

Go past the last record to add a new record.

To close the form and return to the Access main screen, pull down theFile

menu and chooseClose.

To open the form at any time, highlight the form name under theForms

tab on the Access main screen and clickon theOpen

button.

6.3

Exercise: Creating a Single Table Form

For this exercise, we will create a data entry form for the Accounts table created in aprevious exercise.

1.

Click on theForms

tab on the Access main screen and then click on theNew

button to create a new form.

2.

Select theForm wizard

and select theAccounts

table. Then click theOK

button.

3.

Select all of the available fields and click on theNext

button.

4.

Choose aTabular

layout and click on theNext

button.

5.

Choose theStandard

style and click on theNext

button.

6.

Name the form:AccountsDataEntry

Then click on theFinish

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

theFile

menu and choosingClose.

6.4

Review of Creating and Running a Data Entry Form

The basic steps for creating a simple data entry form are:

1.

Choose a table and a form wizard

2.

Specify the fields (columns) that will appear in the form

3.

Specify the layout for the form

4.

Specify the style (fonts/colors, etc.) for the form

5.

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 wizardswhich are adept at building simple forms with a minimal amount of work. More advanced work on forms wouldconcentrate on using the Design View to change a form's appearance and to add or remove fields and labelsonce a form is created.

LAB 7

7.1

Creating and Running a Report

Reports are similar toqueries

in that they retrieve data from one or more tables and display the records. Unlikequeries, 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 tocreate simple reports using the Report wizard.

7.2

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 Queriesand Forms, we begin by selecting theReports

tab from the Access main screen.

To create a new report, click on theNew

button. The New Report dialog box will appear as shown below. SelecttheReport wizard

and then select theCustomer

table as shown below. Then click theOK

button.

In the next step of the Report wizard, we need to specify the fields from the Customer table that will appear onthe report. In this case, we want all of the fields to appear. Move each of the fields from theAvailable Fields

side over to theSelected Fields

side as in the following figure. Then click on theNext

button.

In the next step, we have the opportunity to addGrouping Levels

to the report. A grouping level is whereseveral records have the same value for a given field and we only display the value for the first records. In thiscase, we will not use any grouping levels so simply click on theNext

button as shown below.

In the next step, we are given the opportunity to specify the sorting order of the report. For this example, wewill sort the records on the CustomerID field. To achieve this, pull down the list box next to the number1:andchoose theCustomerID field as shown in the figure below. Then click on theNext

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 issimilar 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, chooseTabular

layout and set the pageOrientation

toLandscape

so that all of the fields will fit across one page. This is shown in the figure below.Click on theNext

button to continue.

In the next step, the style of the report can be selected. For this example, choose theCorporate

style and clickon theNext

button to continue.

Finally, give a name for the new report:CustomerReport

and then click on theFinish

button to create,

saveand 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 notdisplay 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 theFile

menu and chooseClose

or clickon theClose

button.

7.3

Exercise: Creating a Single Table Report

For this exercise, we will create a report showing all of the Accounts information.

1.

From the Reports tab on the Access main screen, click on theNew

button.

2.

Select the Report wizard, select the Accounts table and then click theOK

button.

3.

Select all of the fields in the Accounts table by moving them all over to theSelected Fields

side andthen clickNext

4.

Group the report by CustomerID by clicking on the CustomerID field and then clicking on the rightarrowbutton. This is shown in the following figure:

Click on theNext

button.

5.

Choose to sort the report on the AccountNumber field. Note that a new button will appear calledSummary Options.

Click on theSummary Options

button. Choose the Balance field and select theSum

option. Choose theoption to show bothDetail and Summary

data. Then click on theOK

button.

Click on theNext

button.

6.

Choose aBlock

layout and click on theNext

button.

7.

Choose the Corporate style and the click on theNext

button.

8.

Finally, name the report:AccountsReport

and click on theFinish

button to create, save and run thereport.

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 theFile

menu and chooseClose.

7.4

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 andlayout of the data. Further study of Reports will show how to modify the layout using the Design View.Students are encouraged to work with theReport wizards to create different styles and types of reports.