Home

Database Fields on Sheet

 

Introduction to Data Fields

 

A Database From Scratch

A Database From Scratch

Starting a database from scratch allows you to create and add its different parts as needed. When using this process, the main advantage is that you will exercise as much control as possible on your database because you will be creating all of your objects. The disadvantage is that you will miss that primary layout that the Database Wizard offers. Creating a database from scratch simply means starting from a blank database and adding the different components. Of course, after learning how to perform some changes, you will be able to modify some aspects of a database created using the Database Wizard.

 

To create a database from scratch, if you are just launching Microsoft Access, you can select the first radio button. If you had started Microsoft Access already, you can display the New dialog box and, from the General tab, select Database. Creating a database from scratch also means that you create a database file that does not contain any objects. Therefore, the most you must first do is to give this file a name.

Practical Learning: Creating a Blank Database

  1. Start Microsoft Access
  2. To create a new database, on the right side, click Create a New File
  3. Under New, click Blank Database
  4. Set the name to Clarksville Ice Scream1
  5. Click Create to create the new database

Database Object and Fields

A field is an object used to host, hold, or store a piece of information of a database. Before such an object can perform its function, it must first be created. In the strict sense, the most important or the most regularly used fields of a database are created in a table; but as we have mentioned in previous sections, tables may not be the friendliest objects to present to a user for data entry. For this reason, fields can and should also be created on other objects.

The process of making a field available depends on the type of object it will reside on and probably how the field will be used. This leads to two categories of fields: those that can receive or store information and those that can only present or display it. All fields of a table, especially in Microsoft Access, are created to store data. On the other hand, fields on almost all of the other objects (queries, forms, reports) can be made of combinations of already existing fields of a table, or they can be created independently of any existing or non-existing data.

The Table Data Sheet and its Fields

 

Table Layout

To make fields available on a table, you must create each one of them. Before adding the fields, you must first create the table. There are various techniques available. We have already seen how to use the Table Wizard to create a table. Another technique consists of displaying the table as if you were doing data entering. In this Datasheet View, you can create, add, or insert a field. You can also add fields to a table that was created using the Table Wizard. The last technique you can use consists of designing the table.

To create a table in Datasheet View, on the Database window, you can click the Tables button and double-click the Create Table By Entering Data. Alternatively, on the toolbar of the Database window, you can click the New button. You can also click Insert -> Table from the main menu. One of these techniques would display the New Table dialog box from which you can click Datasheet View and click OK.

Practical Learning: Viewing a Table in Datasheet View

  1. To create a new table, on the main menu, click Insert -> Table
     
    New Table
  2. On the New Table dialog box, make sure Datasheet View is selected and click OK
 

 

 

 

 

Ice Cream

The Title Bar

A table is a rectangular window. On top, it presents a title bar. The title bar is made of a table's icon . The table icon provides a menu that allows you to minimize, maximize, restore or close the table. The middle section of the table icon is formally called the title bar. Like most regular popup windows, the right section of the title bar presents the system buttons.

Columns and Rows

Imagine you create a list of people with whom you have some type of relationship. The list can be started with names as follows: Bill, James, Hermine, and Khan. This type of list is considered one-dimensional because all of its items fit in one category. In order to create a more significant list, you may want to include these people’s email addresses and probably other related information. If you include these additional pieces of information in the same category, the light may become confusing. To arrange the list, you would create categories. Here is an example:

Name Email Address Phone Number Relationship
Bill bill@yahoo.com   Friend
James jamesemail.com (102)399-2893  
Hermine   (101) 447-8384 Cousin
Khan @Khan.com    

This type of display allows you to refer to a piece of information by its category. This is the basis of a table. A table is a two-dimensional list of items so that the items are arranged by categories. A complete or incomplete series of items that represent each category is called a record. Therefore, a table can be represented as follows:

In the world of databases development, a category is represented as a column. Sometimes it is also called a field. A record is represented as a row. To better organize its information, each column of a table must have a (unique) name. By default, Microsoft Access creates a table with columns named Field1, Field2, Field3, etc. By default, a table starts with 10 columns. You can add more or delete unneeded ones.

By default, a table starts with 21 rows. The number of rows increases as a user adds records. The number of rows decreases if you delete records. As we will learn latter, deleting a row is completely different than deleting a column. 

Table Cells

Although a table is primarily recognized as an arranged list of columns and rows, these are hardly important to the user. Each column and each row intersect to create a rectangular box called a cell. The cell is actually the object that holds data of a table. A cell holds only one piece of information. The database developer decides what type of information would go into a cell:

The Scroll Bars

If you start entering data into a table and there are more records than the height of the table can display, the table would be equipped with a vertical scroll bar. The vertical scroll bar would allow you to move up and down on the datasheet. This is useful if/when a table has more records than can be displayed all at once, and this will happen (almost) all the time. In the same way, if a table contains one or more records than the width of the table can display, the table would be equipped with a horizontal scroll bar:

The presence or absence of one or both scroll bars is automatically managed by the operating system. Since you will usually not give your users access to tables, you should not be concerned with the management of scroll bars. If your users need to use a table, they know how to use a scroll bar.

Table Navigation Buttons

The lower left side of the table is made of five buttons used to navigate the table or create records, and a text box. Each button plays a specific picture:

Object Role
First Record: allows moving to the first record of the table
Previous Record: allows to move one record back (if there is one) from the current record
Record Indicator: Displays the number representing the current record
Next Record: allows moving you one record ahead
Last Record: Allows moving you to the last record of the table
New Record: Used to enter a new record on the table
 

Table Creation in Datasheet View

 

Fields Names

Once a table displays in Datasheet View, you can simply start entering data into its cells but because the columns would have insignificant names, you should make sure they display names that can identify their type of information.

A field can have almost any name: "Book Title", "This Is The Book Title", "My Book Titles", etc. Some of your fields will have only tinny data, like a person's age, the number of books in an office, a person’s middle initial, etc, it might not be a good idea to give them a name that would take too much space. In the future, when you start programming, it would be easier to handle field names that are composed of only one word, without space.

To name a field, first figure out what the data in the category will be made of. If you are planning to enter employees' salaries in a field, you can just call the field Salary. If you want to specify employees’ names by first name, middle name, and last name, you can use such field names and make them distinct. In this case, you could name the first field as firstname (in one word), the last name would be called lastname. Although this good technique allows you to use one word name, some people including yourself might be confused. The suggestion is to distinguish wherever a new English 

(or the language you are using to design your database) name starts in the field name, by using a starting uppercase. Instead of firstname, you can use FirstName. Instead of fullname, you can use FullName. Instead of firstdayofmonth, you can use FirstDayOfMonth.

To change the name of a field, you can click its column header, such as Field1. You can also right-click a column and click Rename Column. Alternatively, when any cell under a column has focus, on the main menu, you can click Format -> Rename Column and type the new desired name.

Practical Learning: Naming Datasheet Fields

  1. Double-click the header of the first field: Field1. When it is highlighted, type OrderDate and press Enter
     
  2. Click anywhere under the Field2 column to activate it
  3. On the menu bar, click Format -> Rename Column. That puts the field name in edit mode
  4. Type OrderTime
  5. Right-click Field3 and click Rename Column from the context menu
  6. Type Container and press Tab
     

Tables Names

Like everything that is part of a database, a table must have a name that can be used to refer to the object throughout the database in other files that can be linked to the table. Fortunately, when you perform data entry, information you type into a table is saved immediately, as soon as you move to another cell. There are two main ways you can specify the name of a table, either when you save it for the first time or if you decide to rename it.

To save a table you have just created, on the main menu, you can click File -> Save. You can also press Ctrl + S. You could also perform a trick by trying to close the table. You would be prompted to save the table (if its structure has been changed from the structure it had before it was opened).

If a table has not been saved before, you will be asked to provide a name for the table. A table can have any name. With the same concerns we had when creating field names. This time also, there are suggestions made to you. First, the name of a table should reflect the kind of data it is holding. Second, you can name a table with a few words, with spaces; but for the same reasons mentioned earlier, keep the name of a table in one word. The last suggestion sometimes states that you use a prefix that identifies the table as such, a table. The name of a table would be preceded with tbl. If the name includes one word, such as Musicians, you can give the table a name like tblMusicians, another name would be tblStudents. If the name of the table reflects a combination of words, such as Bank Accounts or Students Academic Numbers, start each new word with an uppercase. Here are examples: tblBankAccounts, tblStudentAcademicNumbers, tblMemberRegistrations.

Unlike the previous version of this site, we will not use the convention of prefixing the name of a database object with a three letters. For example, the names of tables will not start with tbl. We came to this decision because most other database environments, including Microsoft SQL Server, do not suggest this rule. Although the lack of this rule may create some confusion, because you will end up having a table and a form with the same name, we will strive to explicitly state what object type we are referring to. For example, we will avoid writing, “Open Employees” or “Open the Employees object”. Instead, we will usually state, “Open the Employees table”. This should clearly indicate that you must first click the Tables button in the Database Window because the tables are only in the Tables section, and then open the indicated table. In the same way, if we write, “Open the Employees form”, you must first click the Forms button in Database Window, then open the indicated form.
 

Practical Learning: Saving a Table

  1. On the Table Datasheet toolbar, click the Save button to save the current table
  2. Type CustomersOrders and press Enter
  3. You will receive a long message box. Click No
  4. To close the table, click its system Close button

Custom Column and Row Layout in Datasheet View

 

Introduction

A database table and its fields are made of special properties that govern how a table displays its data and how the fields behave. Most of these features are highly customizable. The most important properties you will need to know from a table can help you decide what fields a table should display and in what sequence. Although users will usually not have access to your tables, you should still be aware of some of the internal configurations of a table so you would know what they can do on tables. Anticipating some of the features you will need to implement when designing tables, such as hiding and displaying items, freezing and unfreezing them, it is a good idea to know how these features operate on a table.

Inserting a Column

One of the jobs performed when maintaining or improving a table consists of adding new and necessary columns that were not previously available. This can be done in various ways. In the Datasheet View of a table, you can only insert a column in the middle of two existing column or you can insert a new column to the beginning, that is, to the left of the first column. You cannot simply add a new column at the end of, that is, after the most right column of, the table.

To add, that is, to insert a new column, you can right-click the column header that will succeed the new column and click Insert Column. Alternatively, if the caret is positioned in any cell under the column that will succeed it, on the main menu, you can click Insert -> Column.

Practical Learning: Inserting a Column

  1. The Clarksville Ice Scream1 database should still be opened and the Tables button should be selected in the Database window
    On the Database window, double-click the CustomersOrders table to open it
  2. To add a new field, right-click the OrderDate column header and click Insert Column
  3. Right-click the new Field1 column header and click Rename Column
  4. Type Clerk and click the cell under Container
  5. To insert another column, while the Container column has focus, on the main menu, click Insert -> Column
  6. While the caret is under Field1, on the main menu, click Format -> Rename Column
  7. Type Flavor and press Enter
  8. Enter the following records in the table:
     
    Clerk OrderDate OrderTime Flavor Container
    Paulette McIntyre 05/10/2007 10:16 AM Vanilla Cup
    Ralph Ammian 05/10/2007 10:28 AM Chocolate Cream Bowl
    Alex Mendy   11:35 AM Butter Pecan Cone
    Ralph Ammian 05/10/2007   Vanilla Cone
  9. Close the table

Selecting Columns

Many times during design or once users have started using your database, you will find that a sequence of fields is not appropriate. In order to move fields, you should know how to select them:

  • To select a column, click the desired column header and release your mouse
  • To select more than one column, click and hold your mouse on one of them, then drag to cover the other desired column or columns, when all desired columns are highlighted, release the mouse
  • To select more than one column, click one column that will be at one end, press and hold Shift, and then click the column that will be at the other end

Practical Learning: Selecting Columns

  1. Right-click the CustomersOrders table and click Open
  2. To select a column, position the mouse on OrderTime until the mouse cursor turns into a down-pointing arrow
     
  3. Then click
     
  4. Notice that the whole column has been selected
  5. To select columns in a range, click and hold the mouse on Container
  6. Then drag left until you get to OrderTime
     
  7. Then release the mouse
  8. To use another technique of selecting columns, click Flavor
  9. Press and hold Shift
  10. Then click Clerk and release Shift
  11. Close the table

Deleting Columns

After creating a column, if you find out that you do not need it anymore, you can delete it.
If you had created a relationship that the column is involved in, you cannot delete it until you “break” or delete the relationship first.

To get rid of a column you do not need anymore, you can right-click it and click Delete Column.

Practical Learning: Deleting Columns

  1. Double-click the CustomersOrders table to open it
  2. Right-click OrderTime and, from the context menu, click Delete Column
  3. When a message box displays, read it and click Yes
  4. Close the table

Moving Columns

If you find out during design, data entry, or database maintenance that a particular field is misplaced, you will likely want to move it for better navigation or to ease data entry.

To move a column or group of columns in Datasheet View, first select that column or those columns. Click and hold your mouse on it (or one of them). Then, start dragging left or right in the desired direction. While your mouse is moving, a thick vertical line will guide you. Once the vertical line is positioned to the desired location, release the mouse.

Practical Learning: Moving Columns

  1. Open the ROSH database and, on the Database window, click the Tables button. Open the Staff table in Datasheet View
  2. Scroll right or left so you can see the Sate and the ZIPCode fields at the same time
  3. To move a field, position the mouse on the ZIPCode column header until the mouse cursor turns into a down-pointing arrow:
     
  4. Click to select the whole column (that means click once and release the mouse)
  5. Click the selected column header again and hold the mouse down.
  6. Drag in the left direction until the guiding vertical line is positioned between State and HomePhone:
     
  7. Then release the mouse. Notice that the ZIPCode column has moved
  8. Make sure you can see the HomePhone, the WorkPhone, and the MaritalStatus fields.
    To select two fields, position the mouse on HomePhone. With the mouse cursor pointing down, click and drag in the right direction until WorkPhone is selected, then release the mouse.
  9. Click one of the selected column headers and hold your mouse down
  10. Drag in the right direction until the guiding vertical line is positioned on the right side of MaritalStatus
  11. Then release the mouse
  12. To select a group of columns, click the MaritalStatus column header to select it
  13. Press and hold Shift, then click the WorkPhone column header and release Shift
  14. Click one of the selected column headers and hold your mouse down.
  15. Drag in the left direction until the guiding vertical line is positioned to the left of Extension:
     
  16. Then release the mouse
  17. Save and close the table

Selecting Records

To select a row or record in Datasheet View, click the desired row header and release your mouse. To select more than one record, click and hold your mouse on one of them, then drag to cover the other desired row or rows. When all desired rows are highlighted, release the mouse. Another technique used to select more than one row consists of clicking one row that will be at one end, press and hold Shift, and then click the row that will be at the other end.

Practical Learning: Selecting Records

  1. Open the Clarksville Ice Scream1 database (it should still be in the list of Most Recently Used files under the File menu category)
  2. Double-click the CustomersOrders table to open it
  3. To select one record, position the mouse on the gray button to the left of the third record until the mouse turns into a right pointing arrow
     
  4. Then click
  5. Notice that all cells of the second records are highlighted
  6. To select a range of records, click the gray button to the left of the fourth record. Then click once
  7. Press and hold Shift. Then click the gray button to the left of the second record, and release Shift
     
  8. Notice that three records have been selected
  9. Close the table

Deleting Records

If a record is not needed anymore or has become irrelevant in a database, you can delete it. To do this, right-click the record selector button, which is the gray box, on the left side of the most left field of the record, and click Delete Record from the context menu.

Practical Learning: Deleting Records

  1. Open the CustomersOrder table
  2. To delete a record, right-click the gray button to the left of the second
     
  3. Click Delete Record
  4. A warning message box will appear. Read it and click Yes
  5. To delete more than one record, click and hold the mouse on the gray button to the left of the second record, then drag down to include the third record in the selection
  6. On your keyboard, press Delete
  7. Read the warning message box and press Enter
  8. To close the table, press Ctrl + F4

Columns Width and Rows Height

The columns and rows of a table use some default values to display their records and fields. When data exceeds the regular width of a column, part of the information would be hidden. If a particular column contains data that you want to display at all times, you can enlarge the column. On the other hand, if a column displays tinny pieces of information, you can narrow the column to let it just fit the data as desired. In the same way, you can heighten or shrink rows of a table as you see fit.

There are various techniques you can follow to widen or narrow a column. You can position the mouse on the right border of the column header. The mouse pointer would change into a horizontal double arrow crossed by a vertical beam. If you double-click, the column would be sized to the widest value of the column, provided the widest value is wider than the column header. If the widest value is narrower than the column header, the column width would be wide enough to display the caption of the column. On the other hand, you can change a column's width by clicking on the column's right border and dragging in the desired direction.

To set or change the rows height, you can position the mouse on one of the rows lower border, then click and drag in the opposite direction.

Practical Learning: Changing Columns Width and Rows Height

  1. Open the Altair Real Estate1 database and, in the Database window, click the Tables button
  2. From the Tables section of the Database window, double-click the Employees table to open it
  3. To resize the First Name column, position your mouse on the line between First Name and Last Name until the mouse pointer appears as an I-beam (also called a pipe) with a horizontal double-arrow:
     
  4. Then double-click
  5. To resize the Title column, position your mouse on the line between Title and City:
     
  6. Then click and drag in the right direction until the mouse is positioned in the middle of City:
     
    Enlarging a column
  7. Release the mouse
  8. Right-click the Last Name column header and click Column Width...
  9. In the Column Width dialog box, click Best Fit
  10. Right-click the Employee ID column header and click Column Width...
  11. In the Column Width dialog box, type 10.15 to replace the default value
     
  12. Click OK
  13. Save and close the table

Lesson Summary

 

MOUS Topics

S8 Create a database (... in-design view)
S11 Modify field properties
S25 Delete records from a table
 

Exercises

 

Yugo National Bank

  1. Create a blank database and name it Yugo National Bank
  2. Using the Table Wizard, create a table based on the Accounts sample table of the Personal category. Include the following fields: AccountTypeID, AccountType, and Description. Save the table as AccountTypes and fill it up as follows:
     
    Account Type ID Account Type Description
    1 Checking  
    2 Saving  
    3 CD  
  3. Using the Table Wizard, create a table based on the Employees sample table of the Business category. Include the following fields: EmployeeID, DepartmentName, EmployeeNumber, FirstName, LastName, Title, EmailName, WorkPhone, Extension, Address, City, StateOrProvince, PostalCode, Country, HomePhone, Salary, and Notes. Save the table as Employees and fill it up with employees records
  4. Using the Table Wizard, create a table including the CustomerID field from the Customers sample table of the Business category. From the the Accounts sample table of the Personal category, include the following fields AccountNumber, AccountTypeID, AccountName. From the Addresses sample table, include the following fields: Address, City, StateOrProvince, PostalCode, Country, EmailAddress, HomePhone, WorkPhone, WorkExtension, DateUpdated, and Notes. Save the table as Customers and fill it up with sample customers records

Watts A loan

  1. Catherine Watts lives in Baltimore, Maryland, US. Eight months ago, while she lived with her father, one weekend, she went to attend the wedding of her friend in Chicago. Meanwhile, her father had a heart problem and was rushed to the hospital. The attending doctor wrote on a chart that Catherine's father would be kept nightly for further examinations. While her father was still lying in bed in the hallway and sleeping, a nurse came back and from some events that nobody recalls, Mr. Watts' chart was modified. Next, he was taken to a surgery room since his chart  now indicated that he had been in an unrecoverable accident but that he was a happy organ donor. In the next few minutes, his body was cut in various pieces. His right liver was sent to Canada while the other gave new hope to a patient in California. His left eye gave new site to a woman in Mexico and his skin allowed a man who had been in the center of a mass fire a new cover for his body. The rest of his organs were spread in the region like salt in a boiling water. When the attending doctor came back to look for his patient, he found out what had happened but it was quite late to undo anything. When Catherine came back from Chicago and was asked to recognize at least what was left of her father, she was furious but calmly considered it was an accident. At her friend's insistence, she sued the hospital and got a five-million-dollar settlement. To make sure she would not spent all that cash in bars and night clubs, her friend encouraged her to open a business. Since Catherine had taken some classes in banking management, she decided to open a money lending institution. She would lend money to individuals through car dealers, music instruments shops, furniture stores, and personal loans, etc. She has contracted you to create an application that can help her manage her business.
    Create a blank database and name it Watts A Loan
  2. Using the Table Wizard, create a table with the following fields: CustomerID, (and from the Mailing List sample table) FirstName, MiddleName, LastName, Address, City, State, PostalCode (rename it ZIPCode), Country, HomePhone, WorkPhone, EmailAddress, and Notes. Name the table as Customers
  3. Using the Table Wizard, create another table with the following fields: TransactionID, TransactionNumber, TransactionDate, TransactionAmount, and Notes. Save the table as Transactions
 

Previous Copyright © 2002-2007 Yevol Next