Introduction to Relational Databases and Access 2003

Relational Databases and Office Access 2003 Introduced

What Is a Relational Database?

The term database means different things to different people. For many years, in the world of xBase (that is, dBASE, FoxPro, CA-Clipper), database was used to describe a collection of fields and records. (Access refers to this type of collection as a table.) In a client/server environment, database refers to all the data, schema, indexes, rules, triggers, and stored procedures associated with a system. In Access terms, a database is a collection of all the tables, queries, forms, data access pages, reports, macros, and modules that compose a complete system. Relational refers to the fact that the tables that comprise the database relate to one another.

What Types of Things Can I Do with Microsoft Access? I often find myself explaining exactly what types of applications you can build with Microsoft Access. Access offers a variety of features for different database needs. You can use it to develop six general types of applications: Personal applications Small-business applications Departmental applications Corporationwide applications Front-end applications for enterprisewide client/server databases Intranet/Internet applications Access as a Development Platform for Personal Applications At a basic level, you can use Access to develop simple personal database-management systems. I know people who automate everything from their wine collections to their home finances. The one thing to be careful of is that Access is deceptively easy to use. Its wonderful built-in wizards make Access look like a product that anyone can use. After answering a series of questions, you have finished application switchboards that allow you to easily navigate around the application, data-entry screens, and reports, as well as the underlying tables that support them. In fact, when Microsoft first released Access, many people asked if I was concerned that my business as a computer programmer and trainer would diminish because Access seemed to let absolutely anyone write a database application. Although it's true that you can produce the simplest of Access applications without any thought for design and without any customization, most applications require at least some design and customization. If you're an end user and don't want to spend too much time learning the intricacies of Access, you'll be satisfied with Access as long as you're happy with a wizard-generated personal application. After reading this text, you can make minor modifications, and no problems should occur. It's when you want to substantially customize a personal application without the proper knowledge base that problems can happen. Access as a Development Platform for Small-Business Applications Access is an excellent platform for developing an application that can run a small business. Its wizards let you quickly and easily build the application's foundation. The ability to build code modules allows power users and developers to create code libraries of reusable functions, and the ability to add code behind forms and reports allows them to create powerful custom forms and reports. The main limitation of using Access for developing a custom small-business application is the time and money involved in the development process. Many people use Access wizards to begin the development process but find they need to customize their applications in ways they can't accomplish on their own. Small-business owners often experience this problem on an even greater scale than personal users. The demands of a small-business application are usually much higher than those of a personal application. Many doctors, attorneys, and other professionals have called me in after they reached a dead end in the development process. They're always dismayed at how much money it will cost to make their application usable. An example is a doctor who built a series of forms and reports to automate her office. All went well until it came time to produce patient billings, enter payments, and product receivable reports. Although at first glance these processes seem simple, upon further examination, the doctor realized that the wizard-produced reports and forms did not provide the sophistication necessary for her billing process. Unfortunately, the doctor did not have the time or programming skills to add the necessary features. So, in using Access as a tool to develop small-business applications, it is important that you be realistic about the time and money involved in developing anything but the simplest of applications. Access as a Development Platform for Departmental Applications Access is perfect for developing applications for departments in large corporations. Most departments in large corporations have the development budgets to produce well-designed applications. Fortunately, most departments also usually have a PC guru who is more than happy to help design forms and reports. This gives the department a sense of ownership because it has contributed to the development of its application. If complex form or report design or coding is necessary, large corporations usually have on-site resources available that can provide the necessary assistance. If the support is not available within the corporation, most corporations are willing to outsource to obtain the necessary expertise. Access as a Development Platform for Corporationwide Applications Although Access might be best suited for departmental applications, you can also use it to produce applications that you distribute throughout an organization. How successful this endeavor is depends on the corporation. There's a limit to the number of users who can concurrently share an Access application while maintaining acceptable performance, and there's also a limit to the number of records that each table can contain without a significant performance drop. These numbers vary depending on factors such as the following: How much traffic already exists on the network. How much RAM and how many processors the server has. How the server is already being used. For example, are applications such as Microsoft Office being loaded from the server or from local workstations? What types of tasks the users of the application are performing. For example, are they querying, entering data, running reports, and so on? Where Access and Access applications are run from (the server or the workstation). What network operating system is in place. My general rule of thumb for an Access application that's not client/server based is that poor performance generally results with more than 10–15 concurrent users and more than 100,000 records. Remember that these numbers vary immensely depending on the factors mentioned, as well as on the what you and the users define as acceptable performance. If you go beyond these limits, you should consider using Access as a front end to a client/server database such as Microsoft SQL Server—that is, you can use Access to create forms and reports while storing tables and possibly queries on the database server. Access as a Front End for Enterprisewide Client/Server Applications A client/server database, such as Microsoft SQL Server or Oracle, processes queries on the server machine and returns results to the workstation. The server software itself can't display data to the user, so this is where Access comes to the rescue. Acting as a front end, Access can display the data retrieved from the database server in reports, datasheets, or forms. If the user updates the data in an Access form, the workstation sends the update to the back-end database. You can accomplish this process either by linking to these external databases so that they appear to both you and the user as Access tables or by using techniques to access client/server data directly. Alison Balter's Mastering Access 2002 Enterprise Development, published by Sams, covers the details of developing client/server applications with Microsoft Access. Access as a Development Platform for Intranet/Internet Applications By using data access pages, intranet and Internet users can update application data from within a browser. Data access pages are Hypertext Markup Language (HTML) documents that are bound directly to data in a database. Although Access stores data access pages outside a database, you use them just as you do standard Access forms except that Microsoft designed them to run in Microsoft Internet Explorer 5.5 or higher, rather than in Microsoft Access. Data access pages use dynamic HTML in order to accomplish their work. Because they are supported only in Internet Explorer 5.5 or higher, data access pages are much more appropriate as an intranet solution than as an Internet solution. In addition to using data access pages, you can also publish database objects as either static or dynamic HTML pages. Static HTML pages are standard HTML pages that you can view in any browser. You can publish database objects either dynamically to the HTX/IDC file format or to the ASP (Active Server Pages) file format. Dynamically means that these pages are published by the Web server each time that they are rendered. The Web server publishes HTX/IDC files dynamically, and these files are therefore browser independent. The Web Server also dynamically publishes ASP files published by Microsoft Access, but the published ASP pages require Internet Explorer 4.0 or higher on the client machine. Access 2002 introduced the ability to create Extensible Markup Language (XML) data and schema documents from Jet or SQL Server structures and data. You can also import data and data structures into Access from XML documents. You can accomplish this either using code or via the user interface.

Starting Microsoft Access

The first step in getting started with using Access is to launch Microsoft Access. You launch Microsoft Access from the Start menu or from a desktop shortcut.

To start Access from the Start menu, you select Start | Programs | Microsoft Office | Microsoft Office Access 2003. The Access Desktop with the Getting Started window appears

Opening an Existing Database After you have started Access, you can create a new database or open an existing database. A database is a single file that contains objects such as tables, queries, forms, and reports. A database is stored as a file on your computer or on a network computer. To work with the objects in a database, you must open the database file. To open an existing database from the Getting Started window, follow these steps: Click More… under the Open options. The Open dialog box appears If necessary, open the Look In drop-down list box to select another drive or directory. Click to select the filename of the database you want to open. Click Open. Access opens the database. To open a recently used database from the Getting Started window, follow these steps: Locate the database in the list of files in the Getting Started window. Click the link to the desired database. Access opens the database. To open an existing database from the File menu, follow these steps: Choose File | Open. The Open dialog box appears If necessary, open the Look In drop-down list box to select another drive or directory. Click to select the filename of the database you want to open. Click Open. Access opens the database. To open a recently used database from the File menu, follow these steps: Open the File menu. Locate the desired database in the list of recently used files at the bottom of the File menu. Click to select the desired file. Access opens the database. The Database Window When you open a database, the Database window appears ,The Database window, which appears within the Access desktop window, allows you to select any of the Objects tabs. for example, the Tables tab is selected. In addition to what its parent window, the Access desktop window, contains, the Database window contains its own buttons and objects. Table 1 describes these buttons and objects and what they do. The objects are discussed in more detail in the sections that follow. Table 1 The Buttons and Objects in the Database Window Button/Object Description Open Opens the object you have selected. Design Allows you to modify the design of the selected object. New Opens a new object based on the type of object selected. Tables Lists the tables in the database. Each table contains data about a particular subject. Queries Lists the queries in the database. Each query is a stored question about data in the database. Forms Lists the forms in the database. Each form allows you to view, add, edit, and delete data. Reports Lists the reports for the database. Each report allows you to send table data to the printer or screen in a format that you define. Pages Lists the data access pages. This allows viewing of and working with data from the Internet or an intranet. Macros Lists the macros created to automate the way you work with data. Modules Lists the modules of programming code created for the database. Groups/Favorites Allows you to create your own groups of favorite objects that you frequently work with (forms, reports, and so on). An example would be sales reports.

Working with Existing Database Table

Tables Introduced

Viewing and Navigating Table Data

Tables are the basis of everything that you do in Access. Most of the data for a database resides in tables, so if you're creating an employee payroll database, the employee data will be stored in a table, your payroll codes might be stored in a table, and your past payroll records could be stored in a table. A table contains data about a specific topic or subject (for example, customers, orders, or employees). Tables are arranged in rows and columns, similarly to a spreadsheet. The columns represent the fields, and the rows represent the records

Editing Table Data

You can change the data in a table any time that you are in Datasheet view of a table, the result of a query, or Form view of a form. Access saves changes you make to a record as soon as you move off the record.

Modifying Table Data

One task you might want to perform is to simply modify table data. Here's the process:

1. Select the record you want to change by using any of the techniques listed in

2. Select the field you want to change by clicking the field or using the arrow keys.

3. Type to make the necessary changes to the data. When you move off the record, Access saves your changes.

Deleting Field Contents

Now that you know how to modify the contents of a field, let's talk about how to delete the contents of a field. The process is simple:

1. Select the field contents you want to delete.

2. Press the Delete key. Access deletes the contents of the field.

It's important to note that Access saves changes to the current record as soon as you move off the current record. If you want to cancel all the changes you made to a record, you simply press the Esc key twice. Access cancels all changes you made to that record.

Undoing Changes

There are different options available when you're undoing changes to a field or to a record. The options differ, depending on whether you are still within a field, have left the field, or have left the record. The sections that follow explore the various options that are available.

Undoing Changes Made to the Current Field

When you are in the process of making changes to a field, you might realize that you really didn't want to make changes to that field or to that record. To undo changes to the current field, you can either click the Undo tool on the toolbar, select Edit | Undo Typing, or press the Esc key once. For example, if you meant to change the contact first name from Alison to Sue but realized that you were accidentally typing Sue in the Customer field, you could press the Esc key once, click the Undo tool on the toolbar, or select Edit | Undo Typing to undo your change.

Undoing Changes After You Move to Another Field

The process of undoing changes after you move to another field is slightly different from the process of undoing changes made to the current field. You can either click the Undo tool on the toolbar, select Edit | Undo Current Field/Record, or press the Esc key once. For example, if you meant to change the contact first name from Alison to Sue but realized that you accidentally typed Sue in the Customer field, and then you moved to another field, you could click the Undo tool on the toolbar, press the Esc key once, or select Edit | Undo Current Field/Record to undo your change.

Undoing Changes After You Save a Record

When you make changes to a field and then move to another record, Access saves all changes to the modified record. As long as you do not begin making changes to another record, you can still undo the changes you made to the most recently modified record. To do this, you can either click the Undo tool on the toolbar, select Edit | Undo Saved Record, or press the Esc key twice. For example, if you meant to change the contact first name from Alison to Sue but realized that you accidentally typed Sue in the Customer field, and you then moved to another record, you could click the Undo tool on the toolbar, select Edit | Undo Saved Record, or press the Esc key twice to undo your change.

Adding Records to a Table

Access adds records to the end of a table, regardless of how you add them to the table.

To add records, follow these steps:

1. Select the table to which you want to add information.

2. Click the New Record Navigation button at the bottom of the Datasheet window.

3. Add the necessary information to the fields within the record. When you move off the record, Access saves the new record.

Deleting Records Before you can delete records, you must select them. The following sections therefore cover the process of selecting records and then the process of deleting records. Selecting One or More Records To select one record, you simply click the gray record selector button to the left of the record within the datasheet. To select multiple records, you click and drag within the record selector area. Access selects the contiguous range of records in the area over which you click and drag. As an alternative, you can click the gray selector button for the first record you want to select, hold down the Shift key, and then click the gray selector button of the last record that you want to select. When you do this, Access selects the entire range of records between them.

Finding and Replacing Records

When you are working with records in a large data table, you often need a way to locate specific records quickly. By using the Find feature, you can easily move to specific records within a table. After you have found records, you can also replace the text within them.

Finding a Record That Meets Specific Criteria

The Find feature allows you to search in a datasheet for records that meet specific criteria. Here's how it works:

1. Select the field containing the criteria for which you are searching.

2. Click the Find button on the toolbar. The Find and Replace dialog box appears
   

3. Type the criteria in the Find What text box.

4. Use the Look In drop-down list box to designate whether you want to search only the current field or all fields in the table.

5. Use the Match drop-down list box to designate whether you want to match any part of the field you are searching, the whole field you are searching, or the start of the field you are searching. For example, if you type Federal in the Find What text box and you select Whole Field in the Match drop-down list box, you find only entries where Ship Via is set to Federal. If you select Any Part of Field, you find Federal Shipping, Federal Express, United Federal Shipping, and so on. If you select Start of Field, you find Federal Shipping and Federal Express, but you do not find United Federal Shipping.

6. Use the Search drop-down list box to designate whether you want to search only up from the current cursor position, only down, or in all directions.

7. Use the Match Case check box to indicate whether you want the search to be case-sensitive.

8. Use the Search Fields as Formatted check box to indicate whether you want to find data only based on the display format (for example, 17-Jul-96 for a date).

9. Click the Find Next button to find the next record that meets the designated criteria.

10. To continue searching after you close the dialog box, use the Shift+F4 keystroke combination.

Replacing Data in a Table

There may be times when you want to update records that meet specific criteria. You can use the Replace feature to automatically insert new information into the specified fields. Here's the process:

1. Click within the field that contains the criteria you are searching for.

2. Click the Find button on the toolbar. The Find and Replace dialog box appears.

3. Select the Replace tab
   

4. Type the criteria in the Find What text box.

5. Type the new information (the replacement value) in the Replace With text box.

6. Choose values for the Look In drop-down list box, Match drop-down list box, Search drop-down list box, Match Case check box, and Search Fields as Formatted check box, as described in the "Finding a Record That Meets Specific Criteria" section of this hour.

7. Click the Find Next button. Access locates the first record that meets the criteria designated in the Find What text box.

8. Click the Replace button. Access replaces the text for the record and finds the next occurrence of the text in the Find What text box.

9. Repeat step 8 to find all occurrences of the value in the Find What text box and replace them. As an alternative, you can click the Replace All button to replace all occurrences at once.

   You should use Replace All with quite a bit of caution. Remember that the changes you make are permanent. Although Replace All is a viable option, when you use it you need to make sure you have a recent backup and that you are quite certain of what you are doing. In fact, I usually do a few replaces to make sure that I see what Access is doing before I click Replace All.

   
   

10. Click Cancel when you're done.

If you are searching a very large table, Access can find a specific value in a field fastest if the field you are searching on is the primary key or an indexed field.

With either Find or Replace, you can use several wildcard characters. A wildcard character is a character you use in place of an unknown character. Table 2 describes the wildcard characters.

Table 2 Wildcard Characters You Can Use When Searching

Wildcard Character	Description
*	Acts as a placeholder for multiple characters.
?	Acts as a placeholder for a single character.
#	Acts as a placeholder for a single number.

Filtering Table Data

In a table you can apply filters to fields to limit what records you view. This is very helpful if you want to view just the data associated with a subset of the records. For example, you might want to view just the data associated with sales managers.

Filtering by Selection

The Filter by Selection feature allows you to select text and then filter the data in the table to that selected text. To use the Filter by Selection feature, follow these steps:

1. Open a table in Datasheet view.

2. Select the record and field in the table that contain the value on which you want to filter.

3. Click the Filter by Selection button. The data is filtered to only the specified rows.

Creating Your Own Database and Objects

Designing Databases

Data Analysis and Design

After you have analyzed and documented all the tasks involved in a system, you're ready to work on the data analysis and design phase of an application. In this phase, you must identify each piece of information needed to complete each task. You must assign these data elements to subjects, and each subject will become a separate table in the database. For example, a subject might be a client; every data element relating to that client—the name, address, phone number, credit limit, and any other pertinent information—would become fields within the client table.

You should determine the following for each data element:

• The appropriate data type

• The required size

• Validation rules

You should also determine whether you will allow the user to update each data element and whether the user will enter each update or the system will calculate it. Then you can figure out whether you have properly normalized the table structures.

Database Terms Introduced

In the preceding hours we have briefly mentioned some terms that are integrally important now that you will be designing your own objects. The following are the most important of these terms and what they mean:

• Column or field— A single piece of information about an object (for example, a company name).

• Row or record— A collection of information about a single entity (for example, all the information about a customer).

• Table— A collection of all the data for a specific type of entity (for example, all the information for all the customers stored in a database). It is important that each table contain information about only a single entity. In other words, you would not store information about an order in the Customers table.

• Primary key field— A field or a combination of fields in a table that uniquely identifies each row in the table (for example, the CustomerID).

• Natural key field— A primary key field that is naturally part of the data contained within the table (for example, a Social Security number). Generally it is better to use a contrived key field, such as an AutoNumber field, than a natural key field as the primary key field.

• Composite key field— A primary key field comprising more than one field in a table (for example, LastName and FirstName fields). It is preferable to create a primary key based on an AutoNumber field than to use a composite key field.

• Relationship— Two tables in a database sharing a common key value. An example is a relationship between the Customers table and the Orders table: The CustomerID field in the Customers table is related to the CustomerID field in the Orders table.

• Foreign key field— A field on the many side of the relationship in a one-to-many relationship. Whereas the table on the one side of the relationship is related by the primary key field, the table on the many side of the relationship is related by the foreign key field. For example, one customer has multiple orders, so whereas the CustomerID field is the primary key field in the Customers table, it is the foreign key field in the Orders table.

Testing As far as testing goes, you just can't do enough. I recommend that if an application is going to be run in Windows 98, Windows NT, Windows 2000, and Windows XP, you test in all these environments. I also suggest that you test an application extensively on the lowest-common-denominator piece of hardware so you can ensure that it will work on all the machines in the environment; an application might run great on your machine but show unacceptable performance on a user's slower machine. It usually helps to test your application both in pieces and as an integrated application. Recruit several people to test your application and make sure they range from the most savvy of users to the least computer-adept person you can find. These different types of users will probably find completely different sets of problems. Most importantly, make sure you're not the only tester of your application because you're the least likely person to find errors in your own programs.

Creating Tables

Building a New Table

There are several ways to add a new table to an Access 2003 database: You can use a wizard to help you with the design process, design the table from scratch, build the table from a datasheet (a spreadsheet-like format), import the table from another source, or link to an external table. This hour discusses the processes of building a table from a datasheet and designing a table

Building a Table from a Datasheet

Building a table from a datasheet might seem simple, but it isn't always the best way to build a table because it's easy to introduce severe design flaws into a table. Microsoft added this method of building tables as an "enhancement" to Access—and it is primarily for spreadsheet users getting their feet wet in the database world. I suggest you use one of the other methods to design tables. If you decide to use the datasheet method, though, follow these steps:

1. Select the Tables icon from the list of objects in the Database window.

2. Click the New button. The New Table dialog box appears.

3. Select Datasheet View from the New Table dialog box.
   

4. Rename each column by double-clicking the column heading (for example, Field1) you want to change or by right-clicking the column and selecting Rename Column from the context menu. Type a name for the field and then press Enter.

5. Enter data into the datasheet. Be sure to enter the data in a consistent format. For example, if a table includes a column for employee hire dates, make sure all entries in that column are valid dates and that you enter all dates in the same format. Access uses the contents of each column to determine the data type for each field, so inconsistent data entry confuses Access and causes unpredictable results.

6. After you have added all the columns and data you want, click the Save button on the toolbar. Access prompts you for a table name and asks whether you want to add a primary key.

7. Access assigns data types to each field based on the data you have entered. When Access is done with this, click the View button on the toolbar to look at the design of the resulting table.

8. Add a description to each field to help make the table self-documenting.

Selecting the Appropriate Field Type for Data The data type you select for each field can greatly affect the performance and functionality of an application. Several factors can influence your choice of data type for each field in a table: The type of data that's stored in the field Whether the field's contents need to be included in calculations Whether you need to sort the data in the field The way you want to sort the data in the field How important storage space is to you The type of data you need to store in a field has the biggest influence on which data type you select. For example, if you need to store numbers that begin with leading zeros, you can't select a Number field because leading zeros entered into a Number field are ignored. This rule affects data such as zip codes (some of which begin with leading zeros) and department codes.

Appropriate Uses and Storage Space for Access Field Types

Field Type	Appropriate Uses	Storage Space
Text	Data containing text, a combination of text and numbers, or numbers that don't need to be included in calculations. Examples are names, addresses, department codes, and phone numbers.	Based on what's actually stored in the field; ranges from 0 to 255 bytes
Memo	Long text and numeric strings. Examples are notes and descriptions.	Ranges from 0 to 65,536 bytes.

Number	Data that's included in calculations (excluding money). Examples are ages, codes (such as employee IDs), and payment methods.	1, 2, 4, or 8 bytes, depending on the field size selected (or 16 bytes for Replication ID).
Date/Time	Dates and times. Examples are date ordered and birth date.	8 bytes.
Currency	Currency values. Examples are amount due and price.	8 bytes.
AutoNumber	Unique sequential or random numbers. Examples are invoice numbers and project numbers.	4 bytes (16 bytes for replication ID).
Yes/No	Fields that contain one of two values (for example, yes/no, true/false). Sample uses are indicating bills paid and tenure status.	1 bit.
OLE Object	Objects such as Word documents or Excel spreadsheets. Examples are employee reviews and budgets.	0 bytes to 1GB, depending on what's stored within the field.
Hyperlink	Text or a combination of text and numbers, stored as text and used as a hyperlink for a Web address (uniform resource locator [URL]) or a universal naming convention (UNC) path. Examples are Webpages and network files.	0 to 2,048 bytes for each of the three parts that compose the address (up to 64,000 characters total).

The All-Important Primary Key

A primary key is a field or a combination of fields in a table that uniquely identifies each row in the table (for example, CustomerID). The most important index in a table is called the primary key index; it ensures uniqueness of the fields that make up the index and also gives the table a default order. You must set a primary key for the fields on the one side of a one-to-many relationship. To create a primary key index, you select the field(s) you want to establish as the primary key and then click the Primary Key button on the toolbar.

Creating Queries

Ordering Query Results When you run a new query, the query output appears in no particular order. Generally, however, you want to order query output. You can do this by using the Sort row of the query design grid. To order the results of a query, follow these steps: In Design view, click within the query design grid in the Sort cell of the column you want to sort. Use the drop-down combo box to select an ascending or descending sort. Ascending or Descending appears in the sort cell for the field, as appropriate. Sorting by More Than One Field You might often want to sort query output by more than one field. The columns you want to sort must be placed in order, from left to right, on the query design grid, with the column you want to act as the primary sort on the far left and the secondary, tertiary, and any additional sorts following to the right. If you want the columns to appear in a different order in the query output, you must move them manually in Datasheet view after you run the query. Task: Ordering Query Results To sort query results in ascending order by the ContactTitle field, follow these steps: In Design view, click the Sort row of the query design grid for the ContactTitle field. From the Sort drop-down combo box, select Ascending. Run the query and view the results. The records should now be ordered based on the ContactTitle field. If you want to return to Design view of the query, click View on the toolbar. Sort the query output by the Country field and, within individual country groupings, by the ContactTitle field. Because sorting always occurs from left to right, you must place the Country field before the ContactTitle field. Select the Country field from the query design grid by clicking the thin gray button above the Country column. After you have selected the Country field, move the mouse back to the thin gray button and click and drag to the left of the ContactTitle field. A thick gray line should appear to the left of the ContactTitle field. Release the mouse button. Change the sort order of the Country field to Ascending. Run the query. The records should be in order by country and, within the country grouping, by contact title.

Refining a Query by Using Criteria

So far in this hour, you have learned how to select the fields you want and how to indicate the sort order for query output. One of the important features of queries is the ability to limit output by using selection criteria. Access allows you to combine criteria by using several operators to limit the criteria for multiple fields. Table covers the operators and their meanings.

Table Access Operators

Operator	Meaning	Example	Result of Example
=	Equal to	="Sales"	Finds only records with "Sales" as the field value.
<	Less than	<100	Finds all records with values less than 100 in that field.
<=	Less than or equal to	<=100	Finds all records with or equal tovalues less than or equal to 100 in that field.
>	Greater than or equal to	>100	Finds all records with values greater than 100 in that field.
>=	Greater than	>=100	Finds all records with or equal to values greater than or equal to 100 in that field.
<>	Not equal to	<>"Sales"	Finds all records with values other than Sales in the field.
And	Both conditions must be true	Created by adding criteria on the same line of the query design grid to more thanone field	Finds all records where the conditions in both fields are true.
Or	Either condition can be true	"CA" or "NY" or "UT"	Finds all records with the value "CA", "NY", or "UT" in the field.
Like	Compares a string expression to a pattern	Like "Sales*"	Finds all records with the value "Sales" at the beginning of the field (the asterisk is a wildcard character).
Between	Finds a range of values	Between 5 and 10	Finds all records with the values 5–10 (inclusive) in the field.
In	Same as Or	In("CA","NY","UT")	Finds all records with the value "CA", "NY", or "UT" in the field.
Not	Same as <>	Not "Sales"	Finds all records with values other than Sales in the field.
Is Null	Finds nulls	Is Null	Finds all records where no data has been entered in the field.
Is Not Null	Finds all records that are not null	Is Not Null	Finds all records where data has been entered into thefield.

Updating Query Results If you haven't realized it yet, you can usually update the results of a query. This means that if you modify the data in the query output, Access permanently modifies the data in the tables underlying the query. To see how this works, follow these steps: Build a query based on the Customers table. Add the CustomerID, CompanyName, Address, City, and Region fields to the query design grid and then run the query. Change the address of a particular customer and make a note of the customer ID of the customer whose address you changed. Make sure you move off the record so that Access writes the change to disk. Close the query, open the actual table in Datasheet view, and find the record whose address you modified. The change you made was written to the original table; this is because a query result is a dynamic set of records that maintains a link to the original data. This happens whether you're on a standalone machine or on a network. Building Queries Based on Multiple Tables If you have properly normalized your table data, you probably want to bring the data from your tables back together by using queries. Fortunately, you can do this quite easily by using Access queries.

Power Access Techniques

Power Table Techniques

Working with Field Properties

After you have added fields to a table, you need to customize their properties. Field properties let you control how Access stores data as well as what data the user can enter into a field. The available properties differ depending on which field type you select. You can find a comprehensive list of properties under the Text data type. The following sections describe the various field properties. Notice that the lower portion of the Design view window is the Field Properties pane. This is where you can set properties for the fields in a table.

The Field Size Property: Limiting What the User Enters into a Field

The Field Size property is available for Text and Number fields only. It's best to set the Field Size property to the smallest value possible. For Number fields, a small size means lower storage requirements and faster performance. The same is true for Text fields. To modify the Field Size property, follow these steps:

1. Select the desired field name from the top pane of the Design view window.

2. Click the Field Size property text box in the Field Properties pane.

3. Type the desired field size.
   

It's important to note that for Number fields, you should select the smallest Field Size property value that can store the values you will be entering. Limiting the Field Size property of Number fields saves disk space.

Placeholders That Allow You to Build a Custom Format

Placeholder	Function
0	Displays a digit if one exists in the position; otherwise, displays a zero. You can use the 0 placeholder to display leading zeros for whole numbers and trailing zeros for decimals.
#	Displays a digit if one exists in the position; otherwise, displays a blank space.
$	Displays a dollar sign in the position.
. %,	Displays a decimal point, percent sign, or comma at the indicated position.
/	Separates the day, month, and year to format date values.
M	Used as a month placeholder: m displays 1, mm displays 01, mmm displays Jan, mmmm displays January.
D	Used as a day placeholder: d displays 1, dd displays 01, ddd displays Mon, dddd displays Monday.
Y	Used as a year placeholder: yy displays 95, yyyy displays 1995.
:	Separates hours and minutes.
h, n, s	Used as time placeholders for h hours, n minutes, and s seconds.
AM/PM	Displays time in 12-hour format, with AM or PM appended.
@	Indicates that a character is required in the position in a text or memo field.
&	Indicates that a character is optional.
>	Changes all the text characters to uppercase.
<	Changes all the text characters to lowercase.

Working with Table Properties

In addition to field properties, you can specify properties that apply to a table as a whole. To access the table properties, you click the Properties button on the toolbar while in a table's Design view.

The Description property is used mainly for documentation purposes. The Default View property designates the view in which the table appears when the user first opens it. The Validation Rule property specifies validations that must occur at a record level instead of a field level. For example, credit limits might differ depending on what state a customer is in. In that case, what's entered in one field depends on the value in another field. If you enter a table-level validation rule, it doesn't matter in what order the user enters the data. A table-level validation rule ensures that Access enforces the proper dependency between fields. The validation rule might look something like this:

[State] In ("CA","NY") And [CreditLimit]<=2500 Or _
   [State] In ("MA","AZ") And [CreditLimit]<=3500 Or _
   [State] Not In ("CA", "NY", "MA", "AZ")

This validation rule requires a credit limit of $2,500 or less for applicants in California and New York and a limit of $3,500 or less for applicants in Massachusetts and Arizona, but it doesn't specify a credit limit for residents of any other states. Table-level validation rules can't be in conflict with field-level validation rules. If they are in conflict, you will not be able to enter data into the table.

The Validation Text property determines what message appears when a user violates the validation rule. If this property is left blank, a default message appears.

The Filter property is used to indicate a subset of records that appears in a datasheet, form, or query. The Order By property is used to specify a default order for the records. The Filter and Order By properties aren't generally applied as properties of a table.

The Subdatasheet Name property identifies the name of a table that is used as a drill-down. If this property is set to [Auto], Access automatically detects the drill-down table, based on relationships established in the database. The Link Child Fields and Link Master Fields properties are implemented to designate the fields that are used to link the current table with the table specified in the Subdatasheet Name property. These properties should be left blank when you select [Auto] for the Subdatasheet Name. You use the Subdatasheet Height property to specify the maximum height of the subdatasheet and the Subdatasheet Expanded property to designate whether Access automatically displays the subdatasheet in an expanded state.

The Orientation property determines the layout direction for a table when it is displayed. The default setting for USA English is Left-to-Right. The Orientation property is language specific, and the Right-to-Left setting is available only if you are using a language version of Microsoft Access that supports right-to-left language displays. Arabic and Hebrew are examples of right-to-left languages. You must run a 32-bit Microsoft operating system that offers right-to-left support, such as the Arabic version of Windows 2000, to take advantage of this feature in Access. By installing the Microsoft Office Multilanguage Pack and the Microsoft Office Proofing Tools for a specific language, and by enabling the specific right-to-left language under the Microsoft Office language settings, you can also turn on right-to-left support.

Using Indexes to Improve Performance

Indexes improve performance when you're searching, sorting, or grouping on a field or fields. Primary key indexes are used to maintain unique values for records. For example, you can create a single-field index that does not allow a duplicate order number or a multiple-field index that does not allow records with the same first and last names.

To create an index based on a single field (from Design view), follow these steps:

1. Select the field to be indexed.

2. Click the Indexed row of the Field Properties pane.

3. Select the desired index type—No, Yes (Duplicates OK), or Yes (No Duplicates). The Yes (Duplicates OK) option means that you are creating an index and that you will allow duplicates within that field. The Yes (No Duplicates) option means that you are creating an index and you will not allow duplicate values within the index. If the index is based on company name and you select Yes (Duplicates OK), you can enter two companies with the same name. If you select Yes (No Duplicates), you cannot enter two companies with the same name.

To create an index based on multiple fields (from Design view), follow these steps:

1. Choose View | Indexes. The Indexes window appears.

2. Type the index name in the Index Name column.

3. From the Field Name column, select the desired fields to include in the index.

4. Select the desired index properties

5. Click OK to close the Indexes dialog box.

Combining Your Skills

It's time to combine your skills. This task takes you through the process of creating a new database and then adding a table to it. It walks you through setting several properties of the table and even adding data to it:

1. Not using a wizard, create a new database called MYDB.

2. Create a new table, called tblEmployees, in Design view. The tblEmployees table should have the structure shown in Table

   Field Name	Data Type	Size	Description
   EmpID	Autonumber	Long Integer	Unique number given each record
   LastName	Text	25	Last name of employee
   FirstName	Text	15	First name of employee
   Address	Text	20	Address of employee
   City	Text	15	City of employee
   State	Text	2	State of employee
   Zip	Text	5	Zip code of employee
   Phone	Text	12	Employee phone
   DateofHire	Date	N/A	Date of hire
   HourlyRate	Currency	N/A	Hourly rate
   Pension	Yes/No	N/A	Does employee have a company pension?

3. Set a primary key on the EmpID field.

4. Save the table as tblEmployees.

5. Add to the new table the records shown in Table

   Last Name	First Name	Address	City	State	Zip	Phone	Date of Hire	Hourly Rate	Pension
   Forman	Shell	123 Main St.	York	PA	17401	717-755-8976	1/1/1995	$125.00	Yes
   Terry	Sue	478 Creek View Dr.	Camp Hill	PA	17011	717-737-9087	6/15/2000	$180.00	No

6. Add appropriate captions for the EmpID, LastName, FirstName, DateofHire, and HourlyRate fields.

7. Format the HourlyRate field as Currency. Provide a default value of $125.00. Add a validation rule to ensure that HourlyRate is between 0 and $250.00. Add appropriate validation text.

8. Format the DateofHire field as Medium Date. Provide a default value of today's date. Add a validation rule to ensure that DateofHire is less than or equal to today's date. Add appropriate validation text.

9. Use the Input Mask Wizard to place a phone number input mask on the Phone field. Depending on the input mask that you select, you can provide a different look and feel for the phone number.

10. Create separate indexes for the FirstName, City, State, HourlyRate, and DateofHire fields.

11. Create a compound index that includes the LastName and FirstName fields. Call the index FullName.