BuiltWithNOF
                     Database Creation



After completing the module on
database normalisation, course members should next study this module. The contained  topics include:

Database preparation steps
The Access desktop
Creating a new database
Creating a table
Correcting errors in the table structure
Adding records to the Customer table
Correcting errors in the data
Previewing and printing the contents of a table
Creating additional tables
Using a form to view data
Creating a graph
Using on-line Help.


1. Introduction.

The term database describes a collection of data organised in a manner that allows access, retrieval and use of that data. A database management system (DBMS), like Microsoft Access,allows  you use a computer to create a database; add, change and delete data in the database; sort data in the database; retrieve data in the  database; and create forms, graphs and reports using data in the database.

In Access, a database consists of  a collection of tables. Figure 1 shows a sample database for an organisation.  It consists of two tables. The CUSTOMER table contains information about the  customers of the organisation. The Sales Rep (SLSREP) table contains information  about the organisation’s sales representatives.

The rows in the tables are called  records. A record contains information  about a given person, product or event. A row in the CUSTOMER table, for example,  contains information about a specific customer.

Customer Table:

Cust

No.

Name

Address1

Address2

Town/

City

County

Post

Code

Balance

Credit

Limit

Sales

Rep

 

 

 

 

 

 

 

 

 

 

AN91

Atwater-Nelson

21 High Street

Chadsmoo r

Cannock

Staffs

WS11

4JL

£3478.50

£7000

04

AW52

Alliance

West

The Factory

Tremaine Street

Rugeley

Staffs

WS12 8GH

£492.20

£4000

07

BD22

Betterdial

23 Baker Street

Bloxwich

Walsall

West

Midlands

CV3 6HL

£57.00

£4000

07

CE76

Carson Enterpris e

96 Prospect Road

Hanley

Stoke

Staffs

WS8

6FH33

£4125.00

£9000

11

FC63

Forrest Co.

85

Stocking

Ave.

Bushbury

Wolver-hampto n

West

Midlands

CV4

9GF

£7822.00

£7000

04

FY16

Dovesoft

Conputin g

The Green

 

Cannock

Staffs

WS11 1UE

£3912.00

£7000

07

LR72

Lancross Inc.

10 Bird Street

 

Lichfield

Staffs

WS7

8DL

£0.00

£7000

07

MT19

Morton

Trent

86 Bedford Drive

Wildwood

Stafford

Staffs

ST7 9SW

£1867.50

£7000

04

RO22

Robertso n Inc.

6 Cemetery Road

Chadsmoo r

Cannock

Staffs

WS11 1UG

£2336.25

£9000

11

RO92

Ronald Osten

5 Church

Street

 

Cheslyn

Hay

Staffs

WS3 2WT

£6420.00

£9000

07


 

Sales Rep (SLSREP) Table:

Sales

Rep.

No

Last

Name

First

Name

Address1

Address2

Town/

City

County

Post

Code

Sales

Comm.

Rate

 

 

 

 

 

 

 

 

 

 

04

Wright

Cecilia

12 West Road

Quinton

Birmingham

West

Midlands

BR3 4GK

£53172.00

0.08

07

Perry

Thomas

16 Main Street

Boley Park

Lichfield

Staffs

WS3 6DS

£74956.00

0.06

11

Sanchez

Maria

Flat 6

Nelson House

Church Street

Perton

Wolver

-hampton

West

Midlands

WV4 9HD

£30125.00

0.07

Figure 1.

The columns in the tables are called fields. A field contains a specific  piece of information within a record. In the CUSTOMER table, for example, the fifth field, Town/City, contains the town or city where the customer is  located.

The first field in the CUSTOMER table  is the Customer Number. This is a code assigned by the organisation to each customer. Like many organisations, this organisation calls it a "number"  even though it actually contains letters. The customer numbers have a special  format. They consist of two uppercase letters, followed by a two-digit number.

These numbers are unique, that is, no two customers will be assigned the same number. Such a field can be used  as a unique identifier. This simply  means that a given customer number will appear in only a single record in the table.

There is only one record, for example, in which the customer number is CL67. A unique identifier is also called a primary key. Thus, the Customer Number field is the primary key for the CUSTOMER table.

The next seven fields in the CUSTOMER  table include the Name, Address1, Address2, Town/City, County, Post Code,  Balance, and Credit Limit. For example, customer AN91 is Atwater-Nelson. It is located at 21 High Street, Chadsmoor, Cannock, Staffs. The post code is WS11 4JL. The current balance (the amount it owes to the organisation) is £3,478.50. Its credit limit (the amount its balance should not exceed) is  £7,000.00.

Each customer has a single sales representative. The last field in the CUSTOMER table, Sales Rep Number,  gives the number of the customer's sales representative.

The first field in the Sales Rep table, Sales Rep Number, is the number assigned by the organisation to each  sales representative. These numbers are unique, so the Sales Rep Number is the primary key of the Sales Rep table.

The other fields in the Sales Rep  table are Last Name, First Name, Address1, Address2, Town/City, County, Post  Code, Sales, and Commission Rate. For example, sales representative 04 is  Cecilia Wright. She lives at 12 West Road, Quinton, Birmingham, West Midlands.  Her Post Code is BR3 4GK. So far this year, she has sold £53,172.00 worth  of product and her commission rate is 8% (0.08).

The Sales Rep Number appears in both the CUSTOMER table and the Sales Rep table. It is used to relate customers  and sales representatives. For example, in the CUSTOMER table, the sales representative number for customer AN91 is 04. To find the name of this sales representative,  look for the row in the Sales Rep table that contains 04 in the Sales Rep Number field. Once you have found it, you will see that the name of the sales  representative is Cecilia Wright. To find all the customers for whom Cecilia Wright is the sales representative, look through the CUSTOMER table for all the customers with 04 in the Sales Rep Number field. Her customers, as shown in figure 1, are AN91 (Atwater-Nelson), FC63 (Forrest Co.), and MT19 (Morton Trent).

2. What Is Microsoft Access?

Microsoft Access is a powerful database management system (DBMS) that  functions in the Windows environment  and allows you to create and process data in a database. To illustrate the  use of Microsoft Access, this course presents a series of projects. The projects  use the database of customers and sales representatives. In the first project,  the two tables that comprise the database are created and in the second project the appropriate records are added to them. The second project also uses a  form to display the data in the tables, as well as a graph to visually represent  the data, and prints a report of data contained in the tables.

3. Database Preparation Steps

The database preparation steps give  you an overview of how the database, consisting of the CUSTOMER table and  the SALES REP table shown in figure 1 will be built in this project as follows:

1.       Start Microsoft Access.

2.       Create the CUST database.

3.       Begin the creation of the CUSTOMER table.

4.       Define the fields in the CUSTOMER table.

5.       Save the CUSTOMER table in the CUST database.

6.       Add data records to the CUSTOMER table.

7.       Print the contents of the CUSTOMER table.

8.       Create and use a form to view the data in the CUSTOMER table.

9.       Begin the creation of the Sales Rep table.

10.   Define the fields in the Sales Rep table.

11.   Save the Sales Rep table in the CUST database.

12.   Add records to the Sales Rep table.

13.   Create a graph that visually represents data from the Sales  Rep table.

Do NOT perform these operations yet  as the following pages contain a detailed explanation of each of these steps.

4. Starting Access

Place a formatted diskette in drive  A.  To start Access, click on the Microsoft Access icon in the Program Manager and the Access desktop should  be displayed. This may take several seconds as Access is a large program and  requires more time to load than smaller applications such as the Calculator or WordPad.

5. The Desktop

The first bar on the desktop is the title bar. It displays the title  of the product, Microsoft

 

Figure 2.

Access. The control menu box at the left end of the title bar is used to access the control menu. The second  bar is the menu bar. It contains  a list of menus. You select an option from the menu using the mouse or keyboard.

The third bar is the toolbar. The toolbar contains buttons that allow you to perform certain tasks more quickly than using the menu bar.  Each button contains a picture depicting its function. The specific buttons on the toolbar will vary, depending on the task on which you are working.

 

Normally the correct toolbar will  display on each screen. If, for some reason, it does not, choose the tool bars command from the File or View menu and select the correct toolbar. 

The bottom bar on screen is the status bar. It contains special information  appropriate for the task on which you are working. Currently it contains the single word Ready which means Access is ready to accept commands. 

6. Creating a new database

Before creating tables that comprise the database, you need to create the database itself as a file on disk. Once the database is created, all tables, reports, and forms will automatically be placed within it. The following steps create a new database called CUST. Select the File menu by pointing  to the word File on the menu bar and clicking the left mouse button. Then point to the New Database command. 

Choose the New Database command from the File menu by clicking the left mouse button. A dialog box is offered containing General and Database tabs. Click  the General tab, select the Blank Database icon and click OK.

Type CUST name as the filename and then point to the drop-down list arrow next to the Drives list box.

Click the drop-down list arrow and then point to a:

Select drive A by clicking the left  mouse button and then point to the OK button. The filename is entered and the drive is selected.

The database is created on disk. The database: CUST window should display as shown in figure 3.

 

Figure 3.

It is interesting to check how large the file is before any records are added.

7. Creating a table.

An Access database consists of a collection of tables. Once you have created the database, you must create  each of the tables within it. In this project, for example, you must create both the CUSTOMER and SALES REP tables shown in figure 1.

To create a table, you describe the structure of the table to Access by describing the fields within it.  For each field, you indicate the following information:

¨    Field name - Each field  in the table must have a unique name. In the CUSTOMER table (Figure 4), for example, the field names are Customer Number, Name, Address 1, Address 2, Town/City, County, Post Code, Balance, Credit Limit, and Sales Rep Number.

¨    Data type - Data type indicates to Access the type of data that the field will contain. Some fields, such as Commission Rate, can contain only numbers. Others, such as Balance and Credit Limit, can contain numbers and pound signs. Still others, such  as Name and Address, can contain letters.

¨    Description - Access  allows you to enter a detailed description of the field.

You can also assign field widths to text fields (fields whose data type is "Text"). This indicates the maximum number of characters that can be stored in the field. If you do not assign a width to such a field, Access assumes the width  is 50.

You must also indicate which field or fields make up the primary key, that is, the unique identifier, for the table. In the  sample database, the Sales Rep Number is the primary key of the SALES REP table and the Customer Number is the primary key of the CUSTOMER table.

Field Name

Data Type

Field Size

Primary Key?

Description

Customer Number

Text

4

Yes

Customer Number

(Primary Key)

Name

Text

20

 

Customer Name

Address  1

Text

20

 

Address  Line 1

Address  2

Text

20

 

Address  Line 2

Town/City

Text

15

 

Town or  City

County

Text

15

 

County

Post Code

Text

8

 

Post Code

Balance

Currency

 

 

Current  Balance

Credit Limit

Currency

 

 

Credit Limit

Sales Rep. Number

Text

2

 

Number of  matching

Sales Rep.

Figure 4.

To name a  field, follow these rules:

¨    Names can be up to 64 characters in length.

¨    Names can contain letters, digits, spaces, as well as most of the punctuation symbols.

¨    Names cannot contain periods (.), exclamation points (!),  or square brackets ([ ]).

¨    The same name cannot be used for two different fields in the  same table.

Each field has a data type. This indicates the type of data that can be stored in the field. The data types you will use in this project are:

Text - The field can contain any characters.

Number - The field can contain only numbers. The numbers can be either positive or negative. Fields are assigned this type so they can be used in arithmetic operations. Fields that contain numbers but will not be used for  arithmetic operations are usually assigned a data type of Text. The Sales  Rep Number field, for example, is a text field because the sales representative numbers will not be involved in any arithmetic.

Currency - The field can contain only pound amounts. The values  will be displayed with pound signs, commas, and decimal points, with two digits  following the decimal point. Like numeric fields, you can use currency fields in arithmetic operations. Access automatically assigns a size to currency fields.

The field names, data types, field widths, primary key information, and descriptions for the CUSTOMER table are shown in Figure 4. With this information, you are ready to begin creating  the table. To create the table, use these steps:

7.1 Point to the New button in the Database dialog box.

7.2 Choose the new button by clicking  the left mouse button and then point to the New Table button. The New Table dialog box should now display. If  you have not already determined the fields that comprise your table, you can  use Table Wizards. These guide  you through the table creation by suggesting some commonly used tables and  fields. If you already know the fields you need, however, Table Wizards are NOT particularly helpful.

7.3 Choose the New Table button and  the Table Dialog box should be displayed. To create the table you make entries  in the Field Name, Data Type, and Description columns. You enter  additional information in the Field  Properties box located near the bottom of the Table Dialog box. The current  field indicator (u) should  be currently positioned on the first field, indicating that Access is ready for you to enter the name of the first field in the Field Name column.

The next step in creating the table  is to define the fields by specifying the required details in the Table Dialog  box. Perform the following steps to accomplish this task, using figure 5 as a guideline.

7.4 Type CUSTOMER NUMBER (the name of the first field) in the Field Name column and press the TAB key. The words Customer Number display in the Field Name column and the highlight advances to the Data Type column, indicating you can enter the data type.  The word Text, one of the possible data types, currently displays. You can  also use a drop-down list arrow to display a list of available data types.

7.5 Since Text is the correct data type, press the TAB key to move the highlight to the Description column, type CUSTOMER NUMBER (PRIMARY KEY)  as the description and then point to the Set  Primary Key button i.e. that containing the key icon, on the toolbar. At this stage, the Set Primary Key button should be the fifth button from  the left on the toolbar.

7.6 Click the Set Primary Key button to make the Customer Number the primary key and then point to the Field  Size text box. Customer Number is the primary key as indicated by the  key symbol that appears in front of the field. The pointer, which has changed shape to an I-beam, is in the Field Size text box.

7.7 Select the Field Size text box by clicking the left mouse button. Press the BACKSPACE key twice to erase the current entry of 50. Type 4, which is the size of the Customer Number field. Point to the Field name column just  below the field name Customer Number in the second row. The Field Size is  changed to 4.

 

Figure 5.

7.8 Click the left mouse button to select the second row, that is, to prepare to enter the second field. The current field indicator should move to the second row just below the  field name Customer Number

7.9. Use the techniques illustrated  in paragraphs 7.4 to 7.8 to make the entries from the CUSTOMER table structure  shown in figure 4. You will NOT need to click the Set Primary Key button for any of these fields. The additional fields are entered. A drop-down list  of available data types displays in the Data Type column and use figure 4 to select the correct type. Notice that in figure 5, a custom format has been selected for the currency data types. If you press F1 when positioned on the Format field, Access will display context sensitive help on using formats.

8. Correcting Errors in the Table Structure.

When creating a table, check the entries carefully to ensure they are correct. If you make a mistake and discover it before you press the TAB key, you can correct the error by pressing  the BACKSPACE key until the incorrect characters are removed. Then,  type the correct characters. If you don't discover a mistake until later, you can correct it by pointing to the entry with the mouse, clicking the left  mouse button, and typing the correct value, and then pressing the ENTER key.

If you accidentally add an extra field to the structure, select the field by pointing to the leftmost column  on the row that contains the field to be deleted and then click the left mouse  button. Once you have selected the field, press the DELETE key. This will remove the field from the structure.

If you forget a field, select the  field that will follow the field you wish to add, select Edit, and then choose the Insert Row command. The remaining fields move down one row, making room for the  missing field. Make the entries for the new field in the usual manner.

If you made the wrong field a key  field, point to the Key entry for the field, click the left mouse button, and then click the Set Primary Key button.

As an alternative to these steps,  you might want to start again. To do so, choose the Cancel button in the Create Access Table dialog box and then choose No. The original desktop displays and you can repeat the process you used earlier.

9. Saving a Table.

The CUSTOMER table structure is now  complete. The final step is to save the table within the database. To do so,  you must give the table a name. Table names are from one to sixty-four characters  in length and can contain letters, numbers, and spaces. The two table names  in this project are CUSTOMER and SLSREP.

To save the table, perform the following steps:-

9.1. Select the File menu and point to the Save command.

9.2. Choose the Save command by clicking the left mouse  button and the Save As dialog box  should display. Type CUSTOMER (in uppercase letters) as the name of the table and point to the OK button.

9.3. Point to the Close button in  the Table: CUSTOMER window. Click the Close button to close the window  and conserve memory. 

10. Adding Records to the Customer Table.

Creating a table by building the structure and saving the table is the first step in a two-step process. The  second step is to add records to the table. In order to add records  to a table, the table must first be open. To open a table, select the table  in the Database window (see figure 6) and choose  the Open button.

Figure 6 - Database window.

10.1. Point to the Open button in the Database window and click the left mouse button. Next click the maximise button in the Table: CUSTOMER  window. The window contains the Datasheet view for the CUSTOMER table and the current record indicator is positioned on the first record.  The status bar at the bottom of the  window also indicates that the record is positioned on record 1.

The table should display in datasheet view, and the table is represented as a collection of rows and columns as shown in figure 7.

 

Figure 7.

You often add records in phases. You may, for example, not have enough time to add all the records in one session. To illustrate this process, we will begin by adding the first two records  from figure 1. Referring to figure 7, notice that all records have already been inserted.

10.2 Ensure that the cursor is positioned  in the Customer Number of the first record and type AN91. The AN part of the  value must be in uppercase.

10.3. Press the TAB key to  complete the entry for the Customer Number field. Type Atwater-Nelson and press the TAB key. Type the Address 1 entry - 21 High Street, and press TAB. Type the Address 2 - Chadsmoor and press TAB. Type the Town/city - Cannock  and press TAB and enter the County - Staffs, press TAB again and enter the  Post Code of WS11 4JL. The name, address, town/city and county fields are  entered. The data for the postcode displays on the screen but is NOT complete until you press the TAB key.

10.4. When TAB is pressed, the highlight advances to the Balance field. Type the balance 3478.50 and press the TAB  key. Type the credit limit 7000 and press the TAB key and type 04 (sales rep  number). Ensure that you type 04 rather than simply 4. Access automatically  adds pound signs and commas to the data in the Balance and Credit Limit fields,  because they are currency fields. The sales rep number has been typed,  but is still positioned on the field until you press TAB.

10.5 At this point, the fields are shifted back to the right, the record is saved, and the insertion point moves  to the customer number on the next row. Now add the second record from figure 1.

11. Closing a Table and a Database.

It is a good idea to close a table as soon as you have finished working with it. It keeps the screen from getting cluttered, prevents you from making accidental changes to the  data in the table, and releases computer memory. In addition, if you are no longer working with the database, it is a good idea to close the database  as well.

The following steps describe these processes:

11.1 Point to the Close button for the Table window.

11.2 Click the close button to close the table and then point to the Close button for the Database window. Click the Close button to close the Database window and the database should be closed and the database window removed from the screen.

12. Opening a Database.

In order to work with any of the tables, reports or forms in a database, the database must be open. This is achieved by using the Open Database command on the File menu. If the  database is already open, the Database window will already be displayed on  the screen. 

12.1. Select the File menu and click the Open Database command.

12.2. If the drive listed in the Drives text box is not A, change it to A by clicking the drop-down list and selecting A. Select the CUST database by pointing to it and clicking the left mouse button. Then click the OK button in the Open Database dialog box and the database should  open and the Database window be displayed.

Adding Additional Records.

12.3. You can add records to a table that already contains data using a process almost identical to that used to  add records to an empty table. The only difference is that you place the highlight AFTER the last data record before you enter the additional data. To  do so, use the navigation buttons found near the lower left-hand corner of the screen. These are described in figure 8.

 

Figure 8.

Click the Last Record navigation button and Access positions the highlight on the last record in the table. Next, click the Next Record navigation button and add the eight remaining records into the CUSTOMER table using the same techniques you used to add the first  two records. Subsequently, close the table by clicking the Close button.

Access maintains the data so the primary key always orders it. As Customer Number is the primary key of the CUSTOMER table, the data in the table will automatically be arranged so the customer numbers are in alphabetical order. The data that you just  entered happened to be in the correct order. If you enter a record containing a customer number that is not in alphabetical order, however, Access will  automatically place it in order. You  will not notice this immediately, but rather the next time you open the  table.

For example, if you add a record with customer number PR29, it will appear at the end of the table. The next time you open the table, however, it will appear between the record for customer MTl9 and the one for customer RO22. Records are ordered in ascending order by the primary key field.

13. Correcting Errors in the Data

Just as when you created the table,  check the entries carefully to ensure they are correct. If you make a mistake and discover it before you press the TAB key, correct it by pressing the BACKSPACE key until the incorrect characters are removed and then typing the correct characters.

If you discover an incorrect entry later, correct the error by pointing to the entry with the mouse, clicking the left mouse button, and then making the appropriate correction. If the record you must correct is not on the screen, use the navigation buttons (Next Record, Previous Record, and so on) to move to it. If the field you want  to correct is not visible on the screen, use the horizontal scroll bar along  the bottom of the screen to shift all the fields until the one you want displays.  Then, make the correction.

If you accidentally add an extra record, select the record by pointing to the box that immediately precedes the record and clicking the left mouse button. Then, press the DELETE key. This will remove the record from the table. If you forget a record, add  it using the same procedure as for all the other records. Access will automatically place it in the correct location in the table.

Occasionally, you might attempt to add a record, but Access refuses to accept it. Instead, it gives you an error  message, indicating the problem. The most likely reason for this in the CUSTOMER table is inadvertently entering a customer number that duplicates the customer number on another record.

Suppose, for example, that when you  typed the fifth record (Forrest Co.), you accidentally entered the customer number for the sixth record (FY16). When you later tried to add the sixth  record, Access rejected the addition, because a record with the same customer  number (FY16) was already in the table. If this occurs, first check the customer numbers carefully, make any necessary corrections, and then try again to add the record.

If you cannot determine how to correct the data, you have a problem. Access will not allow you to move to any other  record until you have made the correction, nor will it allow you to close the table. You are, in effect, stuck on the record. If you should ever find  yourself in this situation, simply press the ESC key. This will remove the record you are trying to add from the screen. You can then move to any  other record, close the table, or take any other action you desire.

14. Previewing and Printing the Contents of a Table.

When working with a database, you  will often need to obtain a printed copy of the table contents. Because the CUSTOMER table is substantially wider than the screen, it will also be wider than the normal printed page on portrait orientation. Portrait orientation means the printout is across the width of the page. Landscape orientation  means the printout is across the length of the page. Thus, to print the wide  database table, use landscape orientation. If you are printing the contents  of a table that fits on the screen, you will not need landscape orientation. A convenient way to change to landscape orientation is to use Print Preview. This allows you to determine whether landscape orientation is necessary and, if it is, to easily change the orientation to landscape.

14.1 Ensure the CUSTOMER table is  selected. (Because it is currently the only table in the database, it will  automatically be selected, so you don’t need to take any special action at this stage). Click the Print Preview button i.e. the button to the  right of that containing the printer icon and a preview of the report should  be displayed in portrait mode. In portrait orientation all fields on a page may not be displayed. 

To change to landscape mode, choose Properties from the File>Print SetUp menu. Refer to figure 9 and click the Properties button, and in  the next screen click the Orientation>Landscape option.

 

Figure 9.

After changing the orientation, click  the OK button and print the table by either selecting Print from the File menu or by clicking on the Print icon, which is  third from left on the toolbar. The report in figure 10 should be printed.

 

Figure 10.

The values in figure 10 are only supplied for illustrative purposes and are not readable!

15. Creating Additional Tables.

A database typically consists of more than one table. The sample database contains two: the CUSTOMER table  and the SLSREP table. You need to repeat the process of creating a table and adding records for each table in the database. In the sample database, you  need to create and add records to the SLSREP table. The structure for the table is given in figure 11 and the data is in figure 1.

As an exercise constructthe SLSREP table and enter the three sales rep records into the table. Save and close the table on successful completion.

Field Name

Data Type

Field Size

Primary Key?

Description

Sales Rep. Number

Text

2

Yes

Sales Rep. Number

(Primary Key)

Last Name

Text

12

 

Last Name of Sales Rep.

First Name

Text

8

 

First Name of Sales Rep.

Address 1

Text

20

 

Address Line 1

Address 2

Text

20

 

Address Line 2

Town/City

Text

15

 

Town or City

County

Text

20

 

County

Post Code

Text

8

 

Post Code

Sales

Currency

 

 

Total Sales Amount

of Sales Rep.

Commission Rate

Number

 

 

Commission Rate

 

 

 

 

 

 

Figure 11.

16. Using a Form to View Data.

In creating tables, you have used Datasheet view, i.e. the data on the screen displayed as a table similar to a spreadsheet. You can also use Form view, in which you see a form displaying a single record at a time.

The advantage with Datasheetview is that you can  see multiple records at once. It has the disadvantage that, unless you have  few fields in the table, you cannot see all the fields at the same time. With Form view, you see only a single record, but you can see all the fields in the record. The view you choose is a matter of personal preference.

The simplest way to create a form  is to use the AutoForm button. To do so, first highlight the table for which the form is to be created in the Database window and click the arrow next to the New Object button on the toolbar, and then click the AutoForm button in the dropdown listbox:

 

Ensure the CUST database is open,  the Database window displays and the CUSTOMER table is highlighted. Click the AutoForm button as identified above and the form should display.

You can use Form view like you used  Datasheet view. Use the navigation buttons to move between records. You can  add new records or change existing ones. Press the DELETE key to delete the record displayed on the screen. In other words, you can perform database operations using either Form view or Datasheet view. As you only see one record at a time in Form view, to see a different record, such as the fifth record, use the navigation buttons to move to it.

 

Figure 12.

Closing the form is similar to closing a table. The only difference is that you will be asked if you want to save the form. Click the Close button for the Customer window and then click the Yes button on the displayed Access dialog box asking if you want to save the form. Type CUSTOMER as the name of the form and click the OK button. The form is saved as part of the database and is removed from the  screen.

Using the Saved  Form (Customer Form)

Once you have saved the form, you  can use it at any time in the future by opening it. Opening a form is similar to opening a table. Ensure that the CUST database is open, the Database window  is on the screen and point to the Form tab.

Choose the required form from the  list of forms displayed on the screen and the form should display. It can now be used in the manner described earlier and once you have finished working with the form, close it. You will not be asked if you want to save it as it has already been saved.

The form is removed from the screen  and the Database window displayed.

17. Creating a Graph.

Sometimes the most effective way to present data in a database is graphically. Access contains a graphics tool  that allows you to create and customize a wide variety of graphs. A Graph Wizard can assist in the creation  of a graph.

These notes create the graph shown in Figure 13. It illustrates the sales figures of each of the three sales  representatives. The sales representative numbers appear along the x- (horizontal)  axis. The sales amounts are represented along the y-(vertical) axis.  The height of each bar represents the sales amount for the corresponding sales  representative.

 

 Figure 13.

In creating a graph, you must select the field for the x-axis first. You can then select the field or fields for  the y-axis. To create the graph shown in Figure 13, for example, you must select the Sales Rep Number field first and then select the Sales field. The Graph Wizard then gives you a chance to make a number of selections  that affect the appearance of the final graph. In each case, the Graph Wizard  makes an initial selection for you and then gives you the option of changing the selection. For the graph shown in Figure 13, you can simply accept the selections the Graph Wizard makes for you.

The following steps are performed  to produce this graph:

17.1 Ensure the CUST database is open and the Database window is on the screen. Choose the Form tab and then click the New button.

17.2 A New Form dialog box should  be displayed. Click the Chart Wizard and the down arrow in the Choose the table or query where the object’s data comes from: list box.

17.3 Select the SLSREP table from  the offered list of tables and queries by clicking on it with the left mouse  button. (See figure 14).

 

Figure 14.

17.4 Click the OK button and  then select the Sales Rep Number and click the Add Field button. Perform a similar task by clicking and adding the  Sales field (see figure 15).

 

Figure 15.

17.5 Click the Next button and a dialog box should appear requesting the type of graph required. Choose column chart for this example.

17.6 After again clicking Next, a dialog box is displayed asking for the title of the Graph. Enter Sales by Sales Representative.

17.7 Choose the Next button and then click the Finish button. The chart in figure 13  should be displayed.

Because a graph is just a special  type of form, closing and saving a form is the same process as closing and  saving a graph i.e. Click Close on the graph form window and a dialog box will ask you if you wish to save  the graph. Choose the Yes button  and type SALES BY SALES REPRESENTATIVE as the name of graph and click the OK button.

In addition to viewing the graph on the screen, you can also print the graph. Select the Sales by Sales Representative form from the Database window and click on Open. The graph should be displayed on screen, at which stage, click the Print Preview button. If the  preview is OK, click the Print button to obtain a printed copy.

18. Using On-line Help

With Access, you can obtain help at any time by using the Help menu. For most information, choose Contents,  which provides a variety of ways of finding the information that you need. You can also choose Search to search for a specific topic or procedure. The Cue Cards command displays a series of screens that walk you through  a variety of tasks in the form of a tutorial. About Microsoft Access gives information about the version of Access and the user to whom the product is  registered. Click the Office Assistant button

and then click any other button to obtain help on the other button.

 

[Home] [Database Creation] [Queries] [Maintenance] [Reports] [Normalisation] [Switchboards] [Macros]