Introduction to Microsoft Access

Microsoft Access is a computer application used to create and manage computer-based databases on desktop computers and/or on connected computers (a network). Microsoft Access can be used for personal information management (PIM), in a small business to organize and manage all data, or in an enterprise to communicate with servers.

Like any other computer application, in order to use Microsoft Access, you must first open it. There are various ways this can be done. Microsoft Access is a classic computer application and it gets launched like the usual products you have probably been using. As such, to start this program, you could click Start -> (All) Programs -> Microsoft Access:

As a regular member of the Microsoft Office suite of applications, if your installation created a sub-menu on the Start menu, you could click Start -> New Office Application and proceed from the New dialog box.

Although Microsoft Office 97 and Microsoft Office 2000 get installed in the C:\Program Files\Microsoft Office folder, they treat the shortcuts that launch them differently. The applications that are part of Microsoft Office 97 designate their shortcuts with full names and these are installed in the Microsoft Office folder. Microsoft Office 2000 (Premium) uses shortcut names to designate its shortcuts and they are installed in the Microsoft Office\Office folder. This means that you could launch an application from Windows Explorer or My Computer. Therefore, in order to launch Microsoft Access, locate its shortcut in Windows Explorer or My Computer and double-click it:

If you have a Microsoft Access database such as an E-Mail attachment, a file on a floppy disk, on the network, or in any other means, once you see its icon, you can double-click it. Not only will this action launch Microsoft Access, but also it will open the file.

You can also launch Microsoft Access from a shortcut. If you happen to use the software on a regular basis, you can create a shortcut on your desktop or on the Quick Launch area. Many users also take advantage of the Microsoft Office Shortcut Bar. Sometimes, the icon you need will not be there; in that case you should insert it manually.

If you are working on a network of related computers, your database may be located in another computer. In this case the network or database administrator would create a link or shortcut to the drive that is hosting the database. You can then click or double-click this link or shortcut to open the database and, as a result, launch Microsoft Access.

Microsoft Access Shortcuts

Whenever you find out that you are using a particular program or file most regularly, you should have an icon on the desktop that can lead you to it quickly. This icon is called a shortcut. There are various techniques used to create a shortcut. Probably the first thing you should find out is where your application is located. You can find out by doing a search on the computer. Except on Microsoft Windows 95, you can create a shortcut from the Programs menu.

By default, the shortcuts on the Microsoft Office 97 applications are located in the C:\Program Files\Microsoft Office folder. And these shortcuts hold the same names as their respective programs; those will be Microsoft Word, Microsoft Excel, Microsoft PowerPoint, etc. The shortcuts of the Microsoft Office 2000 applications are located in the C:\Program Files\Microsoft Office\Office folder. These shortcuts use another naming convention. They are Msaccess, Winword, Excel, or Frontpg, just to name a few.

To create a shortcut from Windows Explorer or My Computer, you can right-click the icon or the shortcut of the application, position your mouse on Send To, and click Desktop (Create Shortcut).

To create a shortcut from the Start menu, click Start -> (All) Programs. When the program appears, you can right-click it, position the mouse pointer on Send To and click Desktop (Create Shortcut). 

Practical Learning: Creating a Shortcut 

1. Right-click on an empty area of the Desktop ->  New -> Shortcut
    
   
2. On the first page of the wizard, click the Browse button
3. Make sure the Look In combo box is displaying the C: drive
4. Double-click Program Files
5. Double-click Microsoft Office
6. If you are using Microsoft Access 97, click Microsoft Access. If you are using Microsoft Access 2000, double-click Office. Then click Msaccess:
    
   
7. Click Open
8. Click Next
    
   
9. If you are using Microsoft Access 97, notice the name of the shortcut
   If you are using Microsoft Access 2000 or later, change the name of the shortcut to Microsoft Access:
    
   
10. Click Finish

Microsoft Access Interface

To get better acquainted with the user interface of Microsoft Access, we will use this section as a Practical Learning exercise.

1. As a regular Windows application, Microsoft Access shares some characteristics that are common to other programs. The top section of the interface is made of wide bar called the title bar:
   
   
   
   The left section of the title bar displays a small picture known as the system icon. This icon is used to identify the application. The icon holds a list of actions you can perform to close, minimize, maximize, move or restore the application. To perform any of these actions, you would click the system icon. This would display its list:
   
   
   
   This list can also be referred to as the system menu. To use one of its items, you can click it.
   
   To experiment with the system menu, click the system icon. If the Restore item is available, click it first
2. Click the system icon again and click Move. Notice that the mouse pointer changes its shape into a cross
3. Press and hold Shift (with your left hand). Then press the right arrow key (with your right hand) three times and press the down arrow key twice. Notice that the Microsoft Access window moves by units
4. To move the window slowly, press and hold Ctrl(with your left hand). Then press and hold the left arrow key (with your right hand) while you are still holding Ctrl. Notice that these last two times, the window moves slowly
5. To keep the window in its new position, press Enter
6. The main area of a title bar is a long bar actually referred to as the title bar. This section is also used to perform the same operations available on the system menu. There are other operations you can perform different than the system menu depending on the way you click the main area of the title bar.
   To see an example, double-click the title bar. Notice that this maximizes Microsoft Access.
   The right section of the title bar displays three small squares referred to as the system buttons. They are used to minimize, maximize, restore or close Microsoft Access. These items are

The Main Menu

1. Under the title bar, there is a horizontal list of words. This list is made of items such as File, Edit, View, etc. Since there are various kinds of menus on this application, the menu on top will be referred to as the Main Menu and sometimes the Menu Bar.
   To use a menu item, you click one of its words and the menu expands. If an item is missing from the main menu, you can customize it.
   To experiment with the main menu, click File. There are four main types of menus you will encounter.
   When clicked, the behavior of a menu that stands alone depends on the actions prior to clicking it. Under the File menu, examples are Close or Exit
2. A menu that is disabled is not accessible at the moment. This kind of menu depends on another action or the availability of something else.
   To see an example, one the main menu, double-click Window:
   
3. A menu with three dots means that an intermediary action is required in order to apply its assignment. Usually, this menu would call a dialog box where the user would have to make a decision.
   As an example, on the main menu, position the mouse on File and click Open...
4. On the dialog box, click Cancel
5. A menu with an arrow holds a list of menu items under it. A menu under another menu is called a submenu. To use such a menu, you would position the mouse on it to display its submenu.
   For example, on the main menu, click Tools and position the mouse on Database Utilities
    
   
6. To dismiss the menu, click Tools
7. Notice that, on the main menu (and any menu), there is one letter underlined on each word. Examples are F in File, E in Edit, V in View, etc. The underlined letter is called an access key (the word access has nothing to do with Microsoft Access, it is used in this sense throughout Microsoft Windows and other operating systems). The access key allows you to access the same menu item using the keyboard. In order to use an access key, the menu should have focus first. The menu is given focus by pressing either the Alt or the F10 keys.
   To see an example, press Alt
8. Notice that one of the items on the menu, namely File, has its borders raised. This means that the File menu item has focus
9. Press t and notice that the Tools menu is expanded
10. When the menu has focus and you want to dismiss it, you can press Esc.
    For example, press Esc
11. Notice that the Tools menu has collapsed but the main menu still has focus
12. Press f then press o. Notice that the Open dialog box displays.
13. To dismiss the Open dialog box, click Cancel
14. On some menu items, there is a key or a combination of keys we call a shortcut. This key or this combination allows you to perform the same action on that menu using the keyboard.
    If the shortcut is made of one key only, you can just press it. If the shortcut is made of two keys, press and hold the first one, while you are holding the first, press the second key once and release the first key. Some shortcuts are a combination of three keys.
    To apply an example, press and hold Ctrl, then press o, and release Ctrl
15. Notice that the Open dialog box opens. To dismiss it, Click Cancel

From now on, in this book,

Press

Means

T

Press the T key

Alt, G

Press and release Alt. Then press G

Ctrl + H

Press and hold Ctrl. While you are still holding Ctrl, press H once. Then release Ctrl

Ctrl + Shift + E

Press and hold Ctrl. Then press and hold Shift. Then press E once. Release Ctrl and Shift

The Toolbars

Under the menu bar, there is another bar made of various buttons. This is called a toolbar. The toolbars change a lot in Microsoft Access. As you spend more time with this application you will learn how to recognize these toolbars. Each toolbar has a proper name and we will learn how to recognize them.

At times, there will be many toolbars that come and go while you are using Microsoft Access. For this reason, we will refer to each toolbar by its name. To know the name of a toolbar, you can right-click any word on the menu bar or any button on the toolbar. If you have only one toolbar on your screen, its name will have a check box. The other name(s) on the context menu is (are) the one (those) you can add to the screen if you wish:

You could also create your own toolbar.

The Status Bar

The status bar is a long horizontal bar that spans the whole button section of Microsoft Access. It will be used to provide some assistance or information about an item that is displaying or being accessed in Microsoft Access. At this time, it may be displaying Ready (and it means it)

Microsoft Access Database File

A Microsoft Access database is primarily a Windows file like any other. It must have a location, also called a path, which indicates how the file can be retrieved and made available. Although you can create a database on the root directory such as the C: drive, it is usually a good idea to create your files, including your databases, in an easily recognizable folder.

Starting with Microsoft Windows 95 but except Windows NT 4.0, the operating system creates a folder called My Documents that provides a convenient place for a user to create files. With Windows 2000, XP, and 2003, there is also a My Documents folder but because various people may use the same computer, there is a different My Documents folder for each user. When a user logs in, the operating system locates his or her corresponding My Documents folder and makes it available. Throughout this site, we will use a folder called Exercises and located in a parent folder called Microsoft Access Desktop Databases.

1. As you probably know already, there are various ways you can create a folder. After installing the exercises, open Windows Explorer or My Computer and, on the left frame, select the folder that contains our lessons
2. Create a folder named Exercises

Throughout this site, we will learn different techniques of creating a database. For now, a database is first of all a Windows file. It is mainly created from Microsoft Access. If you are just starting Microsoft Access, you can use one of the two top radio buttons. If Microsoft Access is already opened, to create a database, you can display the New dialog box and select an option from the icons on the property pages. Like every file in the computer, a database must have a name that identifies it. This name must be specified when creating the database.

1. To create a new database, on the main menu, click File -> New…
2. In the New dialog box, click the Databases tab. Click Asset Tracking and click OK
3. Locate the Exercises folder that will contain your databases and display it in the Save In combo box
    
   
4. Click Create
5. When the Database Wizard dialog box comes up, click Finish

Unlike some other programming environments, Microsoft Access considers a database as a single Windows file with a name. We will eventually learn that this file can contain various objects that actually make up a database. Therefore, a Microsoft Access database is opened as a normal file.

To open a database, if you are just starting Microsoft Access, from the Microsoft Access dialog box, you can click the bottom radio button, Open An Existing File, locate the folder that contains the database, select the database file and click Open.

If you locate a database in Windows Explorer, My Computer, My Documents, or any other window that displays the Microsoft Access file icon, you can just double-click the icon and open the database. If you received a database as an email attachment, you can also open the attachment and consequently open the database file.

Microsoft Access keeps a list of most recently used databases under the File menu. In Microsoft Access 97, the list is limited to 4. In versions of Microsoft Access after 97, you can increase the list of MRU (most recently used) files in the General tab of the Options dialog box. To open a file that was previously used, you can click File from the main menu and click the database file from the list.

Practical Learning: Opening a Database

1. Open Windows Explorer or My Computer and locate the folder that contains the resources that accompany our lessons. Copy a folder called GSC and paste it in your Exercises folder
2. In Microsoft Access, to open a database, on the main menu, click File -> Open…
3. Locate your Exercises folder and display it in the Look In combo box. Then double-click the GSC folder to open
4. Click Grier Summer Camp
    
   
5. Click Open

Overview of Database Objects

After creating or opening a database, unless the product is setup otherwise, the first object that appears is a rectangular box named the Database window. It is different on Microsoft Access 97 and Microsoft Access 2000 (and later versions).

The Database window is a classic object of Windows applications. It is equipped with a system icon on the left side of its title bar and three system buttons on the right side. Based on this, you can maximize, minimize, restore, or close it. You can also resize it by dragging one of its borders or corners. Because the Database window holds all objects that are part of a database, if you close the Database window, it also closes the database but leaves Microsoft Access opened. Because Microsoft Access is a Multiple Document Interface (MDI) application, if you maximize any of its child objects, such as the Database windows, the other objects that you subsequently open would be maximized also. In all versions of Microsoft Access, objects are organized in categories.

In Microsoft Access 97, each category is represented by a property page. Therefore, to select a category, you can click its tab.

Once in the property page of a category, to open an object:

• You can double-click it
• You can click it to select it, then click the Open button on the right side
• You can also right-click an object and click Open

One of the biggest changes that Microsoft Access 2000 brought was on the Database window. It got completely redesigned and highly improved. Like all classic windows, it is equipped with a title bar similar to the Database window of the 97 version as we described above. Under the title bar, the Database window is equipped with a contextual toolbar. This means that the toolbar responds according to the object that is selected in the Database window.

Like all releases, objects in Microsoft Access are organized in categories. In the 2000 and later versions, objects are represented by the Objects Bar. Therefore, to select a category, you can click its button. Besides the buttons that represent categories, when you click a button, one, two, or three links allow you to create objects of that category. For example, you can create a table by double-clicking the Create Table By Using Wizard link.

To open an object:

• Double-click it
• Click it to select it and click the Open button on the Database window’s toolbar
• Right-click it and click Open

Besides providing the ability to create a new object or open an existing one, you can also delete an object using the Database window’s toolbar. To do this, you can click the object to select it. Then, on this toolbar, click the Delete button. The Database window’s toolbar also provides four view buttons that allow you to change the way the list displays in the right side of the view. If you have used Windows Explorer, My Computer, or My Documents, you are probably familiar with these buttons. For example, here is the Database window that displays its list in Large Icons:

Database Objects: Tables

A Microsoft Access database is a file made of various internal objects: tables, queries, forms, reports, etc. All these are managed from an object called the Database Window. The objects are kept in categories. To access an object, you click the button that corresponds to its category.

A table is the central point of a database, because all data is stored in tables. For better organization, you will have various tables in your database, each for a different purpose.

Each table is recognized by its name. To open a particular table, you can double-click it. You can also right-click a table's name and click Open. If the desired table is already selected on the Database Window, you can click the Open button to open it.

Practical Learning: Opening a Table

1. On the Database Window, in the Objects Bar (Microsoft Access 2000) click the Tables button or the Tables property page (97)
2. Double-click Employees
    
   
3. To close the table, click its Close button

Database Objects: Queries

A table can be large depending on the information it holds. To further organize your data, you should be able to retrieve necessary information for a specific purpose. The solution is to create a query (or queries) in order to limit part of the data in a table for a specific goal, for better managing or searching. That's the role of a query.

Just like tables, queries are managed from the Database Window in their own category. You can open a query the same way you open a table.

Practical Learning: Opening A Query

1. On the Database Window, in the Objects Bar (2000) click the Queries button or the Queries property page (97)
2. Double-click List of Registrants By T-Shirt Size to open it:
    
   
3. After viewing the query, to close it, click its Close button

Database Objects: Forms

Tables are used to create the data in your database, but they are usually not friendly looking, as far as the users are concerned. Forms are windows objects used to view and/or enter data in your database.

A form can combine data that is part of one or more tables or queries. Forms are the window interfaces that you usually will ask your users to access when performing data entry in your database.

Practical Learning: Opening A Form

1. On the Database Window, click Forms
2. Double-click MembersByGender
    
   
3. After viewing the form, click its Close button dismiss it

Database Objects: Reports

A report is the organized document that allows a user to print information from a database. A report can include different parts or details about your database. It could include data from a table or a query, it could also get its data from various tables, queries, forms, or data that is calculated from other data coming from different tables or forms. You could also create a completely independent report whose content is not related to any data on an object.

Practical Learning: Opening A Report

1. On the Database Window, click the Reports button
2. Double-click Camps
    
   
3. After viewing the report, click its Close button

Database Objects: Pages

Starting with Microsoft Access 2000, Data Access Pages allow you to publish your forms as web pages on the Internet or on an Intranet. Their use is not as common as the other database objects but they provide thoughtful functionality.

Practical Learning: Data Access Pages

1. On the Database Window, click the Pages button
2. Double-click Camps
    
   
3. After viewing the page, click its Close button 

Macros

When creating Microsoft Access databases, you can customize certain behaviors of your products. Some of these behaviors can be automated through a combination of buttons. For example, you may want to open one document (form) from another. You can use macros to do that. In such case, you would not have to write code. Since macros are becoming less and less popular (for good reasons), we will not spend much time learning them.

Modules

Modules are pieces of code used to impose particular behaviors to your application to make it better. They are written in Microsoft Visual Basic. Modules are more flexible and extensive than macros, although they are usually written for various and particular circumstances. One example is to print a receipt after a customer has bought paint in a store.

Macros and Visual Basic code can be used on the same application. Sometimes you will prefer one to the other, and sometimes you will keep using VBA (Visual Basic for Applications). Whenever possible, you should use VBA instead of macros.

Although modules are represented on the Database Window like the database objects we have seen so far, to view, read, or access a module, you need to get to Microsoft Visual Basic. There are various ways you could do that. A module could be an independent piece of code that is not related to another object, in this case its name would appear in the Database Window; all you have to do is double-click it and that would launch Microsoft Visual Basic. If you select a form or report from the Database Window, you will see the Code button on the Database toolbar. If you click that button, it would launch Microsoft Visual Basic. If the form or report has some code associated (or bound) to it or to some of its controls, the code would display; otherwise the programming window look empty (it should have at least one line displaying Option Compare Database.

Practical Learning: Opening Microsoft Visual Basic

1. On the Database Window, click Modules
2. Double-click modUtilities
3. Notice that Microsoft Visual Basic opens (if you are using Microsoft Access 97, you should see a window called the Code Window):
    
   
4. To return to Microsoft Access, click the View Microsoft Access button (if you are using Microsoft Access 97, close the Code window by clicking its Close button )
5. To close the form, click its Close button 

Microsoft Access Help

Context-sensitive help refers to help provided on a specific item on the screen. Such help is provided for objects that are part of Microsoft Access interface. It includes objects like buttons on toolbars, an object opened such as a table, a query, a form, or a report, etc. Context-sensitive help is also referred to as “What’s This?”.

To get context-sensitive help, press Shift + F1. In addition to the traditional arrow, the mouse cursor would be accompanied by a question mark. To get help on an object, you can just click it.

Another type of context sensitive help is provided in various dialog boxes. They display a button with a question mark on the left of the system Close button. To use this type of help, click the question mark button and click the item on which you need help.

1. Press Shift + F1 and notice the new mouse cursor
2. On the Database toolbar, position the mouse on the Open button
    
   
3. Click
    
   
4. After viewing help, press Esc
5. To get context-sensitive help on a dialog box, on the main menu, click Insert -> Form
6. In the New Form dialog box, click the What’s This button 
    
   
7. Click the list box in the middle right side of the dialog
    
   
    
   
8. After viewing help, click the What’s This button again and click the combo box, in the bottom right side of the dialog box
    
   
9. After viewing help, press Esc and click Cancel

The Office Assistant

The Office Assistant is a “character” or a “virtual person” whose main job is to provide instant help when using a Microsoft Office product. The Office Assistant is usually on top of Microsoft Access while you are working. If you do not like the way it looks, you can click it and click Options. This would present you with the Office Assistant property sheet in which the Gallery property page allows you to select a different Office Assistant. The Options property page allows you to configure the behavior and responsiveness of the Office Assistant.

To use its service, just click it, then type a word, a sentence, or a question. After pressing Enter, a primary list of possible matches would be displayed. If you do not find what is close to your request, you can use the available options or change your request.

If you do not want the Office Assistant on the screen while you are working, you can hide it. To do this, in Microsoft Access 97, you can click its Close button. In Microsoft Access 2000, on the main menu, you can click Help -> Hide Office Assistant. To display it when it is not available, on the main menu, you can click Help -> Show the Office Assistant.

Practical Learning: Using the Office Assistant

1. If the Office Assistant is not displaying on the screen, on the main menu, click Help -> Show Office Assistant
   To use the Office Assistant, click it
2. Type Create Table
    
   
3. Click Search
4. On the list that appears, click Create a table
5. On the HTML Help window that appears, read the text and click one of the blue links
6. After reading it, on the toolbar of the HTML Help window, click the Back button
    
   
7. Click another link
8. After reading it, close the HTML Help window that opened but do not close Microsoft Access
9. In Microsoft Access, click the Office Assistant again. Notice that the last request re-displays. Click Search
10. This time, click See More…
11. In the second page, click another link such as Create A Field For Text Or Memos
12. Do not perform but read the instructions. After reading the instructions, close the HTML Help window

Online Help

Online help is a separate program that provides help on Microsoft Access. There are two main types of online help:

• Microsoft Access ships with help available from the main menu. To use it, you can click Help to display the list or available categories. The most direct way of accessing help consists of clicking Microsoft Access Help. This would bring up the Office Assistant and you would proceed as we did above
• If you have access to a Microsoft Developer Network (MSDN) CD-ROM or DVD, which is the help system provided to programmers who use Microsoft technologies (such as Microsoft Visual Studio), it includes a section on Microsoft Office, which internally includes a sub-category on Microsoft Access. On that help system and in the left frame, you can expand the link that displays Office. Then visit links to Microsoft Access or Microsoft Office:
   
  

It is important to note that help in Microsoft Access 2000 and above is different than help in Microsoft Access 97. In the 97 version, help was provided through a system called WinHelp. After that, Microsoft decided to start using HTML Help. Like everything in computer applications, each of these help systems has its advantages and disadvantages. Over all, the process of getting help remains the same but it is displayed differently.

Internet Help

Although help on the Internet tends to be disparate, it is still the widest form of help available. This is provided in web sites, web pages, newsgroups, support groups, etc. As the publisher of the database environment, it is only natural to refer to the Microsoft corporate web site first for help. The Microsoft web site is divided in categories. A web site is dedicated to Microsoft Access at http://www.microsoft.com/access. You can get help at http://support.microsoft.com. Probably the most visited site of Microsoft for developers of all Microsoft products is http://msdn.microsoft.com. This last site provides a tree-based list that presents items in categories.

Microsoft Access Exit

Since Microsoft Access shares the same functionality you are probably familiar with from using other applications, you can close it easily.

• To close Microsoft Access, from the menu bar, you can click File -> Exit
• To close Microsoft Access from the system icon, you can either click it and click Close, or you can double-click its system icon
• To close Microsoft Access from its title bar, you can click its Close button
• To close Microsoft Access like any regular window of the Microsoft Windows applications, you can press Alt + F4
• To close Microsoft Access using mnemonics, you can press Alt, F, X.

Practical Learning: Closing Microsoft Access

• To close Microsoft Access, click the Close button on the top right corner of the window

MOUS Topics

S4

Use the Microsoft Office Assistant

S5

Select an object using the Objects Bar

Exercises

Bethesda Car Rental

1. If you have Internet connection, go to http://www.kbb.com
2. Perform a search to look for a Retail 1996 Honda Accord DX Sedan 4D
3. During your search, change different options and mileage and verify how the price changes

US Senate

1. Go to http://www.senate.gov
2. Review the names of senators and all available information about them
3. Find the name of the Chairman of the Finance Committee.

Introduction to Data Sheets

Creating a Database

The Database Wizard

Many techniques allow you to create a database, the fastest of which consists of using one of the provided examples. Microsoft Access 97 shipped with 22 sample databases while Microsoft Access 2000 ships with 10. Furthermore, the 97 version allowed to provide sample data into the database. This is not available with the 2000 release. The databases that ship with Microsoft Access can help you in two main ways: they provide a fast means of creating a database and you can learn from their structure.

To create a database using one of the samples, there is a little detail to follow depending on whether you had launched the program already or not. If Microsoft Access is not running, you can start it. When the first dialog box comes up, you can click the second radio button: Access Database Wizard, Pages, And Projects and click OK. If the program is already on your screen, on the menu bar, click File -> New Database…

The New dialog box displays two property pages labeled General and Databases. If you want to create a database based on one of the samples, you can click the Databases property page. A list of the sample databases appears. You can then choose one and click OK.

When creating a database using one of the samples, depending on the sample you selected, the Database Wizard will display a few objects and suggest some fields for your database. Some fields are already associated with the objects and some other fields can be added. You can examine them, then add some fields you think are important for your database. You will also have the option of selecting a design layout. Some of the sample databases have been configured to require information about the company you are creating the database for..

Practical Learning: Creating a Database Using a Sample

1. Start Microsoft Access if necessary and click the second radio button.
   If you are using Microsoft Access 97, it is the Database Wizard radio button.
   If you are using Microsoft Access 2000, it is the Access database wizards, pages, and reports radio button
    
   
2. And click OK. This opens the New dialog
3. From the New dialog box, click the Databases tab if necessary and, from the Databases tab, click Time and Billing
    
   
4. Click OK
5. Locate the Exercises folder and display it in the Save In combo box
6. Delete the content of the File Name text box of the File New Database dialog box and type Rockville Techno and click Create
7. When the first page of the wizard comes up, click Next
8. For this exercise, on the Tables In The Database list box, click Information About Employees:
9. On the Fields In The Table list, click the Employee Number check box
    
   
10. Click the check box of Email Name
11. Scroll down and click the Home Phone check box
12. Click Next
13. For the style of the screen display, choose International to preview one of the samples and click Next
14. For the style of the printed report, click Bold to preview that display
15. Click Formal and click Next
16. For the title of the database, type Rockville Technologies
     
    
17. Click Next
18. Then click Finish
    While the Database Wizard is creating a database for you, dialog boxes will be flashing from time to time. If you don't have a printer, a nasty dialog box will be giving an error. If this happens, click OK all the time. In this case, after this lesson, use the Add Printer wizard from the Printers window to install a "fake" printer. One way or another, Microsoft Access needs a printer for the reports
19. When asked to provide information about your customer, click OK
20. In the Company Name text box, type Rockville Technologies, Inc. and press Enter
21. In the Address text box, type 5026 Towers Road Suite D14 and press Enter
22. In the City text box, type Rockville and press Enter
23. In the State field, type Maryland and press Tab
24. In the Postal Code field, type 20852-1644 and press Tab
25. In the Country field, type USA and press Tab
26. In the Payment Terms, type 60 Days
27. Click the Invoice Description field and type For more information on our company, visit us at http://www.rockvilletechnologies.com
28. Press Tab
29. In the Phone Number field, type (301) 869-9814 and press Tab
30. In the Fax Number field, type (301) 869-9618
     
    
31. After entering the necessary data for your customer, to close the company's form, click its system Close button
32. Notice that the database opens with the switchboard.
    To close the switchboard, click its system Close button
33. On the lower left corner of MS Access, click the bar of the Database window and, on the menu that appears, click Restore

Database Properties

Your screen may be made of a large box at this time: this is the Switchboard, and it is a form. A special window you will be using most regularly is called the Database Window. Whenever it is minimized or hidden, you can display it by pressing F11 (unless it has been configured to (almost) never display).

Whether you work alone or in a group, communication and documentation are important. Your database project has its own properties that you can

use to find out some details about your file, to enter some notes about the project, or to give directives to other people who have access to the database. 

Practical Learning: Using Microsoft Access

1. The Rockville Technologies database should still be opened
   To display the Database Window, press F11
2. On the main menu, click File and observe the menu items under File
3. While the File menu is still selected, move the mouse to Tools and click Startup… 
4. The Startup dialog box allows you to set some settings that the current database will follow when it starts.
   In the Application Title, type Rockville Technologies, Inc.
5. Click the arrow of the Display Form/Page combo box and select (None). In case you wonder why (None) is between parenthesis, this is done so that if there are other things in the list, None would always be on top of the list. That way, it would not be between two other objects (forms and Data Access Pages) on the list.
6. Click the browse button of the Application Icon text box
7. On the Icon Browser dialog box, locate the folder that holds the resources for our lessons
8. Click the Rockville Techno icon and click OK
9. Accept all the other defaults:
    
   
10. Click OK
11. Notice that, on the title bar, the database is now using another icon than the default one
12. On the main menu, click Tools -> Options…
13. The Options dialog box allows you to control some settings that are related to Microsoft Access.
    For example, click the General tab
14. Change the content of the Default Database Folder text box to the Exercises folder you will be using throughout our lessons
15. Increase the Recently Used File List combo box to 8
     
    
16. Click OK
17. As mentioned already, each toolbar on the database has a particular name. To find out the name of the current toolbar, right-click any button on it
18. Notice that the Database name has a check box. This means that toolbar is displaying at this time. In this case, this toolbar is called the Database Toolbar
19. On the menu bar, click File -> Database Properties
20. Once more, from the menu bar, click File -> Database Properties
21. From the Properties dialog, click the General tab. Examine the various sections. Notice the size of the current database
22. Click the Summary tab
23. Make sure the Title is set to Rockville Techno
    Click the Subject text box and type Business Regular Activities 
24. Click the Author text box and type Lucien Durand
25. Click the Manager text box and type Christine Lotts
26. Click the Company text box and type Rockville Technologies, Inc.
27. Click the Category text box and type Business
28. Click the Keyword text box and type business, clients, projects, transactions
29. Click the Comments text box and type: This software product is used by the company to conduct its regular business activities and related transactions. For more information, contact Lucien Durand or Christine Lotts. Refer to the Address Book for their phone number, extension, and E-Mail Address.
30. Click the Hyperlink Base text box and type http://www.rockvilletechnologies.com
     
    
31. Click the Statistics, Contents, and Custom property pages and review their contents.
    The Statistics tab gives you statistics about your database and its summary access.
    The Contents tab shows a list of the components that are part of your database.
    The Custom tab shows, and allows you to customize, the field names associated with your database.
32. When you have finished working with the database Properties, click OK (if you click Cancel, any change(s) you have made will be discarded)

Tables Fundamentals

the wizard. To use the Table Wizard, on the main menu, you can click Insert -> Table. Alternatively, on the Database Window, when in the Tables section, you can click the New button. These two actions would display the New Table dialog box from where you would select Table Wizard. If you are using Microsoft Access 2000 and later, from the Tables section of the Database Window, you can double-click Create Table By Using Wizard
 

In the Table Wizard, the tables are organized in two primary categories: Business and Personal. To select one of those categories, you can click its radio button. Each main category is made of various sample tables. To select a sample table, you can click its name in the Sample Tables list. In the middle, the Sample Fields list, the fields associated with the selected sample table are displaying. From that list, you can select the desired field(s). Once a field has been selected, it displays in the Fields In My New Table list. You can then select a different table to mix fields from as many tables as desired. The 4 buttons between the Sample Fields list and the Fields In My New Table list allow you to add or subtract fields. To help with selection and de-selection, the wizard provides four buttons:

During field selection, if you select a field, its corresponding name appears in the right list. If you select a field of the same name more than once, for example, if you select Address twice, the 2nd Address would be called Address1. Sometimes that will be what you want, and sometimes it will be by mistake. If then you make a mistake when selecting fields, you can double-click the unwanted field in the Fields In My New Table list and that field will be removed.

After making your choices, you can keep the names provided by Microsoft Access into your table, or you can rename any field to suit your needs. To rename a field, first select it in the Fields In My New Table list. Then, click the Rename Field button. In the Rename Field dialog box, type the desired name and click OK:

If you try providing a name for a field that already exists, you would receive an error:

Practical Learning: Using the Table Wizard

1. The Rockville Techno database should still be opened
   In the Database Window, click the Tables button
2. Double-click Create Table By Using Wizard. (If you are using MS Access 97, from the Tables tab, click New and double-click Table Wizard). The Table Wizard starts
3. Click the Business radio button if necessary
   In the Sample Tables list box, scroll down and click Assets
4. In the Sample Fields, click AssetID and click the Select One Field button 
5. From the Sample Fields, double-click Make, Model, ModelNumber, SerialNumber, DateAcquired, and PurchasePrice
6. In the Sample Tables, scroll up and click Mailing List
7. In the Sample Fields, scroll down and double-click Notes
8. In the Fields In My New Table list, click ModelNumber to select it and click the Remove One Field button 
9. Click the Personal radio button
10. In the Sample Tables, click Categories
11. In the Fields In My New Table list, click AssetID to select it
12. From the Sample Fields, double-click CategoryName 
13. In the Fields In My New Table list, click CategoryName to select it and click the Rename Field… button
14. In the Rename Field, change the Field Name to AssetType
     
    
15. Click OK
16. Click Next
17. Change the name of the table to CompanyAssets
18. Click Next twice and click Finish
19. To close the CompanyAssets table, click its system Close button 

Data Entry on Tables

Data is entered in a table by typing it into cells. Some fields can be configured to accept some types of data and reject others. Some other fields would allow anything. This depends on how the database fields were setup.

A table's cell holds one particular unit of data. All cells on the same (vertical) column belong to the same category of information. The horizontal range of cells is called a record; and all cells on this range belong to the same record.

After typing data into a cell, you can press Enter or Tab to move the cursor to the next cell. In many circumstances you will also be able to move to the next cell by pressing the right arrow key. Sometimes you can move to the next record even if the current record is not completed. In this case, you could press the down arrow key.

There are three kinds of fields or cells the user will face: a field in which the user can type data, a field that displays a list such as a combo box the user has to select from, and a field that does not receive input from the user. The latter is usually set for an AutoNumber data type: the database itself keeps track of the numbers. If you as the user have to select from a list, click the field that holds the list and select from the combo box. There are two kinds of combo boxes, those that accept new entries from the user and those that allow only a selection from a preset list. The classic field allows the user to just type the appropriate data in a field.

After setting the data in a particular field, you can click another cell and type the desired data. The easiest way is to press the right arrow key to move to the next field or the left arrow to move to the previous field; pressing Enter would work as well.

Data on the same row represents a record. Data on the same column represents a category. After typing data, if you press Enter at the end of a record, the cursor would move to the beginning of the next record. At anytime, you can press the up arrow key to move to the same category field of the previous record. If you press the down arrow key, the same category of the next record would receive focus.

Practical Learning: Table's Data Entry

1. The Rockville Techno database should still be opened
   On the Database window, while in the Tables section, double-click the CompanyAssets table to display it
2. Click the first empty field under AssetType and type Printer 
3. Press Enter and type HP
4. Press Tab and type LaserJet 4200dtn
5. Click the empty box under Date Acquired and type 10/08/2002 or 10/08/02 depending on how your computer is setup (we will discuss this during Database Maintenance but for now, if your computer is configured for a two-digit year, enter only the right two digit of the year)
6. Complete the table as follows:
    

7. Close the CompanyAssets table
8. On the Database window, right-click the Payment Methods table and click Open
9. Complete it as follows:
    

10. Notice that the 0 value changes into No and any other number changes into Yes
11. To close the Payment Methods table, click its system Close button
12. On the Database window, double-click the Employees table to open it
13. Click the first empty field under Last Name, type Phillips and press Enter
14. Perform data entry, skipping other fields
15. Close the table after performing data entry 

Table Printing

The primary function of the table is to provide a means of storing and arranging information of a database. Nevertheless, you can print data of a table, especially if either you do not have appropriate reports available or you are in a hurry. You can print data on a table whether the table is opened or not.

To print a closed table, locate it in the Database Window. Then right-click the desired table and click Print. 

Once a table is opened in Datasheet View, on the Table Datasheet toolbar, you can click the Print button. This will send all the records of the table to the printer for printing.

If you want to control the printing process, instead of using the Print button, on the main menu, you can click File -> Print... To print data of all fields on the table, you can click OK. If you want to print only certain records, unfortunately, Microsoft Access does not allow you to select rows at random: you can only select a range of records. To print a range of records, while a record at one end has focus, you can press and hold Shift. Then click the gray box at the end of the range.

Practical Learning: Printing Tables

1. To reopen the Payment Methods table, on the Database Window, while in the Tables section, double-click Payment Methods
2. To print data from the table, on the Table Datasheet toolbar, click the Print button
3. To close the current table, click its system Close button
4. To print the list of the company’s assets, on the Database Window, click the CompanyAssets table to select it (you don't have to open it)
5. On the main menu, click File -> Print… (if you are using MS Access 2000, you may need to double-click the File menu to display the whole list, which includes Print...)
6. Make sure a printer is selected in the Name combo box
   Click the Properties button
7. Based on your printer, select the Landscape radio button (usually in the Orientation section of a Paper tab) and click OK
8. On the Print dialog box, click OK
   (If you had opened a table, you can close it now)
9. Open the Employees table
10. To print only the names of employees, click David. Press and hold Shift. Then click Network Administrator
11. On the main menu, click File -> Print…
12. On the Print dialog box, click the Selected Record(s) radio button and click OK
13. After using the table, close it

Queries Fundamentals

A query is a request you present to the database, and the database displays its response to you. The whole purpose of creating a query lies on how you formulate that request. There are three main ways you create a query in Microsoft Access:

• The Query Wizard offers the simplest approach where in a step by step process you define the purpose of the query and the database will display its answer
• The best way to build a query is by using the Design View
• Once you get familiar with queries, you will find out that a query is just a group of words called a statement you formulate. This means you can write that statement manually. This approach is the most complex because you need to know the structure of a query statement, but it provides more control than the other two techniques.

The simplest way to create a query is by using the Query Wizard. The wizard presents the tables that are part of the database and you select which fields you need. Such a query is called a Select Query.

Data used on a query can originate from a table, another query, or a combination of tables and/or queries. The first page of the Simple Query Wizard expects you to choose the origin of the query, starting with the table or the query name. For example, you can use a table of employees, then, you choose the necessary data that you want to filter for your query. You can use this query to retrieve the employees' data by their employee number, their last name, the date they were hired, their salary, their marital status, their E-Mail address, or the city or state they live in.

To use the Query Wizard, on the Database Window, you can click the Queries button and then double-click Create Query By Using Wizard. Alternatively, you can first display the New Query dialog from where you would choose Simple Query Wizard. To display the New Query dialog box:

• On the toolbar of the Database window, while in the Queries section, you can click the New button
• On the main menu, you can click Insert -> Query
• On the Database Toolbar, you can click the arrow of the New Object button and click Query

When building a query in the first page of the Simple Query Wizard , you must first specify the object (a table or another query) that will provide data. Then select items using the same types of buttons we reviewed for the Table Wizard.

1. The Rockville Techno database should still be opened
   On the Database Window, click the Queries button
2. To create a query, from the Database window, double-click the Create Query By Using Wizard button
   (If you are using MS Access 97, click New, click Simple Query Wizard, and click OK)
3. From the Tables/Queries combo box, select Table: CompanyAssets
4. From the Available Fields list box, double-click AssetType, Make, Model, DateAcquired, and PurchasePrice
    
   
5. Click Next twice
6. Give the title AssetsInventrory and make sure the Open The Query To View Information radio button is selected
    
   
7. Click Finish

Data Entry on a Query

A query uses the same approach to present its data, like a table: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to prepare data either for analysis or isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.

Data entry on a query is the same as done on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality. Like the table, the query provides navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the last or any record in the range of those available. 

Practical Learning: Performing Data Entry on a Query

1. While the AssetsInventory query is still opened, click the first empty field under the AssetType column
2. Type Computer and press Enter
3. Complete the query as follows:
    

4. After using the query, close it
5. To create a query of selected information of employees, on the main menu, click Insert -> Query
    
   
6. In the New Query dialog box, click Simple Query Wizard and click OK
7. In the first page of the wizard, in the Tables/Queries combo box, select Tables: Employees
8. In the list of Available Fields, double-click FirstName, LastName, Title, and BillingRate
9. Click Next twice
10. Change the Title of the Query to Employees Pay Information and click Finish
11. After viewing the result of the query, close it

Query Printing

Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed but not a professional-oriented product. Data printing on a query is done with the exact same approaches and techniques as for a table.

Practical Learning: Printing a Query

1. On the Database Window, while still in the Queries section, double-click the AssetsInventory query
2. To print the query, on the main menu, click File -> Print…
3. Make sure a printer is selected in the Name combo box and click the Properties button
4. Select the Portrait orientation and click OK
5. Click OK on the Print dialog box
6. To print only a few records, click the gray box on the left side of Laptop
7. Press and hold Shift. Then click the gray box on the left side of Digital Camera and click release Shift
    
   
8. On the main menu, click File -> Print… and, on the Print dialog box, click Selected Record(s)
9. Click OK to print
10. After using the query, close it. 

MOUS Topics

S6 

Print database objects (tables and queries)

S7

Navigate through records in a table, query, ...

S8

Create a database (by using a wizard ...)

S9

Create tables by using the Table Wizard

S23

Enter records using a datasheet

Exercises 

Tenley Associates

1. Start a new database using the Database Wizard and based on the Expenses sample database
2. Name it Tenley Associates
3. For the Information About Employees, add Department Name, Email Name, Home Phone, and Date Hired
4. For the Expense Report Information, include the Business Purpose
5. Set the Screen Style to Stone
6. Set the Printed Reports Style to Corporate
7. Set the Database Title to Tenley Associates
8. Set the Startup form to None
9. From the Tables section of the Database window, open the Expense Categories table and enter the following expenses:
    

10. Print all records on the table and close it
11. Open the Employees table and fill it up with the following values, ignoring those not mentioned:
     

12. Close the table
13. Using the Simple Query Wizard, create a query based on the Employees table and include the following fields: FirstName, LastName, Title, and HomePhone. Save the query as Employees Contact Information
14. Close the query and print all of its records without opening it
15. Using the Employees Contact Information query, print only the second and third records of employees
16. Use the Table Wizard to create a table that includes the following fields: DepartmentID (from the Assets sample table), DepartmentName (from the Employees sample table), and Notes (from the Recipes sample table of the Personal category). Save the table as Departments and fill it up with the following values
     

Introduction to Forms and Reports

The Form Wizard

As far as users are concerned, the form is the central point of a database. It is used to view, enter, manipulate, and search data. Because users spend a great deal of their time looking at forms, you should create and make them as attractive and friendly as possible. Form design can take a long time in database development but Microsoft Access provides quick means to get around.

The Form Wizard provides an easy and fast means of creating a form. Like the other wizards we have used so far, this one also takes you step by step through creating the object. There are two main ways you can launch the Form Wizard: from the Database Window or from the New Form dialog box. On the Database Window, you can click the Forms button. Then double-click Create Form By Using Wizard. On the New Form dialog box, you can click Form Wizard and click OK.

The first page of the Form Wizard allows you to choose the originating table or query that will supply the necessary fields in the form. Once you have selected the object, its corresponding fields display in the Available Fields list box. You can then select all fields or decide which ones you want to include in the form.

The 2nd page of the wizard allows you to select the desired layout of the form. Forms can be designed in various flavors: Columnar, Datasheet, Tabular, Justified: 

• A columnar form is used to display data one record at a time. This is a convenient display for data entry and analysis because the user is able to examine each piece of information for each one record:
   
  
• A tabular form displays its data in a table layout following a continuous series of records. All or almost all records are displaying in a single layout:
  
  
  
  There are three main ways you can create a tabular form: from the New Form dialog box, using the Form Wizard, or designing it
• A datasheet form looks and behaves like a table, displaying all possible records at the same time instead of one record at a time:
  
  
   
  A datasheet form is mainly used in relationships to display another table's related records. It is also suitable for people who prefer to work in a spreadsheet environment. This display allows the database developer to provide a sheet view to the data entry personnel without making the table's design or formats available.
  
  You can make the same form available in Form View and Datasheet View. In fact, most forms are. Users can switch from Form View to Datasheet View by clicking the View menu. Unfortunately, this could also allow the users to get the form in Design View, and then they could modify it. If you do not want the users to have access to Design View but retain Form View and Datasheet View, you may have to create custom menus and toolbars.
  
  There are two main ways you can create a Datasheet form: using the wizard or designing it.
• A Justified form provides a good and consistent look. When the form is created, borders are added to labels:
   
  
  
  The 3rd page of the Form Wizard presents the forms designs you can choose from. These are the designs we saw when creating a database using the Database Wizard.
  
  The 4th page allows you to name the form.

Practical Learning: Form Wizard - Columnar

1. The Rockville Techno database should still be opened
   On the Database Window, click the Forms button
2. On the Database Window’s toolbar, click New
3. On the New Form dialog box, click Form Wizard
4. From the combo box, select the CompanyAssets
    
   
5. Click OK
6. Click the Select All Fields button 
7. Click Next
8. Accept or choose the Columnar layout, then click Next
9. Click the International style and click Next
10. Accept the name of the form as CompanyAssets and click Finish
    An automatic form is created for you
     
    
11. To close the form, click its Close button

Data Entry on a Form

Data entry of a database is mainly performed on forms as they provide the friendliest display of information. Data entry on a form is performed using text boxes, combo boxes, and other controls. On text boxes, the user enters data by typing it. On a combo box, depending on how the object was configured by the database developer, the user may have to only select an item from the list. In some other cases, the user may be allowed to enter new data.

After entering or changing data on a control, to move from one field to the next, the surest way is to press Tab. On most occasions, the user can also press Enter. The Enter key may not move the cursor from a text box that allows multiple lines of text (such as a field created as Memo). If the user is simply reviewing data without performing data entry, the keyboard’s arrow keys can also be used to move among fields.

Practical Learning: Form Data Entry

1. The Rockville Techno database should still be opened and the Forms button should be selected. Otherwise, on the Database Window, click the Forms button
   Double-click the Employees form to open it
2. In the first record, for David Phillips, click Address and type 10048 Venice Drive
3. Press Enter to move to the City field and type Bethesda
4. Complete each record by clicking in the appropriate field and typing the corresponding data (after each record, click the Next Record button)
5. Close the Employees form after performing data entry
6. Double-click Expense Codes
7. In the first Expense Code, type Office Supplies and press Enter
8. Type Software and press Enter 
9. Type Printer and press Enter 
10. Type Computer Supplies and press Tab 
11. Type Car Rental and press Tab 
12. Type Air Fare and press Tab 
13. Type Meal 
14. To close the form, click its system Close button
15. Double-click Work Codes 
16. In the first Work Code, type Regular and press Enter 
17. Type Web Design and press Enter. 
18. Type Data Entry and press Tab 
19. Type Database Design and press Tab 
20. Type Software Coding 
21. To close the form, click its close button 
22. Double-click Clients 
23. In the First Name text box, type Jose and press Enter 
24. In the Last Name text box, type Marillo and press Enter 
25. In the 8012 Herald Ave Suite 402 and press Enter 
26. In the City text box, type Alexandria and press Tab 
27. In the State/Province text box, type VA and press Tab 
28. In the Postal Code text box, type 22031 and press Tab 
29. In the Country text box, type USA 
30. Click in the Company Name text box and type PTN Investors, Inc. 
31. Click in the Contact Title text box and type Director of Investments and press Tab
32. In the Phone Number text box, type 7038384422 and press Tab
33. In the Fax Number, type 7038385035
34. Click in the Notes text box and type PTN is an investment firm with offices in VA, MD, and NY and is interested in network technology for its future activities
     
    
35. Click the Projects button. This opens the form allowing to process projects related to this client
36. In the Project Name text box, type PTNC01
37. Click the arrow of the Project Manager combo box and select Phillips, David
38. Click in the Client PO# text box and type 1
39. In the Project Description text box, type PTN has contracted us, for one year, to provide part-time technical support for its existing computer network. We will be in their office every Wednesday all day long
40. In the Est. Total Billings text box, type 16250 and press Tab 
41. In the Project Begin Date, type 031898 and press Tab 
42. In the Project End Date, type 031899
     
    
43. To close the form, click its Close button 
44. On the Clients form, click the Time Cards button
45. Click the arrow of the Employee combo box and select Phillips, David 
46. Select the date in the Date Entered text box, type 03/18/98 and press Tab. 
47. In the Date Worked text box, type 031898 and press Tab. 
48. Click the arrow of the Project combo box, select PTNC01 and press Tab. 
49. Click the arrow of the Work Code combo box, select Regular and press Tab twice. 
50. In the Billable Hours field, type 8 
51. In the Work Description field, type Besides regular work, we replaced 2 network cards on the receptionist and Ms. Maureen's workstations
52. Press Tab to move to the next record of the subform
53. For the next record, set the Date Worked to 032598 for the PTNC01 project on Regular work billable for 6 hours. For the Work Description, type Spent some time training employee on mild issues of computer networking.
54. In the Expense subform, click the Date field
55. Enter 031898 and press Enter
56. On the Project combo box, select PTNC01 and press Enter
57. Click the Expense Code combo box and select Computer Supplies
58. Click the Amount field and type 386.10 
59. Click the Description field and type I bought 5 network cards. Two were used right away. The spares are kept just in case
60. Click the Next Record button 
61. On the Employee combo box, select Nice, Helene
62. Replace the Date Entered with 032598 
63. In the Time Worked combo box, click the 1st empty field and type 03/25/98 
64. Select the Project Name as PTNC01 
65. Set the Work Code to Regular. Set the Billable Hours to 6 
66. In the Work Description, type We spent a couple of hours on site with Mr. Phillips. 
67. To close the form, click its Close button 
68. On the Clients form, click the Payments button
69. Click the arrow of the Payment Method combo box, select Check and press Tab
70. In the Payment Date, type 03/04/98 and press Tab
71. In the Payment Amount text box, type 8600 and press Tab 
72. To close the Payments form, click its Close button 
73. On the Clients form, click Projects to review its form
     
    
74. Close the Projects form.

Form Printing

Although not part of their primary role, forms provide a quick means of printing data, especially in the absence of desired reports. There are various issues related to printing forms, including printing all records or just some of them.

You can print a form without opening it. Once you know the form you would like to print, locate its name in the Database Window, right-click it and click Print... All records on the form would be printed.

When a form is opened, you have the option of printing all of its records or the selected one. Once in Form View, you can click the Print button on the toolbar. To print just one record, navigate to the record you would like to print, click the Form Selection bar to select the record. On the Form View toolbar, click the Print button.

Practical Learning: Printing Forms

1. The Clients form should still be opened.
   Click the Projects button to open the associated projects. Since we have only one project from this client, we will just print that record
2. On the Form View toolbar, click the Print button . Notice that the job goes straight to the printer and starts printing
3. Close the Projects form and close the Clients form
4. On the Database Window, double-click Employees 
5. To navigate to the 3rd employee, click the Next Record button twice
6. After making sure that the desired record is displaying, in this case, the 3rd record, on the main menu, click File -> Print…
7. On the Print dialog box, click the Selected Record(s) radio button
8. Click OK
9. Verify that only the displaying employee's record was printed
10. Now we will print records from 2 to 4
11. Navigate to record No.2
12. Click the Record Selector bar
     
    
13. Press and hold Shift. Click the Next Record button twice to display the record of Lester Vaughn (even though the record # still displays 2)
14. Release Shift
15. On the main menu, click File -> Print… 
16. On the Print dialog box, click the Selected Record(s) radio button and click OK
17. Verify that records from 2 to 4 have been printed
18. Close the Employees form

Reports Fundamentals

A report provides an object used to print a database records. Although you can print tables, queries, or forms, reports are customized to be printer friendly. They can perform and display calculations. Once again, Microsoft Access provides wizards to quickly create reports.

The simplest way to create a report is by using the AutoReport feature available either from the New Object button on the Database toolbar or the New Report dialog box.

1. The Rockville Techno database should still be opened
   On the Database Window, click the Tables button
2. Click the Payment Methods table to select it
3. On the Database toolbar, click the arrow of the New Object button and select AutoReport
    
   
4. After viewing the newly created report, close it
5. When asked whether you want to save the report, click Yes
6. Accept the suggested name for the report and press Enter
7. On the menu bar, click Insert -> Report
8. On the New Report dialog box, select AutoReport: Columnar
9. In the combo box, select Expense Codes
    
   
10. Click OK
11. After viewing the report, close it
12. When asking to save it, click Yes
13. Accept the suggested name for the report and click OK 

The Report Wizard

Microsoft Access can help you create a more elaborate report where you would use one of the pre-designed layouts. This is done using the Report Wizard. The Report Wizard is available from the New Report dialog box that you can access either from the menu bar or from the New button on the Database Window when in the Reports section. If you are using Microsoft Access 2000 or 2002, you can also double-click Create Report By Using Wizard.

Practical Learning: Using the Report Wizard

1. On the main menu, click Insert -> Report
2. On the New Report dialog box, click Report Wizard and click OK
3. On the first page of the wizard, click the arrow of the Tables/Queries combo box and select Table: Employees
4. Click the Select All Fields button 
5. Click Next
6. Accept anything in the second page and click Next
7. Accept anything in the third page and click Next
8. In the Layout section, click the Columnar radio button:
    
   
9. Click Next
10. Select the Bold Style to preview it. Then click Formal and click Next
11. Accept the suggested Title of the report as Employees and press Enter
12. Scroll down in the report to view the records and close the report

Report Printing

As stated already, the report is the favorite object used to print data. As done with the other objects, you can print a report whether it is opened or not. Before printing a report, you must first select or open it. If you want to print a report without opening it, in the Database window, make sure the Reports section is selected. Then, you can locate and right-click the desired report. If you follow this approach, the report would be sent directly to the printer.

If you want to control or customize the printing of a report, first select it in the Reports section of the Database window or open it. Then, on the main menu, you can click File -> Print… This would display the Print dialog box, allowing you to select a printer if you have more than one. You can also specify such aspects as the color, the number of copies or the range of records to be printed.

Practical Learning: Printing a Report

1. On the Database window, click the Reports button
2. Right-click the Expense Codes reports and click Print…
3. To print a report after displaying it, double-click the Time Sheet report to open it
    
   
4. On the main menu, click File -> Print…
5. Click the Properties button
6. Based on your printer, make it print in black and white or grayscale and click OK
7. Click OK on the Print dialog box
8. After printing the report, close it
9. On the Database window, click the Forms button and double-click Clients
10. From the Clients form, click Time Cards
11. After viewing the Time Cards form, click its Preview Time Card… button
12. To print the report, on the Print Preview toolbar, click the Print button
13. After printing the report, close it

MOUS Topics

S6 

Print database objects (..., forms, reports, ...)

S7

Navigate through records in a form

S16

Create a form with the Form Wizard

S23

Enter records using a form

S35

Create a report with the Report Wizard

S26

Preview and print a report

Exercises 

Tenley Associates Company Expenses

1. Open the Tenley Associates database
2. Use the Form Wizard to create a Columnar form based on the Employees table and including all fields. Save the form as Employees and set its display Style to Stone
3. Use AutoForm: Columnar from the New Form dialog box to create a form based on the Expense Details table and save it as Expense Details
4. Fill the Employees form up with the following values, ignoring those not mentioned:
    

5. Print the Employees form
6. Using the Report Wizard, create a Columnar report based on the Expense Categories table, including all fields and using the Corporate style. Save the report as
   Expense Categories and print all of its records
7. Using the AutoReport from the Database toolbar, create a report based on the Employees Contact Information query. Save the report as
   Employees Contact Information and print all of its records

Database Fields on Sheet

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 on a table; but as we have mentioned in previous sections, tables may not be the friendliest windows to present to a user for data entry. For this reason, fields can and should also be created on other windows.

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 database, on the main menu, click File -> New… (or File -> New Database… for Microsoft Access 97)
    
   
2. In the New dialog box, click General if necessary. Make sure Database or Blank Database is selected and click OK
3. Change the name of the file to Clarksville Ice Cream
4. Locate then display the Exercises folder in the Save In combo box and click Create
5. To create a new table, on the main menu, click Insert -> Table
    
   
6. On the New Table dialog box, make sure Datasheet View is selected and click OK

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 list may become confusing. To arrange the list, you would create categories. Here is an example:

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 database 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 from 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:

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 an empty 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
    
   

The Name of a Table

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

Table Creation in Datasheet View

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 an empty space.

(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.

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
    
   

The Name of a Table

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

Column and Row Maintenance in Datasheet View

Column Selection

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

Column Deletion

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

Column Moving

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 a Column

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

Record Selection

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 Cream 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

Record Deletion

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 short pieces of information, such as one character for middle initial or two characters (US states or Canadian provinces), 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 Rockville Techno database that you started in Lesson 2 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 Address column, position your mouse on the line between Address and City:
    
   
6. Then click and drag in the right direction until the mouse is positioned in the middle of City:
    
   
7. Release the mouse
8. Right-click the Country column header and click Column Width...
9. In the Column Width dialog box, type 12.50 to replace the default value
    
   
10. Click OK
11. Click and cell under the Title column
12. On the main menu, click Format -> Column Width...
13. On the Column Width dialog box, click Best Fit and click OK
14. Save and close the table

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:
    

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 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 living 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 after a neighbor found him on the floor of his dining room. 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 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 spend all that cash picking up men 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 instrument stores, 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

Managing Data Fields 

The Table in Design view

The title bar of a table in Design View presents the table’s icon on the left section. Like most other windows, the table's icon holds the table's system menu. This system icon can also be double-clicked to close the table. The long empty section of the title bar is different from a classic window. Instead of being used to maximize, minimize, move, or close the table, it provides features adapted for the database object it is. For example, at a minimum, it can be used to switch views. To use its function, you can right-click it. This would present a menu you can select from:

The right section of the title bar presents the system buttons of a regular window. They can be used to minimize, maximize, restore, or close the table.

On the left side of the view, the row headers allow you to select a row completely. The most left gray button allows you to select everything on the table while in Design View:

The File Name column is used to type a name for each field. The rules we used when in Datasheet View apply here. A field can have any name but keep a name with one word and no space.

Practical Learning:  Creating Columns in Design New

1. While in Design View, click the first empty box under Field Name, type EmployeeID
2. Press the Tab key three times and type DateHired 
3. Press the down arrow key and type EmployeeNumber 
4. Press the down arrow key and type FirstName 
5. Press the down arrow key and type MiddleInitial 
6. Press the down arrow key and type LastName 
7. Press the down arrow key and type WorkPhone 
8. Press the down arrow key and type Extension 
9. Press the down arrow key and type EmailAddress 
10. Press the down arrow key and type Address 
11. Press the down arrow key and type City 
12. Press the down arrow key and type State 
13. Press the down arrow key and type ZIPCode 
14. Press the down arrow key and type Country 
15. Press the down arrow key and type Salary 
16. Press the down arrow key and type MaritalStatus 
17. Press the down arrow key, type Notes and press Tab
18. To save the table, on the Table Design toolbar, click the Save button
19. Change the name of the table to Employees and click OK
     

20. You will receive a long message box about a lack of a primary key (we are not there yet). Click No
21. Close the table

Field Renaming

One of the jobs involved with database design and maintenance is to review fields and make sure they are explicit enough for the user. There are three issues related to a field's name. Some fields, as you will find out, are involved in relationships, and you will have to be careful when deciding to change their name. The other issues are related to a field's actual name and its caption.

A field's name is mainly made of two parts from the developer's standpoint: the actual name and the caption. A field's name is stored as part of the table and is involved with any business dealing the table has to cope with. The caption simply guides the user in identifying the use of a particular field. These two items are not strictly related. For example, you could have a field whose name is SocSecNbr while its caption displays Nat Number From Gvt and this will not affect the functionality of the table. Overall, as a developer, you should pay attention to your fields names because these are the names you will refer to when performing calculations and other programming issues.

To rename a field in Datasheet View, use any of the techniques we learned to change a field's name. After you have typed the name, the new name will replace the old one. To rename a field in Design View, click it and type the new name.

Practical Learning: Renaming a Field

1. Open the Rockville Techno database. In the Database window, if necessary, click the Tables button
2. Double-click the Employees table to open it
3. Complete the records as follows:
    

4. To switch the table to Design View, right-click its title bar and click Table Design
5. In StateOrProvince, click between the most left e and O. Then press and hold Delete until the Field Name displays State
6. Press the down arrow key to select PostalCode and type ZIPCode
7. Double-click EmailName, type EmailAddress and press Tab
8. Save the table and close it

Field Selection in Table Design View

In the Design View of a table, there are various types of operations you can perform for database maintenance. Operations include naming or renaming fields, moving or deleting fields, and changing various other properties. In order to perform some of the operations on a field or a group of fields, you must first select one.

• To select one field, you can position the mouse on the gray box to its left and click
• To select more than one column, you can click and hold your mouse on one of the left gray boxes, then drag up or down to cover the other desired field names. When all desired field names are highlighted, release the mouse
• To select more than one column, you can also click one column that will be at one end, press and hold Shift, then click the column that will be at the other end
• To select columns at random, click the left gray box of one of them, press and hold Ctrl, then click the gray box of each of the desired boxes

Column Moving in Table Design View

The columns of a table are displayed in the sequence in which they were created. We learned earlier how to change this sequence in Datasheet View. You can also change the sequence of fields in the Design View of a table.

• To move a column or a group of columns, after making the selection, click the gray box of the field name or one of the gray boxes of one of the selected fields and hold the mouse down. Then drag up or down. A horizontal line will guide you. Once it gets to the desired location, you can release the mouse

Practical Learning: Maintaining Fields in Table Design View

1. The Rockville Techno database should still be opened.
   To open the Employees table in Design View, in the Tables section of the Database window, right-click the Employees table and click Design
2. While in the Design View of the table, click the gray box on the left of Extension to select it
    
   
3. Click the same box again and hold the mouse down. Then drag it down until the guiding horizontal line is positioned under WorkPhone
    
   
4. Release the mouse
5. To select various fields, position the mouse pointer to the left gray box of the HomePhone field until the mouse pointer changes into a right pointing arrow:
    
   
6. Click and hold the mouse down. While holding, drag down to Extension to select those 3 fields, and release the mouse
7. To move the selected fields, click and hold the mouse on one of the black selected boxes on the left of the field names
8. Drag up until the guiding horizontal line is positioned on top of Title:
    
   
9. Then release the mouse
10. Save and close the table

Field Insertion

When it comes to tables, the best place to add new fields is in Design View because this view provides more flexibility. In Design View, you can insert a new field at any position. You can also add a new field to the end of the table, a feature that is not available in Datasheet View. You can also use one of the same fields as we saw when using the Table Wizard:

• To insert a new column, you can right-click the field that will succeed it (you can right-click anywhere on the horizontal boxes of the column, that is, on the Field Name, the Data Type, or the Description columns), click Insert Rows, and type a name for the new column
• To insert a new column, you can also click anywhere on the column that will succeed it. Then, on the main menu, click Insert -> Rows. Alternatively, when a column has focus, on the Table Design toolbar, you can also click the Insert Rows button
• To add a new column at the end of the table, click the first empty field under Field Name and type the desired name
• To use one of the fields available from the Table Wizard, right-click either the first empty field under Field Name or right-click the field that will succeed it and click Build… On the Field Builder dialog box, select the category (Business or Personal), select the desired field, and then click OK

Practical Learning: Inserting New Fields in Table Design View

1. The Rockville Techno database should still be opened.
   To open the Employees table in Design View, in the Database window, click it to select it if necessary. Then, on the Database window, click the Design button
2. To add a new field at the end of the table, click the first empty box under BillingRate. Type Observations and press Enter
3. To insert a new field before LastName, right-click LastName and click Insert Rows
4. Type MiddleName and press Tab
5. To insert an already configured field, right-click EmployeeNumber and click Build…
6. In the Field Builder, click the Business radio button if necessary. In the Sample Tables list, click Employees
7. In the Sample fields list, scroll down and click DateHired
    
   
8. Click OK
9. To add another field, click Address
10. On the main menu, click Insert -> Rows
11. Type WebSite and press Enter
12. Save the table

Field Deletion

When in Design View, you can delete a field you don't need anymore or if it was added by mistake. To delete a column, you can right-click anywhere on its line (on the Field Name, the Data Type, or the Description columns) and click Delete Rows. When a column has focus, you can also click the Delete Rows button on the Table Design toolbar to delete it.

Practical Learning: Deleting a Field in Table Design View

1. To get rid of a column, right-click WebSite and click Delete Rows
2. When asked whether you want to permanently delete the field(s), click Yes
3. Save and close the table

Queries and Fields