| Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers. Modifying Your Table Design
Applies to: Microsoft Office Access 2003
Sections in this articleBefore you get startedDeleting tables Renaming tables Changing field names Moving fields Inserting fields Copying fields Deleting fields Changing data attributes Reversing changes Using the Table Analyzer Wizard Taking a look at Lookup properties Changing the primary key Compacting your database No matter how carefully you design your database, you can be sure that you’ll need to change it at some later date. Here are some of the reasons you might need to change your database:
The articles based on this book refer to three sample databases — the Wedding List database, the Housing Reservations database, and the LawTrack Contacts database. Of the three, the LawTrack Contacts sample database is available for download on Microsoft Office Online. If you are interested in exploring the other two databases, you can buy the Microsoft Office Access 2003 Inside Out book. Download and open the sample application to follow along with the examples mentioned in this article. Note All the screen images in this chapter were taken on a computer running Microsoft Windows XP, with the display theme set to Windows XP. In addition, the Use Windows Themed Controls on Forms setting has been enabled in the sample databases. This article takes a look at how you can make these changes easily and relatively painlessly with Microsoft Office Access 2003. If you want to follow along with the examples in this chapter, you should first create the Contact Tracking database described in the article Creating your database and tables. Note You might have noticed that the Contacts table you defined for the Contact Tracking database in the article "Creating your database and tables" article is different from the tblContacts table in the Contacts database. In this article, you’ll modify the Contacts table you built in the previously mentioned article. You’ll also learn how to use the Table Analyzer Wizard to help you normalize (normalize: To minimize the duplication of information in a relational database through effective table design. You can use the Table Analyzer Wizard to normalize your database.) an existing table that contains data from several subjects. Before you get startedAccess makes it easy for you to change the design of your database, even when you already have data in your tables. You should, however, understand the potential impact of any changes you plan and take steps to ensure that you can recover your previous design if you make a mistake. Here are some things to consider before you make changes:
Tip Before saving any changes that permanently alter or delete data in your database, Access always prompts you for confirmation and gives you a chance to cancel the operation. Making a backup copyThe safest way to make changes to the design of your database is to make a backup copy of the database before you begin. If you expect to make extensive changes to several tables in your database, you should also make a copy of the .mdb file that contains your database. You could use a utility, such as Windows Explorer, but Access 2003 includes a handy new feature for making backups easily. When you have the database open that you want to back up, click Back Up Database on the File menu. Access offers to create a copy of your database with the current date appended in the file name. If you want to change a single table, you can easily make a backup copy of that table right in your database. Use the following procedure to copy any table — the structure and the data together.
Checking object dependenciesIf you're just starting out and learning Access by reading the articles through from the beginning, you probably haven't built anything but tables yet. The new Object Dependency tool in Access won't provide very interesting results in a database with nothing but tables, but you'll find this tool invaluable later, after you have built dozens of objects and then need to make some changes to your tables. As you learned in the Creating your database and tables article, you can turn on features to track and perform name AutoCorrect for objects by clicking Options on the Tools menu and then selecting these features on the General tab. Access 2003 uses this AutoCorrect information not only to automatically correct names but also to provide you with detailed information about which objects depend on one another. If you're about to make a change to a field in a table, wouldn't it be good to know which queries, forms, and reports use that table before you make the change? The Perform Name AutoCorrect feature will help you out if you have enabled it, but it can't always detect and fix field names when you have used them in an expression. If you would like to see the Object Dependencies feature in action on your machine, open one of your own databases that contains tables, queries, forms, and reports, or open the LawTrack Contacts sample database (Contacts.mdb) that you can download from this link: Download the sample databases. You can find out which other objects depend on a particular object (such as a table) by selecting the object that you're planning to change in the Database window and then clicking Object Dependencies on the View menu. You can also select an object, right-click, and click Object Dependencies on the shortcut menu. If you haven't turned on Track Name AutoCorrect Info (or have turned it off), Access shows you the dialog box in the following illustration. Click OK to turn on Track Name AutoCorrect Info — the Object Dependencies feature will take a few seconds to examine all your objects. Access shows you the result in the Object Dependencies task pane, as shown in the following illustration. Notice that in many cases you will have to follow a chain of dependencies to find all the objects that might be affected by the change of a field name in a table. For example, in the LawTrack Contacts sample database, a query (qryContacts) is used rather than the table (tblContacts) to provide records to the form that edits contact information (frmContacts). If you were to scroll further down the Object Dependencies task pane looking for forms dependent on tblContacts, you would not find the form frmContacts listed. You can click the plus sign next to any object name to open an expanded list of dependent objects, as shown in the qryContacts query in the previous illustration. Notice that you find frmContacts listed there as a dependent object of qryContacts, which is ultimately dependent on the table you're thinking about changing. When you find an object that you want to investigate further, you can click the object name to open it in Design view. As you can imagine, this new tool can make maintaining your application much easier. Even if you have the Perform Name AutoCorrect feature enabled, you can use this tool after you have modified your table to verify that the changes made were what you expected. Deleting tablesYou probably won't need to delete an entire table very often. However, if you set up your application to collect historical information — for example, total product sales by year — you'll eventually want to delete information that you no longer need. You also might want to delete a table if you've made extensive changes that are incorrect and it would be easier to delete your work and restore the table from a backup. To delete a table, select it in the Database window and press the DELETE key (or click Delete on the Edit menu). Access opens the dialog box shown in the following illustration, which asks you to confirm or cancel the delete operation. Tip Even if you mistakenly confirm the table deletion, you can click Undo on the Edit menu to get your table back. In fact, you can undo up to the last 20 changes that you made in the Database window. You can also undo up to 20 changes in table Design view; however, once you save changes to a table design, you will not be able to undo those changes. Tip You can use the Cut command on the Edit menu or the Cut button on the toolbar to delete a table. Both of these methods place a copy of the table on the Clipboard. After you close the database in which you've been working, you can open another database and paste the table that's on the Clipboard into the database. If the warning dialog box doesn't look exactly like the previous illustration, it might be because you turned on the Microsoft Office Assistant. If you chose the option to have the Office Assistant display alerts, the Office Assistant will respond with any Access warning message, as shown in the following illustration. In this message, the Links character stuck its nose forward and yowled at me as part of its animation. Tip Although the Office Assistant can be fun at first blush, it can get a bit annoying if you're working hard on a development project and getting several error messages. If you want to turn on the Assistant, click Show the Office Assistant on the Help menu. Once the character appears, you can right-click it and click Options on the shortcut menu to customize how it works. When the Assistant is visible, you can click Hide the Office Assistant on the Help menu to make it go away. If you have defined relationships between the table you want to delete and other tables, Access displays another dialog box that alerts you and asks whether you also want to delete the relationships. If you click Yes, Access deletes all relationships between any other table and the table you want to delete and then deletes the table. (You can't have a relationship defined to a nonexistent table.) Even at this point, if you find that you made a mistake, you can click Undo on the Edit menu or click the Undo button on the toolbar to restore both the table and all simple relationships. Caution When you undo a table deletion, Access may not restore all the previously defined relationships between the table and other tables. You should verify the table relationships in the Relationships window. Renaming tablesIf you keep transaction data (such as receipts, deposits, or checks written), you might want to save that data at the end of each month in a table with a unique name. One way to save your data is to rename the existing table (perhaps by adding a date to the name). You can then create a new table (perhaps by making a copy of the backup table's structure) to start collecting information for the next month. To rename a table, select it in the Database window and click Rename on the Edit menu. Access places the name in an edit mode in the Database window so that you can type a new name, as shown in the following illustration. Type the new name, and press ENTER to save it. If you enter the name of a table that already exists, Access displays a dialog box that asks whether you want to replace the existing table, as shown in the following illustration. If you click Yes, Access deletes the old table before performing the renaming operation. Even if you replace an existing table, you can undo the renaming operation by clicking Undo on the Edit menu. Changing field namesPerhaps you misspelled a field name when you first created one of your tables. Or perhaps you've decided that one of the field names isn't descriptive enough. As you learned in the Creating your database and tables article, you can change the displayed name for a field by setting its Caption property. But you won't necessarily want the hassle of giving the field a caption every time it appears in a query, a form, or a report. Fortunately, Access makes it easy to change a field name in a table — even if you already have data in the table. Note The next several examples in this article show you how to change the Contacts table in the Contact Tracking database to more closely match tblContacts in the LawTrack Contacts sample database. In the "Creating your database and tables" article, you created the first draft of the Contacts table by using a wizard. Now you need to make a few changes so that the table will hold all the data fields that you need for your application. You bypassed your chance in the wizard to rename all sample fields when you originally selected them, but now you will rename one of the fields before beginning work on the rest of your application. Renaming a field is easy. For example, you chose the field MiddleName in the wizard, but you've decided that you need only the contact's middle initial in this database. It makes sense to change the field name to reflect the actual data you intend to store in the field. (Later in this article, you'll see how to shorten the length of the field.) Open the Contacts table in the Contact Tracking database in Design view, and then move the cursor to the MiddleName field. Highlight the characters Name at the end of the field name, and then type Init. You can also click in the field name, use the arrow keys to position the cursor just before the letter N, press the DELETE key to remove the characters you don't want, and type the new ones. While you're at it, press F6 to jump down to the Field Properties area of the window, press TAB to move to the Caption property, and change the field caption. Your result should look something like the following illustration.
As you follow along with the examples in this article, it might be useful to compare the structure of the Contacts table you built in the Creating your database and tables article and the actual tblContacts table in the LawTrack Contacts sample database. If you exactly followed the instructions in the "Creating your database and tables" article, your Contacts table in the Contact Tracking database should look like the following table. Contacts
You can see the actual design of tblContacts in the following table. tblContacts
As you can see, you have a lot of work to do — renaming fields, moving fields, inserting fields, and changing data types and lengths — to make the two tables identical. Moving fieldsYou might want to move a field in a table definition for a number of reasons. Perhaps you made an error as you entered or changed the information in a table. Or perhaps you've discovered that you're using some fields you defined at the end of a table quite frequently in forms or reports, in which case it would be easier to find and work with those fields if they were nearer the beginning of your table definition.
The actual sequence of field definitions in a table is not all that important. In the relational database model, there really is no defined sequence of fields in a row or rows in a table. Access, like most database programs that implement the relational model, does allow you to define a field order when you create a table. This order or sequence of fields becomes the default order you see in a table datasheet or in a list of field names when you're designing a query, form, or report. You can use the mouse to move one or more rows. Simply follow these steps:
In the design for the tblContacts table in the LawTrack Contacts database, the LastName field appears before the FirstName field. Let's assume you want the LastName field to appear first by default in all datasheets (and it makes more sense to sort first on LastName and then FirstName). Select the LastName field by clicking its row selector. Click the row selector again, and drag up until the line between the ContactID field and the FirstName field is highlighted, as shown in the following illustration.
In following illustration, the fields are positioned correctly. In this exercise, there's one additional field you need to move to make the design of the Contacts table (Contact Tracking database) more similar to tblContacts (LawTrack Contacts database). Near the bottom of the field list, grab the Birthdate field and move it up just after MiddleInit. It makes more sense to have the contact's birth date field close to the contact name fields and not stuck down at the bottom with the spouse information. Also, grab the Notes field and move it to the end after SpouseBirthDate. After you've done this, your table should now look like the following illustration. Inserting fieldsPerhaps one of the most common changes you'll make to your database is to insert a new field in a table. Up until now, we've renamed and moved the available fields to more closely match tblContacts. If you take a look at the comparison of the two tables again (shown in the following two illustrations), you can see that you need to add a few more fields. Now you're ready to insert fields to store the contact title, suffix, contact type, default address indicator, and more. First, you must select the row or move your cursor to the row that defines the field after the point where you want to insert the new field. In this case, if you want to insert fields for the title (Mr., Mrs., etc.), suffix (Jr., Sr., etc.), and contact type (customer, sales prospect, etc.) between the MiddleInit and Birthdate fields, place the cursor anywhere in the row that defines the Birthdate field. You can also select the entire row by using the arrow keys to move to the row and then pressing SHIFT+SPACEBAR or by clicking the row selector. Next, click Rows on the Insert menu (as shown in the following figure) or click the Insert Row button on the toolbar. (You can also click a field row and press the INSERT key to insert a row above your selection.) Access adds a blank row that you can use to define your new field. Type Title for the field name. Choose the Text data type, and set the Field Size property to 10. Move down to the Birthdate field again, and insert another row above it. Enter a Suffix field that has the Text data type with a field size of 10. Do it one more time and enter a ContactType field, data type Text, and length 50. Insert a field between Birthdate and WorkAddress, name it DefaultAddress, set its data type to Number, and set the Field Size to Integer. The actual LawTrack Contacts application uses this field to indicate whether the work or home address is the default mailing address. Move down to Photo and insert a field above it. Enter a field name of Website, and set its data type to Hyperlink. Finally, move down to the blank row beyond Notes (you can use an existing blank row to add a field at the end), and create a field named CommissionPercent that has the Number data type and a field size of Double. When you finish, your Table window in Design view should look something like the one shown in the following illustration. Don't worry about setting other properties just yet. Tip You can move the cursor between the upper part and the lower part of any Table or Query window in Design view by pressing F6. Copying fieldsAs you create table definitions, you might find that several fields in your table are similar. Rather than enter each of the field definitions separately, you can enter one field definition, copy it, and then paste it as many times as necessary. To finish defining the Contacts table, you need three additional fields — WorkFaxNumber, HomePhone, and MobilePhone. You could certainly insert a new row and type in all the properties as you just did in the previous section, but why not copy a field that is similar and make minor changes to it? For this part of the exercise, select the row for the WorkPhone field definition by clicking the row selector at the far left of the row. Click Copy on the Edit menu, or click the Copy button on the toolbar, as shown in the following illustration.  Move the insertion point to the row that should follow the row you'll insert. (In this case, move the insertion point to the HomeAddress field, which should follow your new field.) Insert a blank row by clicking Rows on the Insert menu or by clicking the Insert Row button on the toolbar. Select the new row by clicking the row selector. Click Paste on the Edit menu or click the Paste button on the toolbar to insert the copied row, as shown in the following illustration. Caution If you choose the Paste command when a row containing data is selected, the copied row will replace the selected row. Should you make this replacement in error, click Undo on the Edit menu to restore the original row. You can use the Paste command repeatedly to insert a copied row more than once. Remember to change both the name and the description of the resulting field or fields before you save the modified table definition. In this case, it's a simple matter to change the name of the copied row from WorkPhone to WorkFaxNumber and to correct the description and caption accordingly. Note that this procedure also has the benefit of copying any formatting, default value, or validation rule information. If you're careful, you don't actually have to insert a blank row to paste a field definition from the Clipboard. After you fix the WorkFaxNumber field name and description in the upper part of the window and Caption in the lower part of the window, scroll down to the EmailName field and click in the row but do not select the row. Click the Paste button on the toolbar twice to insert two more copies of WorkPhone just above EmailName. Change the name of the first one to HomePhone and the second to MobilePhone, and correct the descriptions and captions. You should now have a table that's almost identical to the tblContacts table in the LawTrack Contacts sample database. Be sure to save your changed table. Deleting fieldsRemoving unwanted fields is easy. With the Table window open in Design view, select the field that you want to delete by clicking the row selector. You can extend the selection to multiple contiguous fields by holding down the SHIFT key and using the up and down arrow keys to select multiple rows. You can also select multiple contiguous rows by clicking the row selector of the first row and, without releasing the mouse button, dragging up or down to select all the rows you want. After you select the appropriate fields, click Delete or Delete Rows on the Edit menu or press the DELETE key to delete the selected fields. If a table contains one or more rows of data, Access displays a warning message when you delete field definitions in Design view, as shown in the following illustration. Click No if you think you made a mistake. Click Yes to proceed with the deletion of the fields and the data in those fields. Keep in mind that you can still undo this change up to the point that you save the table. If you want to test this out in the sample table you have been building, make sure you have saved your latest changes and then switch to Datasheet view by clicking the View button on the far left end of the toolbar. Type your name in the Last Name and First Name fields and switch back to Design view by clicking the View button again. Try deleting any field in the design, and Access will warn you that you might be deleting some data, as well. Changing data attributesAccess provides a number of different data types. These data types help Access work more efficiently with your data and also provide a base level of data validation; for example, you can enter only numbers in a Number or Currency field. When you initially design your database, you should match the data type and length of each field to its intended use. You might discover, however, that a field you thought would contain only numbers (such as a U.S. ZIP Code) must now contain some letters (perhaps because you've started doing business in Canada). You might find that one or more number fields need to hold larger values or a different number of decimal places. Access allows you to change the data type and length of many fields, even after you've entered data in them. Changing data typesChanging the data type of a field in a table is simple. Open the table in Design view, click in the Data Type column of the field definition you want to change, click the down arrow button at the right to see the available choices, and select a new data type. You cannot convert an OLE Object or a Replication ID data type to another data type. With several limitations, Access can successfully convert every other data type to any other data type, even when you have data in the table. The following table shows you the possible conversions and potential limitations when the table contains data. Warning When the field contents don't satisfy the limitations noted in the following table, Access deletes the field contents (sets it to Null) when you save the changes. Limitations on converting one data type to another
If you want to see how this works in the Contacts table you have been building in the Contact Tracking database, open the table in Datasheet view and enter any last name and first name in one or two rows. You want to change the EmailName field from the Text data type that the Table Wizard provided to Hyperlink. Scroll right on one of the rows and enter an e-mail address in one of the rows in the form: myname@proseware.com. In another row, add the correct URL prefix in the form: mailto:myname@proseware.com. Now, switch to Design view and change the data type of the EmailName field from Text to Hyperlink and save the change. Notice that Access gives you no warning about any conversion problems because it knows it can store any text field that is not larger than 255 characters in a hyperlink, which can be up to 2 gigabytes. Switch back to Datasheet view and scroll to the right to find the changed field. You should see a result something like the following illustration. Both entries look fine. However, if you click the first one, Access attempts to open your browser because the full text stored in the hyperlink is johnv@viescas.com#http://johnv@viescas.com/#. Because the link address portion indicates the HTTP protocol, your browser opens, instead of your e-mail program. When you click the second link, it should open a blank message in your e-mail program with the To: line filled in correctly. Access recognized the mailto: prefix and converted the text correctly. Changing data lengthsFor text and number fields, you can define the maximum length of the data that can be stored in the field. Although a text field can be up to 255 characters long, you can restrict the length to as little as 1 character. If you don't specify a length for text, Access normally assigns the length you specify on the Tables/Queries tab of the Options dialog box. (The default length is 50.) Access won't let you enter text field data longer than the defined length. If you need more space in a text field, you can increase the length at any time; but if you try to redefine the length of a text field so that it's shorter, you will get a warning message (like the one shown in the following illustration) stating that Access will truncate any data field that contains data longer than the new length when you try to save the changes to your table. Note also that it warns you that any validation rules you have designed might fail on the changed data. Tip Remember, you can change the default data type for a new field and the default length of new text and number fields by clicking Options on the Tools menu and then clicking the Tables/Queries tab of the Options dialog box. If you want to try this in your Contacts table, open it in Datasheet view and type more than one character in MiddleInit. The last two changes you need to make to this table so that it is exactly like tblContacts in the LawTrack Contacts database are to set the length of MiddleInit to 1 and the length of SpouseName to 75. Switch to Design view and make those two changes. When you try to save the changes, you should see the warning message in the previous illustration (because you're shortening the length of the MiddleInit field). Click Yes to allow the changes and then switch back to Datasheet view. You should find one truncated character in MiddleInit. Sizes for numeric data types can vary from a single byte (which can contain a value from 0 through 255) to 2 or 4 bytes (for larger integers), 8 bytes (necessary to hold very large floating-point or currency numbers), or 16 bytes (to hold a unique ReplicationID or Decimal number). Except for ReplicationID, you can change the size of a numeric data type at any time, but you might generate errors if you make the size smaller. Access also rounds numbers when converting from floating-point data types (Single or Double) to integer or currency values. Dealing with conversion errorsWhen you try to save a modified table definition, Access always warns you if any changes to the data type or length will cause conversion errors. For example, if you change the Field Size property of a number field from Integer to Byte, Access warns you if any of the records contain a number larger than 255. (Access deletes the contents of any field it can't convert at all.) If you examine the Limitations on converting one data type to another table, you'll see that you should expect some data type changes to always cause problems. For example, if you change a field from Hyperlink to Date/Time, you can expect all data to be deleted. You'll see a dialog box similar to the one shown in the following illustration warning you about fields that Access will set to a Null value if you proceed with your changes. Click Yes to proceed with the changes. You'll have to examine your data to correct any conversion errors. If you click No, Access opens the dialog box shown in the following illustration. If you deleted any fields or indexes, added any fields, or renamed any fields, Access will save those changes. Otherwise, the database will be unchanged. You can correct any data type or length changes you made, and then try to save the table definition again. Changing other field propertiesAs you learned in the Creating your database and tables article, you can set a number of other properties that define how Access displays or validates a field that have nothing to do with changing the data type. These properties include Description, Format, Input Mask, Caption, Default Value, Validation Rule, Validation Text, Required, Allow Zero Length, and Indexed. If you have data in your table, changing some of these properties might elicit a warning from Access. If you change or define a validation rule, or set Required to Yes, Access offers to check the new rule or requirement that a field not be empty against the contents of the table when you try to save the change. If you ask Access to test the data, it checks all the rows in your table and opens a warning dialog box if it finds any rows that fail. However, it doesn't tell you which rows failed. If you changed the rules for more than one field, you'll see the error dialog box once for each rule that fails. As you'll learn later, when you define queries, forms, and reports, these objects inherit several of the properties that you define for your table fields. In previous versions of Access, the catch was that once you defined and saved another object that used table fields, any subsequent change that you made to properties in table design didn't change automatically in other dependent objects. You had to go find those properties yourself and fix them (or use a tool such as the Black Moshannon Systems — http://www.moshannon.com — Speed Ferret product). You would get the new property settings in any new objects you created, but the old ones remained unchanged. The good news is there's a new feature in Access 2003 that takes care of this problem for some properties. To see how this works, you must first make sure that you have this feature turned on in the Options dialog box. Click Options on the Tools menu, click the Tables/Queries tab, and verify that you have selected the Show Property Update Options buttons option. Next, open the Contacts table in Design view in the Contact Tracking database you have been building. You haven't defined a description for any of the fields yet. Remember from the "Creating your database and tables" article that Access displays the description on the status bar when the focus is on this field in any datasheet or form. Click in the Description column next to the ContactID field and enter a useful description such as Unique contact ID and press TAB. As soon as you do this, you'll see a little AutoCorrect smart tag appear that looks like a lightning bolt. If you hover near the smart tag, it tells you that it offers property update options. Click the down arrow next to the tag to see the option you can choose, as shown in the following illustration. Access offers you this option whenever you change the Description, Format, or Input Mask properties. You can click the option to ask Access to also change this property wherever the ContactID field is used in other objects. Of course, you don't have anything but tables in your sample database right now, so choosing the option won't do anything. Caution You must choose the option shown on the Property Update Option button immediately after you make the change in your table definition. If you move to another field or move to another property and make another change, the button disappears. You can get it back by returning to the property you changed and changing it again. If you ask it to make changes, Access opens an Update Properties dialog box that lists all the objects it plans to change. You can reject all changes or selectively apply the change to only some of the objects. Reversing changesIf you make several changes and then decide you don't want any of them, you can close the Table window without first saving these changes. When you do this, Access opens the dialog box shown in the following illustration. Simply click No to reverse all your changes. Click Cancel to return to the Table window without saving or reversing your changes. Tip You can always reverse up to the last 20 changes you made since you last saved the table design by clicking Undo. You can also open the list next to the Undo button to selectively undo a series of changes. Using the Table Analyzer WizardEven if you follow all the recommendations in the Designing your database application article and build a normalized database, you might not arrive at the best design. In fact, you often cannot fully evaluate a database design until you use the database and store data. Access includes the Table Analyzer Wizard, which can examine data in your tables (or data you import from another source) and recommend additional refinements and enhancements to your database design. You'll recall from the "Designing your database application" article that one of the key elements of good database design is the elimination of redundant data. The Table Analyzer Wizard is particularly good at scanning data in your tables, identifying data repeated in one or more columns, and recommending alterations to your design that break out the redundant data into separate tables. You can find an example of such redundant data in the LawTrack Contacts database (Contacts.mdb). Imagine that a customer sent you a file containing company and contact information. Sounds like a good, easy place to start collecting or adding to your contact data. However, when you open the file, you see that most companies are listed several times because the original data isn't normalized. You'll find just such a table, saved as tblContacts4TableAnalyzer, in the LawTrack Contacts sample database. You can see how the Table Analyzer Wizard works by using it on the tblContacts4TableAnalyzer table. First, open the LawTrack Contacts database (Contacts.mdb). Click Analyze on the Tools menu and then click Table on the submenu. Access starts the Table Analyzer Wizard and displays the first page, shown in the following illustration. This first window is one of two introductory windows that explain what the wizard can do. Click the Show me buttons to get a better understanding of the kinds of problems the wizard can solve and to see how the wizard works. Click Next twice to get to the first "action" page of the wizard, shown in the following illustration. On this page of the wizard, you select the table you want to analyze. For this exercise, select the tblContacts4TableAnalyzer table. (Note that you have an option on this page to continue to show the two introductory pages each time you start the wizard. If you think you understand how the wizard works, you can clear the check box to skip the introductory pages the next time you start the wizard.) Click Next. On the next page, the wizard asks if you want to rearrange the fields in the target table or if you want the wizard to decide the arrangement for you. If you know which fields contain redundant data, you can make the decision yourself. Because the wizard handles all the "grunt work" of splitting out lookup data, you might choose the latter option in the future to further normalize tables in your application. For now, select the Yes, let the wizard decide option to see how effective it is. Click Next to start the analysis of your table. The following illustration shows the result of the wizard's analysis. (I've shifted the contents of this figure to fit the result in a single window.) In this case, the wizard did a pretty good job of identifying the separate company and contact information and splitting the fields into two tables. It also recognized that ContactType has lots of repeating values and perhaps should be in a separate lookup table, but it incorrectly related that table to the company information. There isn't enough data (only 18 rows — and each contact is related to only one company) in the table for the wizard to have noticed a many-to-many relationship between companies and contacts. It probably kept the HomeStateOrProvince field with the company data because it didn't see a different value across multiple rows for the same company. It also incorrectly used the Department field as the primary key for the table containing the ContactType lookup field. We really don't need to do much work to fix this if we're happy with the one-to-many relationship. First, click HomeStateOrProvince in Table2 and drag it into Table1 between HomeCity and HomePostalCode. Also, move the Lookup to Table3 field from Table2 to Table1 to correctly relate the contact type lookup information to contacts instead of companies, and move the Department field from Table3 to Table2 between CompanyName and Address. Once you have adjusted the way the wizard split your tables, the next step is to give each of the new tables a new name. To rename a table, first click the table name and then click Rename Table in the upper part of the window. (You can also double-click the table's title bar.) The wizard opens a dialog box in which you can enter a new name. You should change Table1 to Contacts, Table2 to Companies, and Table3 to ContactTypes. Click Next when you are finished. The next page asks you to verify the primary key fields for these tables. You can select new fields for the primary key of each table or add fields to the primary key. The wizard couldn't identify any naturally occurring unique value, so it generated a unique ID (which will be an AutoNumber in the final tables) for two of the tables. You need to select the Contacts table and click Add Generated Key to create a primary key for that table. The following illustration shows the result of moving fields, assigning new names to the tables, and adding a primary key. Click Next to accept the settings and go on to an analysis of duplicate values in the lookup tables. The Table Analyzer Wizard looks at values in the new tables to try to eliminate any possible duplicates created by typing errors. The following illustration shows the result of this analysis on the sample table. Because the wizard sees several rows with Training or Police Dept. in them, it suggests that some of these values might, in fact, be the same. You can use this window to tell the wizard any correct values for actual mistyped duplicates. This could be extremely useful if your incoming data had the same company listed several times but with a slightly different spelling or address. The wizard will store only unique values in the final table. You could, if necessary, tell the wizard to substitute one set of similar values for another to eliminate the near duplicates. In this case, you should tell the wizard to use the original value for all the values listed as duplicates by selecting the (Leave as is) option as shown in the previous illustration. Click Next when you are finished to go on to the next page. Finally, the wizard offers to create a new query that has the same name as the original table. (See the following illustration.) If you've already been using the old table in queries, forms, reports, and data access pages, creating a new query that integrates the new tables into the original data structure means you won't have to change any other objects in your database. In most cases, the new query will look and operate just like the original table. Old queries, forms, reports, and data access pages based on the original table will now use the new query and won't know the difference. This is only an example, so click No, don't create the query. Click Finish to build your new tables. The wizard also creates relationships among the new tables to make sure you can easily re-create the original data structure in queries. The following illustration shows the three new tables built by the wizard. Notice that the wizard left behind an ID field in the Contacts table as a link to the ContactTypes table. The values in the ContactTypes table are actually unique, so there's no reason not to use the actual value as the primary key instead of an artificial ID. Taking a look at Lookup propertiesAs you have been working in table design, you've probably noticed that there's a Lookup tab available in the lower part of the Design window. You might have also noticed that Access offers you a Lookup Wizard entry in the drop-down list of data types and a Lookup Field option on the Insert menu. Microsoft Access 95 introduced this feature to allow you to predefine how you want the field displayed in a datasheet, form, or report. For example, if you have a DepartmentID field in an Employees table that stores the primary key value of the department for which the employee works, you might want to display the department name rather than the number value when you look at the data. If you're displaying a Yes/No field, you might want to provide a drop-down list that shows options for invoiced and not invoiced instead of yes and no or true and false. In the sample databases, Lookup properties are defined for only a few fields — ones that would later need a drop-down list (combo box) on one or more forms or reports. One such example is in the Housing sample database (Housing.mdb). Open the database, view the Table objects, select tblEmployees, and open it in Design view. Click the DepartmentID field and then click the Lookup tab to see the settings, as shown in the following illustration. As you can see, the Display Control property is set to Combo Box. You see combo boxes in Windows applications all the time — sometimes called drop-down lists. It's a box that you can type in, which also has a button on the right that you can click to drop down a list of values to select. In Access, you tell the combo box what type of list you want (Row Source Type) and specify the source of the list (Row Source). Access is a bit unusual because it lets you define a list that contains more than one column that you can display (Column Count), and it requires you to specify which of the columns (Bound Column) actually supplies the value to be stored when you pick an item from the list. This means that you might see a text value, but the combo box stores a number. You can see this combo box in action by switching to Datasheet view. You can click in the Department field and type a name from the list, or click the drop-down arrow on the right and choose an item from the list, as shown in the following illustration. Remember, DepartmentID is actually a number. If you didn't define the special settings on the Lookup tab, you would see a list of numbers in the Department column. For details about these settings, see the Lookup properties table. The properties in this table are now defined because a combo box will be used in one or more forms that would be built later to display related department information while editing an employee record. By setting the values in the table, you can avoid defining the combo box settings again when the forms are built. If you want to see how this works on a form, you can open frmEmployeesPlain in the Housing database. (You cannot open the "production" version of frmEmployees from the Database window, because code behind that form forces you to sign on to the application first.) You can see the result in the following illustration.
The following table gives you an overview of what the lookup settings mean. Lookup properties
Tip When designing a combo box that displays multiple columns when dropped down, you can specify a List Width value that's the sum of the Column Width values plus 0.25 inches to allow for the vertical scroll bar.
Changing the primary keyThe Designing your database application article discussed the need to have one or more fields that provide a unique value to every row in your table. This field or group of fields with unique values is identified as the primary key. If a table doesn't have a primary key, you can't define a relationship between it and other tables, and Access has to guess how to link tables for you. Even if you define a primary key in your initial design, you might discover later that it doesn't actually contain unique values. In that case, you might have to define a new field or fields to be the primary key. Let's go back to the three tables you built earlier with the Table Analyzer Wizard. Suppose you discover that users are becoming confused by the fact that ContactTypes_ID is a number instead of the actual text. (See my comments about using the Lookup Wizard in the previous section.) You could keep the lookup table to help avoid duplicate values, but there's no reason not to store the actual text value in the Contacts table instead of a linking ID. To fix this, you need to perform the following steps. Be sure to save your work at the end of each step.
Keep in mind that you can directly change the primary key for any table that does not have any relationships defined. Also, when the table contains data, the new fields that you choose for a primary key must have unique values in all the rows. Compacting your databaseAs you delete old database objects and add new ones, the space within your .mdb file can become fragmented. The result is that, over time, your database file can grow larger than it needs to be to store all your definitions and data. To remove unused space, you should compact your database periodically. No other users should be accessing the database you intend to compact. You can compact the database you currently have open by clicking Database Utilities on the Tools menu and then clicking Compact and Repair Database on the submenu. If you want to compact another database, you must close your current database and then click the Compact and Repair Database command. Access opens the dialog box shown in the following illustration. Select the database you want to compact, and then click Compact. Access asks you for a name for the compacted database. You can enter the same name as the database you are compacting, or you can use a different name. If you use the same name, Access warns you that the original database of the same name will be replaced. If you proceed, Access compacts your database into a temporary file. When the compacting process is successfully completed, Access deletes your old database and gives its name to the new, compacted copy. Tip You can also set an option to compact the database each time you close it. Click Options on the Tools menu, click the General tab, and then click Compact on Close. If multiple users are sharing the same database, Access compacts the database after the last user closes it. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Printer-Friendly Version
Show All
Hide All
Back to top
