1. About Database
2. Starting The Microsoft Access Database System
3. Creating A New SouthWest Database
4. Creating An Employee Table With The Design View
5. Inserting A Text/Character Field Into A Database Table From The Design View
6. Saving the New Employee Table From The Design View
7. Closing A Database Table That Is Open In The Design View
8. Re-Opening The Employee Table From The Design View
9. Inserting A Yes-No/Logical Field Into A Database Table From The Design View
10. Inserting A Numeric Field Into A Database Table From The Design View
11. Inserting A Date Field Into A Database Table From The Design View
12. Deleting A Field From A Database Table From The Design View
13. Opening A Database Table In The Browse Mode
14. Adding Records To A Database Table From The Browse Mode
15. Inserting A Memo Field Into A Database Table From The Design View
16. Simple Sorting Of A Database Table From The Browse Mode
17. Freezing Fields In The Database Table Browse Mode
18. Printing Records From The Database Table Browse Mode
19. Delete A Record From A Database Table
20. Creating A Quick AutoForm For Data Entry Into A Database Table

DataBase: <==>A database is comprised of those (1) tables, (2) programs, (3) forms, (4) labels, and (5) queries related to the specific application.

DatabaseTable: <==> a collection of information pertaining to some type of entity. A database table might represent an entity such as clients (contact manager) , an entity such as parts (inventory management system), an entity such as homes (real-estate management system), an entity such as students (grade book program), etc.

Record:  <==> the information related to one entity (one part, one client, or one student). In a client database table, each record would contain the information related to one client. In a student database table, each record would contain the information related to one student.

Field: <==> the individual sub-components of one record. Each database table is partitioned into one or more fields. The fields within a client database table might be Name, Address, City, State, Zip, Phone, Male, Notes, etc. The fields within a Hhomes database table might be Owner, Address, City, State, Zip, Phone, NoBedrooms, NoBath, Pool, Info, etc.

Field Name: <==> Associated with each field must be a Field Name. The Field Name is a program variable associated with this field; this name should be meaningful and a single token (no blanks!). It is best to use upper & lower case letters to make this field more readable; FirstName is preferred to any of the following: firstname, first_name, firstName, or  First Name. The first character of each important word in the field name should be capitalized; there should be no blanks in the field name.

Field Data Type: <==> Associated with each field must be a Data Type, also spelled Datatype. The Data Type  is absolutely essential. It is the data type that helps to prepare the right type of container in which the user can include the desired information. Without going into detail, it is sufficient to say that computers store character data differently than whole numbers differently than fractional numbers etc. Our primary data types shall include text/char, logical/yes-no, date, numeric, and memo.

Text Data Type: <==> the Text Data Type, often called the Character Data Type, prepares a container into which the user may enter any of the ASCII characters from the keyboard. The field Data Type must be Text when there is character data such as A, B, C, ..., Y, Z, a, b, c, ... , y, z, +, -, $, %, >, (, {,etc. The text field is confined to some fixed number of bytes/characters (size).

Yes/No Data Type: <==> the Yes/No Data Type, often  called the Logical Data Type, prepares a container for a Yes/No or True/False type of response. Some database languages allow you to do this with a Check Box, some with T/F and Y/N, and some with both.

Date Data Type: <==> the Date Data Type  prepares a container for dates in the format 12/25/99 or 12-25-99. Sometimes additional formats, such as 25-Dec-99 are acceptable.

Numeric Data Type: <==> the Numeric Data Type  prepares a container for numeric information. Numeric data is often stored in the computer's native binary format. The computer stores integer { 0, +/- 1, +/- 2, +/- 3, +/- 4, ...} differently than it stores rational {a/b | a is an integer, b is an integer, and b <> 0} numbers. We sometimes call the rational numbers fractions. Larger numbers and precision often require more bytes for storage. It is the database designer's responsibility to select a container that is sufficiently large enough to hold all of the values that might be stored in the field without making the field too large; this size can be changed later.

Numeric [Byte] Data Type:  <==> can be used to store numbers 0 <==> 255. [1 byte]

Numeric [Integer] Data Type: <==> can be used to store numbers -32,768 <==> 32,767. [2 bytes]

Numeric [Long Integer] Data Type: <==> can be used to store numbers -2,147,483,648<==> 2,147,483,647. [4 bytes]

Numeric [Single] Data Type: <==> real/fractions represented with scientific notation with at least 6 digits of accuracy. [4 bytes]

Numeric [Double] Data Type: <==> real/fractions represented with scientific notation with at least 12 digits of accuracy. [8 bytes]

Database Form: <==> a screen window into some portion of the database information. A form can contain information from database fields as well as  buttons, tabs, & controls which assist in user processing.

Database Report: <==> a print formatted copy  of some portion of the database information.

Database Label: <==> a print copy  of some portion of the database information that is designed especially for output on business type labels.

Database Query: <==> a specialized request for some portion of the database information.
Primary Goal of a DBMS: Provide a convenient and efficient environment in which store and retrieve information. 
 

Start->Programs->Access

Our objective is to create a database application for the SouthWest corporation. The tables for this application shall include  Employees, Customers, Products, Orders, Invoices, Suppliers, etc.
 

1]  Since it is a good idea to place all items associated with a database application inside a folder, let us create a folder, called SouthWest. Students, using a university laboratory should create this folder on floppy drive A or in the Temp folder of hard drive C; since the hard drive access is so much faster, I choose to create folder/directory C:\Temp\SouthWest. (See Below!)
 
  2] When first launching the Microsoft Access Database application, you will have an opportunity to create a new database or open an existing database.  Using the mouse, select the radio button Blank Database. Using the mouse, push/select the OK button.  (See Below!)
 
3] You will now be given an opportunity to select the destination folder for your database; select folder/directory C:\Temp\SouthWest.  (See Below!) You will also be given an opportunity to name the new database; enter SouthWest.mdb in the File Name text box.  (See Below!)  All Access databases must have the extension mdb (microsoft database).  Using the mouse, push/select the Create button. (See Below!)
 

---

Creating An Employee Table With The Design View

---

1] When first launching the Microsoft Access Database application, you will have an opportunity to create a new database or open an Select the Tables Tab; Notice that there are no tables in the SouthWest application below. We are now ready to create a new Employee database table.  Using the mouse, push/select the New button. (See Below!)

  2] Using the mouse, select Design View.  Using the mouse, push/select the OK button. (See Below!)
  3] The design view provides us an opportunity to (1) add new fields to the database table, (2) change attributes of existing fields, and/or (3) delete existing fields. The new table . (See Below!)
 

---

Inserting A Text/Character Field Into A Database Table From The Design View

---

1] Each database table is partitioned into one or more fields. Associated with each new field must be a Field Name. The Field Name [yellow] is a program variable associated with this field; this name should be meaningful and a single token. It is best to use upper & lower case letters to make this field more readable; FirstName is preferred to any of the following: firstname, firstName,or  First Name. Enter FirstName [no blanks!] into the first Field Name. (See Below!)
 
2] Associated with each field must be a Data Type, also spelled Datatype. The Data Type [pink] is absolutely essential. This data type helps to prepare the computer for the proper type of information. It is our intention to create a Text container into which the user can enter any of the characters from the keyboard {A, B, C, ..., Y, Z, a, b, c, ... , y, z, +, -, $, %, >, (, {,etc.}.  Select Text [from pull down menu] in the Data Type.  (See Below!)
 
3] The Description (black) is an optional block of text that will help other computer scientists better understand the purpose of the field. The Selection of good Field Names often enables us to leave this optional description blank. (See Below!)
 
4] The Field Size (green) is the maximum number of characters that you want to allow the user to enter for this field. Enter 15 [with the blank] in the Field Size.  (See Below!)
 
5] The Caption  (blue) is the prompt/title that will be associated with this field when forms and reports are generated; it should be readable and meaningful. Enter First  Name [with the blank] in the Caption. (See Below!)
 
6] Some fields are absolutely essential to every record. An employee without a first name is meaningless. The Required  will force entry into this field prior to saving/changing a record; select Yes [from pull down menu] in the Required.
 
7] Somewhat associated with the required fields is whether or not a field can be blanked out or Allow Zero Length. A last name of zero length is also meaningless. The Allow Zero Length  will force entry of at least one character  into this field prior to saving/changing a record; select No [from pull down menu] in the Required field.
 
8] An Index (olive) will greatly speed up sorting and searching related to this field. Since the user might want to skip through the records on the form in alphabetical order by first and last name, we probably should select Yes (allow duplicates because two people might have the same last name or the same first name). Selecting Yes would enable us to print a report or labels in alphabetical order.

It is my desire to keep this first database table quite simple. I hope to address indexing and searching in a later document; we shall select No in the Index field. (See Below!)



9] Other Text fields in the Employee database Table might be the following:
 

Field Name	Data  Type	Field  Size	Caption	Required	Allow Zero Length	Index
Last Name	Text	20	First Name	Yes	No	No
Salutation	Text	30	Salutation	No	No	No
Street	Text	60	Street	No	No	No
City	Text	20	City	No	No	No
State	Text	2	State	No	No	No
ZipCode	Text	10	Zip Code	No	No	No
HomePhone	Text	14	Home Phone	No	No	No
WorkPhone	Text	14	Work Phone	No	No	No

 

---

Saving the New Employee Table From The Design View

---

File->Save

2] You will be given an opportunity to select a database table name. Enter Employee into the text field. Using the mouse, push/select the OK button. (See Below!)
 
3] The Primary Key is an index that helps to (1) order the data when presented in a list, form, label, or report and (2) make searching by this key much more efficient. Since indexing is a topic for another time, we shall not define a primary key. Using the mouse, push/select the NO button. (See Below!)
 
  4] The Employee Table is now part of the SouthWest database. The table file is encapsulated inside file SouthWest.mdb.
 

---

Closing A Database Table That Is Open In The Design View

---

-> close

 

1] We realize that we have forgotten to provide a container in which to store the E-Mail address of the employees. We need to modify the database structure/layout.

The database structure of an existing table must be re-opened in the design view to  (1) add a new field to the database table, (2) change the attributes of an existing field, and/or (3) delete an existing fields. Select the Table Tab in order to modify a database table structure.

Select the Table Tab; this permits you to view the database tables associated with the open database. As a database expands, there will often be more than one table associated with the database. The SouthWest company might have an employee table, a customer table, a products or inventory table, etc.
 

2] Using the mouse, select the Table To Modify. Since the SouthWest database currently has only one table, finding the Employee table is easy. Using the mouse, select/push the Design Button..  (See Below!)
 
  3] Add the E-Mail text container to the database table. (See Below!)
 

 

---

Inserting A Yes-No/Logical Field Into A Database Table From The Design View

---

---

Inserting A Numeric Field Into A Database Table From The Design View

---

Numeric [Byte] Data Type:  <==> can be used to store numbers 0 <==> 255. [1 byte]

Numeric [Integer] Data Type: <==> can be used to store numbers -32,768<==> 32,767. [2 bytes]

Numeric [Long Integer] Data Type: <==> can be used to store numbers -2,147,483,648<==> 2,147,483,647. [4 bytes]

Numeric [Single] Data Type: <==> real/fractions represented with scientific notation with at least 6 digits of accuracy. [4 bytes]

Numeric [Double] Data Type: <==> real/fractions represented with scientific notation with at least 12 digits of accuracy. [8 bytes]

The SouthWest Company has 1,000 departments (numbered 1, 2, 3, ... , 999, 1000).
 

Field Name	Data  Type	Field  Size	Decimal Places	Caption	Required	Index
NoDependents	Numeric	Byte	Auto	No. Dependents	No	No
HourlyRate	Numeric	Single	2	Hourly Rate	No	No
DepartmentNo	Numeric	Single	Auto	Department No.	No	No

---

Inserting A Date Field Into A Database Table From The Design View

---

2] Note that there are several possible date formats. (See Below!)
 
3] The complete Birthday field is now complete. (See Below!)
 

 

1] In order to delete a field, move the mouse to the box on the left of the file name; right mouse click this box to and select Delete Rows from the pull down menu. (See Below!) In the illustration below, it is our desire to remove field Junk from the database layout. The box to the left of field Junk has been colored red; it is on this box that the right mouse button is pressed to activate the pull down menu.. Delete Rows is then selected. (See Below!)
 

2] When the junk field is deleted and the database layout/structure/design is saved, all of the data associated with this field shall be lost. There is not an UnDo option! If you delete the field, you lose the data associated with that field!

1] Using the mouse, select the Tables Tab. Using the mouse, select that database table which you wish to browse; we shall select the Employee Table.  Using the mouse, select/push the Open button. (See Below!)
 
  2] The database table in now open in the browse mode. This mode resembles a spreadsheet view. (See Below!)

3] A database table with only three or four fields and a small number of records will be totally visible in the view mode. Since the Employee database has 14 fields, they will not all be viewable at one time. There is a slide bar on the bottom of the screen that can be used to see the other fields; it has been colored red in this screen capture to make it easier to identify; normally it will be gray. (See above)
 

  4] The red slide bar can be used to view the other fields. (See Below!)
 
  5] The browse/view mode will always include one blank record at the end; it is in this blank record that new records can be added to the table.

6] Note that the captions entered, when the database table was created in the design view, appear at the top of each column.

1] Open the database table in the browse mode.  There is plenty of room to type Maurice into the First Name field. There is plenty of room to type Eggen into the Last Name field. There is not enough room to type Dr. Maurice Eggen into the Salutation field. (See Below!)
 
  2] Just as spread-sheet cell width can be altered by dragging the mouse in the columns (at the top), so can the browse window. When the mouse is placed over the line that partitions the Salutation and the Street, the mouse icon changes into a black icon that resembles the following:  (See Below!)
 
  3] Holding down the left mouse button, drag the salutation field to the left {enough to display the entire salutation for Dr. Eggen}. (See Below!)
 
  4] Let go of the mouse button. (See below!)
 
  5] Each field width can be made smaller or larger. When you close the browse mode, you will be given an opportunity to save your changes you have made to the various field widths.  (See Below!) I recommend saving the changes once you get the fields the way you like; this will assure that the table browse mode will maintain your changes. Using the mouse, push/select the Yes button. (See Below!)
 
  6] In the screen below you can see the first half of the information in the Dr. Maurice Eggen Record. (See Below!)
 
  7] In the screen below you can see the second half of the information in the Dr. Maurice Eggen Record. (See Below!)
 
  8] Note that there is an empty record, at the bottom, in which to add the next employee. (See Above!)

9] Five records have now been added. (See Below!)

10] The rest of the Five records. (See Below!)

2] An Info field has now been added to the Employee table. (See Below!)
 
  3] The Employee table has now been opened in the browse mode. General comments about some of the employees have been included. This field will be represented as a scroll window on our data entry forms. For the moment however, the data will just be scrolled off the screen when long blocks of text are entered into the info field from the browse window. (See Below!)
 

 

1] Suppose we would like to have the Employee table ordered by the Last Name. Using the mouse, right click in the Last Name caption, colored red for easy identification, at the top of the table; select Sort Ascending. Ascending order places these in alphabetical order from A to Z. Descending order would place the records in alphabetical order from Z to A. (See Below!)
 
2] The results of the sort may be seen below. Note that all of the information in the William Hicks record was shifted as a package. The same is true for the other records. This sort did not simply change the Last Name column; it sorted entire records.
 
3] Any of the fields may be sorted in ascending or descending order.

4] The sort order for each table will be remembered. If you add new records, or change existing records, the browse window will appear to be unsorted. If you close this window and re-open it, you will see that the information will continue to be sorted each time the browse window is opened.

Suppose we enter a record for Ken Hicks. (See Below!) It is not immediately sorted!
 

  5] The Employee browse is closed and reopened.  Note that Ken Hicks appears in order by last name! (See Below!)
 
  6] The sorting of character ignores case sensitivity. This means that ken hicks would be sorted exactly like Ken Hicks. (See Above!)

7] The spelling of Ken Hicks has now been corrected!

 

3] Let us move the slide bar to view the First Name caption. Using the mouse, right click in the First Name caption at the top of the table; select Freeze Columns. The FirstName field will be frozen in place (on the left) as the slide bar shifts the non-frozen fields across the view.   (See Below!)
 

 

 

 
  6] When you close the browse mode, you will be given an opportunity to save your changes you have made to the various field widths.  (See Below!) I recommend saving the changes once you get the fields the way you like; this will assure that the table browse mode will maintain your changes. Using the mouse, push/select the Yes button. (See Below!)
 
 

7] The frozen columns can be unfrozen at any time. Using the mouse, pull down the Format Menu and select Unfreeze All Columns.

1] Suppose we wish to print all of the information from all of the Employee table. Open the database table in the browse mode. Using the mouse, pull down the File Menu and select Freeze Columns. (See Below!)

2] Page 1 of the output is shown below.

3] Page 2 of the output is shown below.  Without locking the FirstName and LastName, they are once more difficult to see what fields align with what employee.

4] Page 3 of the output is shown below.  Without locking the FirstName and LastName, they are once more difficult to see what fields align with what employee.
 
5] Page 4 of the output is shown below.  Without locking the FirstName and LastName, they are once more difficult to see what fields align with what employee.
 

---

5] Suppose the FirstName and LastName fields are frozen as illustrated above. Page 1 of the output is shown below.
 
  5] Suppose the FirstName and LastName fields are frozen as illustrated above. Page 2 of the output is shown below.
 
  5] Suppose the FirstName and LastName fields are frozen as illustrated above. Page 3 of the output is shown below.
 
  5] Suppose the FirstName and LastName fields are frozen as illustrated above. Page 4 of the output is shown below.
 

 

 
  2] You will then get a last chance to confirm the deletion {unless the confirmation is turned off}. Using the mouse, push/select the Yes button. (See Below!)
 

3] Note that the Tom Terrific record has now been deleted.  (See Below!)

1] Autoform is used to create a very simple data entry form/screen by a quite novice user. It is extremely crude, but superior to data entry using the table browse mode.  We shall use autoform to create a simple screen for the Employee database table.

2] Using the mouse, select the Tables Tab. Using the mouse, select the Employee Table.  Using the mouse, hold down the Insert Menu and select the AutoForm. (See Below!)
 

  3] Our form is now complete. The captions for the fields appear in a vertical column along the left side of the form. A corresponding data entry field appears in a second column on the right. This autoform has a lot of wasted space. It does not use colors and fonts well. But it is functional and easy to create and certainly superior to the table browse mode
 
 

5] Autoform processing and buttons.
The top button is used to move the read/write pointer to the first logical record in the database table. [Top of File]
  The previous button is used to move the read/write pointer to the previous logical record in the database table.
  The next button is used to move the read/write pointer to the next logical record in the database table.
  The bottom button is used to move the read/write pointer to the last logical record in the database table. [Bottom of File]
  The add button is used to move the read/write pointer to a blank record at the end of  the database table for data entry.
  Tells  us that the Employee table has 6 records.
  The current record number window tells us that the current record number is 1; the read/write pointer is pointing to record 1. If you place the mouse in this current record number window and enter 4, the information from record 4 will immediately be seen in the form.

  The resize button is used to resize the autoform; if you make it small enough, slide bars will automatically be created.

 

The current record may be deleted from the autoform. Note the triangle, colored red,  in the top left corner of the form. Using the mouse, right mouse click on the triangle and select Cut. The following confirmation window will pop up to confirm your deletion. Using the mouse, select/push the Yes button to delete the current record.
 

  6] You may either use the  Terminate button or 

 
 

 
  8] Using the mouse, select the Forms Tab. There is now a form called Employee. (See Below!)
 
  8] The Employee form may be re-opened at any time. Using the mouse, select the Employee form. Using the mouse select/push the Open button. (See Below!)
 

9] If you change the database layout {add fields, delete fields, edit field properties}, it is best to delete the form and run autoform again to make sure that your data entry form reflects all of the changes.

 Possible Exam Questions

---

May be accessed through URL: http://www.cs.trinity.edu/~thicks
May also be accessed through URL: http://carme.cs.trinity.edu
This Document May Not Be Printed or Reproduced Without Written Permission.
 2003 Copyright : Dr. Thomas E. Hicks
Permission granted : Professional Educators & College Students may print one copy of this page!

---

	Dr. Thomas E. Hicks Computer Science Department     Trinity University "Dr. Web"